The joys of jOOQ

This is an appreciation of the excellent jOOQ library for JVM langauges.

Last updated: 2026-01-20

Intro

I love jOOQ. In case you've not heard of it, here's the elegant description from jOOQ's website:

jOOQ generates Java code from your database and lets you build type safe SQL queries through its fluent API.

I feel like most other database-querying libraries try to reinvent SQL for some reason. You have ORMs like Hibernate that try to abstract the SQL away, which is all well and good until you realise you have to learn a second language to configure them. You have Spring Data JPA that also tries to remove the need to write SQL, which works nicely until you need something more than a 'get/update by ID'.

By comparison, here's some Java code that uses jOOQ:

    DSLContext db = DSL.using(SQLDialect.POSTGRES);

    List<Book> books = db.select(BOOK.NAME, AUTHOR.NAME)
            .from(BOOK)
            .join(AUTHOR)
            .on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
            .fetchInto(Book.class);
    

If you've ever written SQL before, then you already understand what this code is doing. That's the beauty - you don't have to learn any special language, it's just SQL. Actually, it's better than just SQL - it's type-safe SQL. This means that, as long as you stick to referencing generated tables and column, it's nigh on impossible to write a statment with a syntax error.

Oh yeah, notice how the description of jOOQ says that it "generates" Java code? I don't understand why more libraries don't have this feature. In many other database libraries, you have to define your tables and columns twice - once in your SQL scripts and once in code. With jOOQ, you do it only once.

To get the code generation, you just add a snippet to your POM.xml or build.gradle file to have jOOQ inspect your schema on build and generate type-safe, intuitive classes for all your tables and columns.

jOOQ is not limited to simple queries either - your are able to construct pretty much any SQL query using the methods it exposes. There is a DSL.sql(String) method in case you need to execute some custom SQL, I suppose for performance reasons or because your statement is too complex to represent using jOOQ, but I have never used this or seen it in any of our codebases.

Implicit joins

An interesting feature of jOOQ's is the ability to forgo the explicit joining of tables. After all, if I'm trying to select a book's author's name, and the book.author_id -> author.id relationship is defined in my DDL, why should I need to manually type out the join? Check out these two examples:

The traditional way to join tables:

    Book getById(int id) {
        return dslContext
                .select(BOOK.NAME, AUTHOR.NAME)
                .from(BOOK)
                .join(AUTHOR)
                .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
                .where(BOOK.ID.eq(id))
                .fetchOneInto(Book.class);
    }
    

The cool way to join tables😎:

    Book getById(int id) {
        return dslContext
                .select(BOOK.NAME, BOOK.author().NAME)
                .from(BOOK)
                .where(BOOK.ID.eq(id))
                .fetchOneInto(Book.class);
    }
    

This works using jOOQ's Implicit path JOIN mechanism, allowing you to use relationships you've defined to select data from where you want it. This might seem like just a way to save two lines of code at first, but it's actually extremely powerful when combined with the following feature.

Dynamic select fields

Suppose you're building some kind of projection search where the client is able to specify which of the resource's field they want returned in the response - this is useful when the resource your API returns is large but some clients only care about a small subset of it. jOOQ lets you do this:

    Book getById(int id, List<Field<?>> fields) {
        return dslContext
                .select(fields)
                .from(BOOK)
                .where(BOOK.ID.eq(id))
                .fetchOneInto(Book.class);
    }

    // Example usage
    Book getBookById(int id) {
        return getById(id, List.of(BOOK.ID, BOOK.TITLE, BOOK.PAGE_COUNT));
    }
    

You can probably tell that this is nicer than writing SQL strings by hand, where you'd need to do string concatentation to build up your query.

Putting it together

Now here's the really cool part. Imagine some of your clients care about author information while others don't. If you call

    Book getBookById(int id) {
        return getById(id, List.of(BOOK.ID, BOOK.TITLE, BOOK.PAGE_COUNT));
    }
    

then jOOQ will output the following SQL:

    select "book"."id", "book"."title", "book"."page_count"
    from "book"
    where "book"."id" = 1

Now if we add BOOK.author().NAME.as("author") to that list of fields, jOOQ outputs this:

    select
      ""book"."title",
      "alias_122142126"."name" as "author"
    from (
      "book"
        join "author" as "alias_122142126"
          on "book"."author_id" = "alias_122142126"."id"
      )
    where "book"."id" = 1

Glossing over the funky alias that's been added, we can see jOOQ has added the join for us! This is mega. We make use of this feature in our customer-data-service which has ~30 clients, all wanting slightly different data. Being able to pass in the desired fields and letting jOOQ build the query saves a huge amount of complexity in our code (this is contradictory to when I said jOOQ is "just" SQL, but come on, this is awesome).

You can also pass lists of where clauses, saving you from stitching them together by hand:

    Book search(List<Condition> conditions) {
        return dslContext
                .selectFrom(BOOK)
                .where(conditions)
                .fetchOneInto(Book.class);
    }
    

Not all sunshine and rainbows

In the first paragraph, I mentioned that there are two issues with jOOQ. So in the interest of fairness, here they are. The first one is a biggy for some users: some databases are only supported if you pay for jOOQ. The free edition of jOOQ supports most of the big players: Postgres, SQLite, MySQL, H2 and many others. But some options are locked behind a paywall, including Oracle and SQL Server. You can see which editions of jOOQ support which databased on the Support Matrix.

The other niggle involves the fetchLazy() method that jOOQ offers for reading records from your database on demand instead of loading them all into memory at once - a very useful feature if you have a query that can return a massive number of records. However, it only behaves as expected if you specify the fetch size before calling the method, a detail that (at least for me) is easy to forget. For example, if you are using Postgres and execute the following query, then all records will be loaded into memory at once, probably OOMing your app:

    // selectFrom(BOOK) selects all columns from the BOOK table.
    Stream<Book> books = db.selectFrom(BOOK)
            .fetchLazy()
            .fetchStreamInto(Book.class);
    

To use fetchLazy() properly, you need to call fetchSize(int) beforehand e.g

    Stream<Book> books = db.selectFrom(BOOK)
            .fetchSize(1000)
            .fetchLazy()
            .fetchStreamInto(Book.class);
    

This ensures that only 1000 records are fetched from the database at a time.

In fairness to jOOQ, this behaviour is clearly documented in the javadoc:

Depending on your JDBC driver's default behaviour, this may load the whole database result into the driver's memory. In order to indicate to the driver that you may not want to fetch all records at once, use fetchSize(int) or Settings.setFetchSize(Integer) prior to calling this method.

Wrapping up

So that's it. Next time you spin up an application with a database, try using jOOQ (see the org.jooq.SQLDialect javadoc for the list of supported dialects).

TODO

• Add example of how to implement code generation