Core Java

jOOQ Tutorial for Type safe DB querying

Course Overview

SQL is a powerful and highly expressive language for queries against relational databases. SQL is established, standardised and hardly challenged by alternative querying languages. Nonetheless, in the Java ecosystem, there had been few relevant steps forward since JDBC to better integrate SQL into Java. All attention was given to object-relational mapping and language abstractions on a higher level, such as OQL, HQL, JPQL, CriteriaQuery. In the mean time, these abstractions have become almost as complex as SQL itself, regardless of the headaches they’re giving to DBAs who can no longer patch the generated SQL. jOOQ is a dual-licensed Open Source product filling this gap. It implements SQL itself as an internal domain-specific language in Java, allowing for the typesafe construction and execution of SQL statements of arbitrary complexity. This includes nested selects, derived tables, joins, semi-joins, anti-joins, self-joins, aliasing, as well as many vendor-specific extensions such as stored procedures, arrays, user-defined types, recursive SQL, grouping sets, pivot tables, window functions and many other OLAP features. jOOQ also includes a source code generator allowing you to compile queries in modern IDEs such as Eclipse very efficiently.

jOOQ is a good choice in a Java application where SQL and the specific relational database are important. It is an alternative when JPA / Hibernate abstract too much, JDBC too little. It shows, how a modern domain-specific language can greatly increase developer productivity, internalising SQL into Java. In this course, we’ll see how we can efficiently query databases using jOOQ.

About the Author

Lukas is the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. Data Geekery has been selling database products and services around Java and SQL since 2013.

Ever since his Master’s studies at EPFL in 2006, Lukas has been fascinated by the interaction of Java and SQL. Most of this experience he has obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). he’s sharing his knowledge at various conferences, JUGs, in-house presentations and on the company blog.


Getting Started with jOOQ, H2, and Maven

In this lesson, we will discuss how to get started with jOOQ, H2, and Maven by creating a sample project.

Work with the jOOQ DSL

jOOQ is a DSL (domain-specific language), which mimicks both standard and vendor-specific SQL syntax in a Java API. Being an internal DSL, the Java compiler can verify your SQL queries for syntactic correctness (e.g. correct order of SQL keywords). Having tables and columns as generated Java objects, the compiler can also verify meta data correctness (e.g. correct column names and types). In this lesson, we will delve into jOOQ DSL and use predicates, statements and build expressions.

Perform CRUD with Active Records

In this lesson, we are going to see how to perform CRUD with Active Records. Writing CRUD is boring and repetitive, which is why ORMs like Hibernate have emerged and have been successful at increasing developer productivity. But Hibernate makes a lot of assumptions (and restrictions) about your architecture, when often you really just want to operate on single records from tables. jOOQ knows “active records”, or also UpdatableRecords, which can be loaded with “special” kinds of SELECT statements, and which then keep track of dirty flags, internally. We will learn how to perform simple active record operations and how to use optimistic locking.

Comparing jOOQ with JDBC

In this lesson, we are going to compare jOOQ with JDBC. JDBC has often been criticized for being verbose. JDBC is also criticized for having chosen the wrong “defaults”, e.g. the default to lazy materialization of result sets. We’ll see how jOOQ improves on the criticisms around checked exceptions, result sets, Prepared statements and Statements with result sets.

The jOOQ Configuration

In this lesson, we are going to learn how lifecycle management objects interact with jOOQ. There are a variety of SPIs (Service Provider Interfaces) which allow for managing the lifecycle of various entities within jOOQ. These SPIs can be injected via a Configuration object. We’ll see how to use the ConnectionProvider, how jOOQ generates and executes SQL statements in the context of a concrete SQLDialect and how Settings are used to provide jOOQ with information about general query rendering and execution behaviour.

Make sure to retweet this, let your social followers know!

Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments
Back to top button