This is a note to understand jOOQ in depth.

Key Types

The DSL and the DSLContext are the main entry points providing implementations to the org.jooq interfaces. You can instantiate all objects from these two types.

DSL is mainly used to create all jOOQ objects including the DSLContext. For better fluency and readability, use import static org.jooq.impl.DSL.* to use static methods such as exists, val, select, inline and etc. Each SQL dialect has its own dialect-specific DSL such as MySQLDSL.

The DSLContext represents a closable connection and is attached to the Configuration. Thje configuration controls statement execution behaviors such as SQL dialect, execute listener provider, record mapper provider, connections used (Connecton, DataSource, or a ConnectionProvider), statement types (PREPARED_STATEMENT the default, or STATIC_STATEMENT), logging, JDBC flags (such as timeout, max rows etc.). There are two common ways to create an instance

  • From a pre-existing configuration: DSLContext create = DSL.using(configuration);.
  • From ad-hoc arguments: DSLContext create = DSL.using(connection, dialect);.

IMHO, a DSLContext is really a configured connection to database. It is used to execute db statements and fetch results.

Transaction Management

jOOQ use a simple API to manage transactions. The following is an example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

create.transaction(configuration -> {
    AuthorRecord author =
    DSL.using(configuration)
    .insertInto(AUTHOR, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
    .values("George", "Orwell")
    .returning()
    .fetchOne();
    DSL.using(configuration)
    .insertInto(BOOK, BOOK.AUTHOR_ID, BOOK.TITLE)
    .values(author.getId(), "1984")
    .values(author.getId(), "Animal Farm")
    .execute();
    // Implicit commit executed here
});

The lambda expression receives a new configuration used within the local scope. It doesn’t use the globally scoped DSLContext reference. It supports nesting of transactions. By default, jOOQ uses DefaultTransactionProvider that implements java.sql.Savepoint.

Connection and Updatable Records

By default, all records fetched through jOOQ are “attached” to its DSLContext to make so-called updatable records. It is possible to write code like record.store() to save updates. The setting can be turn off by settings.withAttachRecords(false);

jOOQ can generate UpdatableRecord code for every entity table, i.e., a table that has a primary key. The object has refresh, store, and delete method running on the original connection.

Lazy Fetching And Later Fetching

The org.jooq.Result is a simple in-memory Java list object that has no cursor and fetch methods. To fetch records one-by-one, use fetchLazy() to get a cursor that holds an internal reference to java.sql.ResultSet that may need to be closed. the fetch().stream() returns a Java stream that holds an internal reference to java.sql.ResultSet.

Use CompletableFuture to delay fetching of data.

Code Generation

It generates org.jooq.Table for table metadata. For each table there can be a org.jooq.Record class and a table interface. It generated POJOS and org.jooq.DAO. It generates db sequences, procedures, UDTs, and etc.