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:
|
|
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.