All of the examples mentioned here - and a lot more besides - are in a GitHub repository.

Introduction

I ignored jOOQ for quite a long time - mainly because I was happy using Jdbi (and still am); and then later on because I spent time using Hibernate ORM (which was easy to get up-and-running but hard to get right).

I still find it faster to write native SQL than to write jOOQ’s SQL-as-Java - but there are many really great things in jOOQ which I appreciate. What caught my eye recently was this article: jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL - which is a few years old, at this point, but still was something I really wanted to try for myself: returning more complex nested result sets, without having to wrangle all that JSON-related syntax provided by vendors (such as those provided by MySQL).

There were also other things which caught my eye, including:

  • code generation for POJOs (including Java records)
  • automatic support for optimistic locking
  • automatic handling of prepared statements

I have to confess, writing type-safe SQL was not so high on the list of things that interested me - although I do see how powerful that can be.

Using the Sakila Database

The first thing I did was to set up my own copy of the Sakila database.

Specifically, I used the scripts for MySQL.

I used MySQL v8.4.

(And Java 21.)

Running the jOOQ Code Generation Tool

I wanted to see how to use jOOQ’s code generation options.

There are various different ways to run the code generator. I chose to use the command line. To do that, I first wrote a small Maven script to collect the JAR files needed to run the generator:

The jars-pom.xml POM collects the required JAR files and places them in a lib/ subdirectory. The following command does this:

1

mvn -f jars-pom.xml package

This is a one-time process. I don’t need to do this again unless I need to download newer versions of any of the JARs.

Now I can run jOOQ generation commands such the ones shown in the next sections.

Generating an XML Schema

I did not need to do this, but I wanted to.

jOOQ can generate an XML representation of my schema. To do this I created the following jOOQ configuration file:

jooq-xml-schema-config.xml

The following command uses the above jooq-xml-schema-config.xml file:

1

java -classpath lib/* org.jooq.codegen.GenerationTool jooq-xml-schema-config.xml

The config file contains JDBC connection details for my Sakila database. jOOQ connects to the DB and extracts all of the relevant schema data.

You can see the results for my Sakila database here.

This XML data can be used later on (see below) to actually generate the Java code for jOOQ (and for me) to use.

You don’t have to use this type of XML file to generate jOOQ code. You can generate code directly from the database itself - and I expect that is what most people do. But (as noted earlier) I wanted to try this, out of curiosity.

Generating jOOQ Classes

A new jOOQ config schema is needed to do this.

My example is here: jooq-classes-config.xml

There are many ways to configure this file, for many different purposes and behaviors. Mine is relatively simple.

The generation command:

1

java -classpath lib/* org.jooq.codegen.GenerationTool jooq-classes-config.xml

Some points to note from the config file:

jOOQ Record Classes

jOOQ generates one jOOQ Record class for each table and view in the related schema. Each class contains information about the columns in each table and view.

In this context, Record means a relational table record (e.g. a row of data). These days, this is somewhat unfortunate, since this jOOQ Record is based on the jOOQ class org.jooq.Record;. Nowadays, this clashes with Java’s newer Record type (which is a totally different thing).

You will see in my jOOQ-related code that I always explicitly add the following import to avoid naming collisions:

1

import org.jooq.Record; // must explicitly include this import

jOOQ Table Classes

jOOQ generates one jOOQ Table class for each table and view it exports. These classes also help to support strongly typed Java when you write your jOOQ SQL code.

So, for the DB table ACTOR, jOOQ generates the file Actor.class.

Since I wanted jOOQ to also generate Java POJOs, I wanted to distinguish the jOOQ table classes from my POJO classes. They would both be in different packages - but would both be called (for example) Actor.class.

So, I chose to make jOOQ generate its table classes using a suffix of Table, so I would not be juggling two different classes (e.g. called Actor.class).

You can see this in the jOOQ config file here. First, jOOQ adds a suffix of _TABLE to the database table name; and then jOOQ uses Pascal case formatting to change that into a string suitable to be used as a Java table name.

jOOQ Generated POJOs

I was not sure how I was going to use the POJOs I chose to generate.

It turns out they were mainly useful as templates for generating custom POJOs - and I found myself using jOOQ’s Record classes (and UpdatableRecord subclasses) most of the time.

As it mentions in one section of the jOOQ manual, when discussing DAOs and POJOs:

It’s perfectly fine to work with org.jooq.UpdatableRecord directly, or with SQL statements, instead!

Arranging the Generated jOOQ Classes

I tried various things, but ended up simply copying the generated classes into the main “Sources” structure (under src/main/java/), under their own package hierarchy.

I considered doing what Hibernate does - which is to place all generated classes in a parallel generated-sources structure, but ended up keeping things simpler than that.

Writing jOOQ SQL

All of the jOOQ SQL samples I wrote are runnable against my Sakila database.

You can see them all in JooqExamples.java.

The main entrypoint is here: Sakila.java

I arranged the queries into the following broad categories:

  • Fetching exactly one Record (code)
  • Fetching zero, one or many records (code)
  • Basic projections (code)
  • Parent-child navigation (code)
  • Fetching into POJOs (code)
  • Path joining (code)
  • Multisets (code)
  • Nested Java records (code)
  • Updating (code)
  • Inserting and deleting (code)
  • Path correlation (code)
  • Aliasing (code)
  • Common jOOQ types (code)
  • DAO methods (code)
  • Plain SQL (code)

If you look at the “nested Java records” section, you can see some of the really cool ways in which jOOQ leverages a database’s built-in nesting capabilities (e.g. via JSON handing, for MySQL).

Here is one:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

List<FilmRec> resultOne = dsl.select(
        FILM.TITLE,
        DSL.multiset(
            DSL.select(
                FILM_ACTOR.actor().FIRST_NAME,
                FILM_ACTOR.actor().LAST_NAME)
                .from(FILM_ACTOR)
                .where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
        ).as("actors").convertFrom(r -> r.map(Records.mapping(ActorRec::new))),
        DSL.multiset(
            DSL.select(FILM_CATEGORY.category().NAME)
                .from(FILM_CATEGORY)
                .where(FILM_CATEGORY.FILM_ID.eq(FILM.FILM_ID))
        ).as("categories").convertFrom(r -> r.map(Records.mapping(CategoryRec::new)))
)
        .from(FILM)
        .where(FILM.TITLE.startsWithIgnoreCase("arm"))
        .orderBy(FILM.TITLE)
        .fetch(Records.mapping(FilmRec::new));

The best walkthrough of what is going on here is in the article I mentioned at the start: jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL

GitHub Code

jOOQ Sakila Demo