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.SQLDialectenum 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
ExecuteListenerand custom configuration data. - Object qualification: whether to use catalog and schema names.
- Render name/identifier style:
QUOTED(the default),AS_IS,LOWER, andUPPER. - Keyword style:
AS_IS(the default),LOWER,UPPERandPASCAL. - Parameter types: JDBC only supports
?. Others may supportINDEXED(the default),NAMED,NAMED_OR_INLINED, andINLINED. - 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
POJOS
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,ROLLBACKstatements. - 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.
|
|
Any uncaught exception will rollback the transaction. jOOQ supports nested transactions. The org.jooq.impl.DefaultTransactionProvider supports nested transactions.
Exception
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.
Performance
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.ResultSetinto 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.