Work with the jOOQ DSL

This article is part of our Academy Course titled jOOQ – Type safe DB querying.

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. Check it out here!

Examples displayed in this section are also available from the org.jooq.academy.section1 package.

1. The idea behind the jOOQ DSL

jOOQ is a DSL (domain-specific language), which mimicks both standard and vendor-specific SQL syntax in a Java API. The idea behind this API is easy to understand:

• 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 other words, when you want to express a SQL query like this:

SELECT   author.first_name, author.last_name
FROM     author
ORDER BY author.id 

… then you can immediately write the same query with jOOQ

select  (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.from   (AUTHOR)
.orderBy(AUTHOR.ID); 

How does it work, and how do jOOQ and your Java compiler know what “select” means, or what “AUTHOR.FIRST_NAME” means?

2. Running a first query

In the above example, a couple of simple assumptions have been made, and these assumptions will be made throughout the course:

• Whenever you see a standalone SQL keyword, then it was probably static-imported from org.jooq.impl.DSL
• Whenever you see a standalone table reference, then it was probably static-imported from the generated Tables class

In other words, in every class that uses jOOQ, ideally, you’ll just add these two import statements:

import static org.jooq.example.db.h2.Tables.*;
import static org.jooq.impl.DSL.*;

This will make the above code compile. But such a Select statement doesn’t do much, it just sits there, and can be printed to the console:

System.out.println(
    select  (AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
    .from   (AUTHOR)
    .orderBy(AUTHOR.ID)
); 

The above would print:

select "PUBLIC"."AUTHOR"."FIRST_NAME", "PUBLIC"."AUTHOR"."LAST_NAME" from "PUBLIC"."AUTHOR" order by "PUBLIC"."AUTHOR"."ID" asc

Executing such a query is very simple. All we need to do is provide it with a JDBC Connection, and then call fetch() on it:

DSL.using(connection)
   .select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch(); 

Note, instead of repeating DSL.using(...) all the time, you can of course also assign the object to a local variable, or configure it using Spring or your favourite configuration framework:

DSLContext dsl = DSL.using(connection);

dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch(); 

jOOQ will internally create a new JDBC PreparedStatement, execute it, consume the JDBC ResultSet, and eagerly close all resources that it created. The resulting object is a Result, which also implements a very useful toString() method:

System.out.println(
    dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
       .from(AUTHOR)
       .orderBy(AUTHOR.ID)
       .fetch()
); 

The above would yield:

+----------+---------+
|FIRST_NAME|LAST_NAME|
+----------+---------+
|George    |Orwell   |
|Paulo     |Coelho   |
+----------+---------+ 

3. Other statement types

Every DML SQL statement (and also some DDL SQL statements) are supported natively by jOOQ, including SELECT, UPDATE, INSERT, DELETE, MERGE. If we want to create, update, delete a new AUTHOR record, we can write the following SQL statements:

3.1. INSERT

dsl.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .values(3, "Alfred", "Hitchcock")
   .execute(); 

Note that instead of calling fetch(), we’ll now call execute(), which returns the number of affected rows.

An interesting aspect of the above query is the fact that jOOQ uses a lot of Java generics to ensure type safety in your queries. For example, the following two queries would produce compilation errors:

dsl.insertInto(AUTHOR, AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .values(3, "Alfred")
// ^^^^^^^ values() expects three arguments (for ID, FIRST_NAME, LAST_NAME),
//                  but only two were provided!
   .execute() 

That’s pretty powerful as you will never forget adding an equal amount of values to the VALUES clause as required from your INTO clause. This also extends to type mismatches. If you decide to reorder the columns in the INTO clause, but forget to adapt the VALUES clause, chances are that your Java compiler will complain again. The following won’t compile:

dsl.insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, AUTHOR.ID)
   .values(4, "Alfred", "Hitchcock")
// ^^^^^^^ values() expects arguments of type (String, String, Integer),
                    but (Integer, String, String) was provided!
   .execute() 

3.2. UPDATE

The UPDATE statement is just as straight-forward as the previous statements:

dsl.update(AUTHOR)
   .set(AUTHOR.DATE_OF_BIRTH, Date.valueOf("1899-08-13"))
   .where(AUTHOR.ID.eq(3))
   .execute() 

By now, selecting the author with ID = 3 will yield the complete AUTHOR record:

dsl.select()
   .from(AUTHOR)
   .where(AUTHOR.ID.eq(3))
   .fetch() 

… yielding

+----+----------+---------+-------------+
|  ID|FIRST_NAME|LAST_NAME|DATE_OF_BIRTH|
+----+----------+---------+-------------+
|   3|Alfred    |Hitchcock|1899-08-13   |
+----+----------+---------+-------------+ 

3.4. DELETE

Last but not least, the DELETE statement follows, trivially:

dsl.delete(AUTHOR)
   .where(AUTHOR.ID.eq(3))
   .execute() 

4. Predicates

An important aspect of SQL, and especially of dynamic SQL are predicates. SQL knows a variety of predicates, such as:

4.1. Everyday predicates

• comparison predicates
• NULL predicate
• LIKE predicate
• IN predicate
• EXISTS predicate

4.2. More advanced predicates

• quantified comparison predicates
• DISTINCT predicate
• BETWEEN predicate
• OVERLAPS predicate

Also, in SQL, predicates can be easily combined using AND and OR. jOOQ reflects all of these predicates in a fluent way, directly on column types. This is best explained by example:

4.3. Comparison predicates

// A filtering predicate
AUTHOR.ID.eq(3);

// A join predicate
AUTHOR.ID.eq(BOOK.AUTHOR_ID); 

Most of these predicates are also type safe, e.g. you cannot compare numbers with strings.

AUTHOR.ID.eq("abc");
//        ^^ Compilation error. An expression of type Integer is expected
//           (or Field<Integer>, or Select<? extends Record1<Integer>>) 

An interesting case for type safety is the IN predicate, which expects either a list of values, or a subquery with exactly one column on the right side of the IN keyword:

// IN list
AUTHOR.ID.in(1, 2, 3);

// IN with subquery
AUTHOR.ID.in(select(BOOK.AUTHOR_ID).from(BOOK)) 

The second example performs a semi-join between AUTHOR and BOOK tables, returning only those authors that have written at least one book. Because of Java generics, the following query would not compile:

// IN list with wrong types
AUTHOR.ID.in("a", "b", "c");
//           ^^^^^^^^^^^^^ This in() method expects an Integer... argument

// IN with subquery returning wrong type
AUTHOR.ID.in(select(BOOK.TITLE).from(BOOK))
//           ^^^^^^^^^^^^^^^^^^ This in() method expects a   Select<? extends Record1<Integer>>
//       

In addition, the following (invalid) statement will be rejected by the Java compiler:

AUTHOR.ID.in(select(BOOK.AUTHOR_ID, BOOK.TITLE).from(BOOK))
//        ^^ This in() method expects a   Select<? extends Record1<Integer>>,
//           but instead, an incompatible Select<Record2<Integer, String>> was provided 

5. Column expressions

In SQL, you can create new types of column expressions by applying functions or operations to them. For instance, you can concatenate first and last names of authors:

5.1. In SQL:

SELECT   author.first_name || ' ' || author.last_name
FROM     author
ORDER BY author.id 

5.2. With jOOQ::

dsl.select(concat(AUTHOR.FIRST_NAME, val(" "), AUTHOR.LAST_NAME))
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch() 

Remember that we’re static importing everything from DSL, including DSL.concat() and DSL.val().

dsl.select(AUTHOR.FIRST_NAME.concat(" ").concat(AUTHOR.LAST_NAME))
   .from(AUTHOR)
   .orderBy(AUTHOR.ID)
   .fetch() 

Of course, since Java doesn’t allow operator overloading (or symbolic method names), we’ll have to do with regular method names, in this case concat. Whether you want to use prefix-notation for functions, or infix-notation for operators is usually up to you.

6. More information about the jOOQ DSL

The jOOQ DSL is very rich with features. Listing all the features in this course would be repeating the reference manual. Consider the manual’s section about SQL building to learn more about the jOOQ DSL.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.