jOOQ, in depth: code generation, the type-safe SQL DSL, MULTISET, transactions and running it in production

The model: SQL first, schema as types

An ORM starts from Java objects and derives SQL. jOOQ starts from the database and derives Java. The code generator reads your schema's catalog and emits a class per table (with a typed field per column), a Record class per table, key and index constants, sequences, routines and enum types. At runtime a DSLContext turns DSL expressions into dialect-specific SQL with bind values, runs it through JDBC (or R2DBC for reactive drivers) and maps rows back into typed records.

Two consequences matter in practice. First, every query is visible and reviewable as SQL, so there is no hidden lazy loading and no surprise flush. Second, the generated code is a build artefact derived from the schema, so the build must have a schema to read. Getting that pipeline right is the first job.

Code generation wired into the build

The generator can read a live database over JDBC, or parse DDL files without a database. The most faithful setup is: run your migrations (Flyway or Liquibase) against a throwaway database in the build, then point the generator at it. Testcontainers makes the throwaway database easy (see Testcontainers for Java). The generated code then matches exactly what production will have after the same migrations run. A Maven configuration looks like this:

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <version>${jooq.version}</version>
  <executions>
    <execution>
      <id>jooq-codegen</id>
      <phase>generate-sources</phase>
      <goals><goal>generate</goal></goals>
    </execution>
  </executions>
  <configuration>
    <jdbc>
      <driver>org.postgresql.Driver</driver>
      <url>${codegen.db.url}</url>
      <user>${codegen.db.user}</user>
      <password>${codegen.db.password}</password>
    </jdbc>
    <generator>
      <database>
        <name>org.jooq.meta.postgres.PostgresDatabase</name>
        <inputSchema>public</inputSchema>
        <excludes>flyway_schema_history</excludes>
        <recordVersionFields>version</recordVersionFields>
      </database>
      <target>
        <packageName>com.acme.db</packageName>
        <directory>target/generated-sources/jooq</directory>
      </target>
    </generator>
  </configuration>
</plugin>

Decide early whether generated sources are committed. Committing them lets IDEs and reviewers see schema changes as diffs and keeps builds independent of Docker, but someone must regenerate after every migration. Generating in every build removes drift but makes the build depend on a database container. Either works; mixing the two produces code that is silently out of date. The recordVersionFields setting marks a column for optimistic locking, covered below.

Writing queries with DSLContext

Create one DSLContext per data source and dialect, and share it. It is thread-safe when built over a DataSource. Queries read like SQL, but every column is a typed Field<T>, so comparing a Field<Long> with a string does not compile.

import static com.acme.db.Tables.*;
import static org.jooq.impl.DSL.*;

DSLContext ctx = DSL.using(dataSource, SQLDialect.POSTGRES);

// typed projection straight into a Java record
public record BookRow(long id, String title, String author) {}

List<BookRow> books = ctx
    .select(BOOK.ID, BOOK.TITLE, AUTHOR.LAST_NAME)
    .from(BOOK)
    .join(AUTHOR).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
    .where(BOOK.PUBLISHED_IN.ge(2020))
    .orderBy(BOOK.TITLE)
    .fetch(Records.mapping(BookRow::new));

The Records.mapping(BookRow::new) call is checked against the projection: if the select list has three columns of types long, String and String, the constructor must take exactly those. Java records pair well with this (see Java records).

Dynamic SQL, the reason many teams leave JPQL strings behind, is ordinary Java. Conditions are values you build up and combine:

Condition where = noCondition();
if (filter.title() != null)  where = where.and(BOOK.TITLE.containsIgnoreCase(filter.title()));
if (filter.minYear() != null) where = where.and(BOOK.PUBLISHED_IN.ge(filter.minYear()));
if (!filter.authorIds().isEmpty()) where = where.and(BOOK.AUTHOR_ID.in(filter.authorIds()));

var rows = ctx.selectFrom(BOOK).where(where).limit(50).fetch();

Every value becomes a bind parameter, so this is safe from SQL injection without any escaping. The danger is the explicit escape hatch: DSL.field(String) and DSL.condition(String) take raw SQL. Never concatenate user input into them; use their ? placeholder overloads.

Nested results with MULTISET

Loading parents with their children is where ORMs produce N+1 queries and where hand-written SQL produces flat, duplicated rows. jOOQ's MULTISET operator, added in 3.15, nests a correlated subquery as a collection inside each row. On databases with JSON or XML support, jOOQ emulates it by having the database aggregate the children and parsing the result back into typed records. The result is one round trip and no duplication.

public record Book(String title, int year) {}
public record AuthorWithBooks(String name, List<Book> books) {}

List<AuthorWithBooks> result = ctx
    .select(
        AUTHOR.LAST_NAME,
        multiset(
            select(BOOK.TITLE, BOOK.PUBLISHED_IN)
            .from(BOOK)
            .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
            .orderBy(BOOK.PUBLISHED_IN)
        ).convertFrom(r -> r.map(Records.mapping(Book::new))))
    .from(AUTHOR)
    .where(AUTHOR.COUNTRY.eq("NO"))
    .fetch(Records.mapping(AuthorWithBooks::new));

Check the generated SQL with EXPLAIN on large data. Every parent row runs a correlated subquery, so the child foreign key needs an index. Very large child collections are better fetched separately and stitched in memory.

Writing data: inserts, upserts, records and batches

Inserts and updates are also typed, and RETURNING is supported where the database has it (and emulated where it can be):

Long id = ctx.insertInto(BOOK, BOOK.TITLE, BOOK.AUTHOR_ID, BOOK.PUBLISHED_IN)
    .values("Systems Performance", 7L, 2020)
    .returningResult(BOOK.ID)
    .fetchOne(BOOK.ID);

// upsert: ON CONFLICT on PostgreSQL, MERGE or ON DUPLICATE KEY elsewhere
ctx.insertInto(STOCK, STOCK.SKU, STOCK.QTY)
   .values(sku, qty)
   .onConflict(STOCK.SKU)
   .doUpdate().set(STOCK.QTY, STOCK.QTY.plus(qty))
   .execute();

// active-record style for simple CRUD; store() issues INSERT or UPDATE of changed fields only
BookRecord b = ctx.fetchOne(BOOK, BOOK.ID.eq(id));
b.setTitle("Systems Performance, 2nd ed.");
b.store();

// many rows: one JDBC batch instead of N round trips
ctx.batchInsert(newBookRecords).execute();

With recordVersionFields configured and the executeWithOptimisticLocking setting enabled, store() adds WHERE version = ? and increments the version, and a concurrent update causes a DataChangedException instead of a lost write. Batches are not free: one failing row can fail the whole batch depending on the driver, so validate first and keep batches to a few hundred or thousand rows.

Transactions and Spring Boot

jOOQ has its own transaction API, which passes a derived configuration into a lambda. Everything inside uses the same connection, and the transaction commits on normal return and rolls back on an exception:

long orderId = ctx.transactionResult(cfg -> {
    DSLContext tx = DSL.using(cfg);
    long oid = tx.insertInto(ORDERS, ORDERS.CUSTOMER_ID).values(customerId)
                 .returningResult(ORDERS.ID).fetchOne(ORDERS.ID);
    tx.batchInsert(lines.stream().map(l -> toRecord(tx, oid, l)).toList()).execute();
    int updated = tx.update(STOCK).set(STOCK.QTY, STOCK.QTY.minus(qty))
                    .where(STOCK.SKU.eq(sku).and(STOCK.QTY.ge(qty))).execute();
    if (updated == 0) throw new OutOfStockException(sku);   // rolls everything back
    return oid;
});

The classic mistake is using the outer ctx inside the lambda instead of tx. That query runs on a different connection, outside the transaction, and survives the rollback. In Spring Boot, spring-boot-starter-jooq provides a DSLContext bean that joins Spring-managed transactions, so @Transactional service methods work as usual and the trap disappears. Choose one style per codebase. Spring Boot covers the wider configuration.

Worked example: a paginated order history endpoint

An endpoint returns a customer's orders newest first, 20 at a time, each with its line items. A first version uses OFFSET: fine on page 1, and by page 500 the database is reading and discarding 10,000 rows per request. Keyset (seek) pagination instead remembers the last row's sort key and asks for rows after it. jOOQ has a seek clause for this:

public record Line(String sku, int qty) {}
public record OrderView(long id, OffsetDateTime placedAt, BigDecimal total, List<Line> lines) {}

List<OrderView> page(long customerId, OffsetDateTime lastPlacedAt, Long lastId) {
    var q = ctx.select(ORDERS.ID, ORDERS.PLACED_AT, ORDERS.TOTAL,
                multiset(select(ORDER_LINE.SKU, ORDER_LINE.QTY)
                         .from(ORDER_LINE).where(ORDER_LINE.ORDER_ID.eq(ORDERS.ID)))
                    .convertFrom(r -> r.map(Records.mapping(Line::new))))
        .from(ORDERS)
        .where(ORDERS.CUSTOMER_ID.eq(customerId))
        .orderBy(ORDERS.PLACED_AT.desc(), ORDERS.ID.desc());
    if (lastId == null) return q.limit(20).fetch(Records.mapping(OrderView::new));       // first page
    return q.seek(lastPlacedAt, lastId).limit(20).fetch(Records.mapping(OrderView::new));
}

With an index on (customer_id, placed_at DESC, id DESC) and one on order_line(order_id), every page costs the same: an index range read of 20 orders plus 20 small child lookups, in one round trip. The id tiebreaker matters, because two orders placed in the same millisecond would otherwise be skipped or repeated at a page boundary. The API returns the last row's (placed_at, id) as an opaque cursor.

Running it in production

Observe every query. An ExecuteListener sees each execution's lifecycle, so slow-query logging, metrics and tracing take one class:

public class SlowQueryListener implements ExecuteListener {
    private static final long THRESHOLD_NS = 200_000_000L;   // 200 ms
    @Override public void executeStart(ExecuteContext ctx) { ctx.data("t0", System.nanoTime()); }
    @Override public void executeEnd(ExecuteContext ctx) {
        long took = System.nanoTime() - (Long) ctx.data("t0");
        if (took > THRESHOLD_NS) log.warn("slow query {} ms: {}", took / 1_000_000, ctx.sql());
    }
}

DSLContext ctx = DSL.using(new DefaultConfiguration()
    .set(dataSource).set(SQLDialect.POSTGRES)
    .set(new DefaultExecuteListenerProvider(new SlowQueryListener())));

Log ctx.sql(), which contains placeholders, not values with the binds inlined. Inlined values leak personal data into logs and make every query text unique, which breaks aggregation in tools such as pg_stat_statements. For the same reason, keep jOOQ's default prepared statements with bind values in production. Inlining all values defeats the database's plan cache.

Pool and dialect. jOOQ does not pool connections; put HikariCP or your container's pool underneath. Set the SQLDialect to match the production database, since it controls rendering of upserts, pagination, RETURNING and MULTISET emulation. Run integration tests against the real engine, not H2 pretending to be PostgreSQL.

Editions and versions. The Open Source Edition is Apache-2.0 licensed and supports open-source databases. Commercial editions add databases such as Oracle and SQL Server, and support for older JDKs: from 3.20 the Open Source Edition requires JDK 21. Check the jOOQ site for the current release and the database list per edition before planning an upgrade.

Failure modes

When to choose jOOQ, and when not to

Choose jOOQ when the database is a first-class part of the design: reporting, analytics-style reads, complex joins, window functions, upserts, vendor features, or a schema owned by DBAs and migrations rather than by entity classes. It also suits teams who want every query visible in review.

Prefer JPA when the domain is a rich object graph with mostly simple CRUD and you rely on unit-of-work features such as dirty checking, cascades and a first-level cache. Spring Data repositories give the fastest start for that style. Mixing works too. Many codebases keep JPA for aggregate CRUD and use jOOQ for read models and reports on the same DataSource. Use Spring-managed transactions so both share a connection.

What to do next

1. Add the code generator to one service's build, generating from your real migrations on a Testcontainers database, and decide whether generated sources are committed.
2. Port one query you currently build as a string to the DSL, with dynamic conditions built from noCondition().
3. Replace one N+1 loading path with MULTISET, index the child foreign key, and compare query counts and latency.
4. Switch one deep-paginated endpoint from OFFSET to seek with a unique tiebreaker column.
5. Register a slow-query ExecuteListener that logs SQL with placeholders, and wire it into metrics.
6. Add a version column to one contended table and enable optimistic locking; write a test with two concurrent updates.
7. Confirm your edition covers your database and JDK before the next upgrade.