Mind is Software

Ying’s thoughts about software and business

jOOQ Configuraiton and Execution

This is the part 2 of a study note based on the official jOOQ document. It describes the jOOQ configuraiton and execution.

1 Connection and DataSource

jOOQ uses a JDBC java.sql.Connection to create a java.sql.Statment or a java.sql.PreparedStatement object to execute query. You provide a connection in configuration and control the lifecycle and manage transactions by yourself. Internally, jOOQ use the DefaultConnectionProvider that exposes transaction control methods.

If you use a connection from javax.sql.DataSource from a Spring context, the connection is closed, actually released, after query execution by jOOQ. In this case, the DataSourceConnectionProvider is used.

A custom connection provider needs to define two methods: acquire and release.

2 Custom Data

Custom data, is more or less a string identifier, can be configured into the execution context and be accessed by custom exectue listner and custom query parts.

3 Configuration

A configuration returns a DSLContext instance that is used to execute queries. A congfiguration has the following attributes:

  • SQL Dialect: defined in org.jooq.SQLDialect enum type. It affects the SQL rendering and value binding.
  • Connection and DataSource: using connection requires explicit close and transaction management. DataSource allows connection pool.
  • Custom data: allows ExecuteListener and custom configuration data.
  • Object qualification: whether to use catalog and schema names.
  • Render name/identifier style: QUOTED (the default), AS_IS, LOWER, and UPPER.
  • Keyword style: AS_IS (the default), LOWER, UPPER and PASCAL.
  • Parameter types: JDBC only supports ?. Others may support INDEXED(the default), NAMED, NAMED_OR_INLINED, and INLINED.
  • Statement type: static statement or prepared statement (sending bind variables to DB, the default).
  • Auto-attach: by default, all records fetched are attached to the configuration that created them. This allows updatable records.
  • JDBC flags: JDBC flags can be set globally or per statement. queryTimeout, maxRows, fetchSize.

4 SQL Execution


jOOQ can fetch data inot POJOs. It can find mapping meta-information by JPA @Column or to the best-matching constructor, attribute or setter. jOOQ can fetch data into abstract classes or interfaces using HashMap wrapped in a java.lang.reflect.Proxy. POJOs can be load back to records to be stored. If jOOQ generates DAOs, those DAOs can be used to search, update and delete record.

You can define a custom RecordMapperProvider and configure it to provide default record mapper. ModelMapper and SimpleFlatMapper provide an explict jOOQ intergration.

Updatable Records

The code generator generates a org.jooq.UpdatableRecord for every table that has a primary key. Tables without a primary key are considered non-updatable by jOOQ and are generated as org.jooq.TableRecord class and they don’t support store(), refresh() and delete() method. A record fetched from the db is attached to the configuration that create it. It has refresh, store and delete methods. jOOQ maintains an internal state for every column value: the value itself, the original value, the changed flag. When create a new record, jOOQ retrieves its ID after store. For attached records, jOOQ always lazy-fectch relevant records without caching.

Optimistic Locking

If set Settings().withExecuteWithOptimisticLocking(true)), for update and delete statement, jOOQ runs a SELECT .. FOR UPDATE statement and check the record version, if the version is modified, throw a DataChangedException.

Batch Execution

Use create.batrchStore(books) to batch execute all update and insert statements.

Transaction Management

There are four ways to manage transactions in Java/SQL:

  • Use vendor-specific COMMIT, ROLLBACK statements.
  • Call JDBC Connection.commit(), Connection.rollback().
  • Use third-party transaction management libraries like Spring TX.
  • Use Java EE JTA.

jOOQ provides an API to run transaction: create.transaction(configuration -> {...}). Some org.jooq.TransactionProvider implementation like Spring uses a global configuration, the jOOQ transaction API alls locally scoped configuration. An implementation can pass the implementation of the following interfaces.

// for transaction(TransactionRunnable)
public interface TransactionalRunnable {
    void run(Configuration configuration) throws Exception;

// for transactionResult(TransactionCallable)
public interface TransactionalCallable<T> {
    T run(Configuration configuration) throws Exception;

Any uncaught exception will rollback the transaction. jOOQ supports nested transactions. The org.jooq.impl.DefaultTransactionProvider supports nested transactions.


jOOQ uses org.jooq.exception.DataAccessException to wrap all java.sql.SQLException and has several subtypes such as DataChangedException, DataTypeExcepton, DetachedException, InvalidResultException, and MappingExcception.

You can specify a org.jooq.ExecuteListener to override jOOQ’s exception handling.


Some jOOQ performance issues are:

  • cost to construct queries
  • time to render SQL strings
  • time to bind values to prepared statements
  • time to fetch result because jOOQ always fetches complete java.sql.ResultSet into memory. User lazy fetching and scroll over an open cursor if necessary.

Working with JPA

First, jOOQ can be used as type-safe native query to fetch untyuped data. If native query is augumented with the entity class, JPA can map the result to the entity, however, it is unsafe. Another method is to use SqlResultMapping to map query result to JPA entity.