While jOOQ is not a full fledged ORM (as in an object graph persistence framework
), there is still some convenience available to avoid hand-writing boring SQL for every day CRUD.
API. It has a few very useful features, including:
A 1:1 mapping to the underlying table
is mapped on a 1:1 basis to the underlying table (or view) of your database. For example, if you create a table like this (using PostgreSQL syntax):
CREATE TABLE customer (
id BIGSERIAL NOT NULL PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
vip BOOLEAN DEFAULT FALSE
… and if you’re using the code generator (which you should), then apart from the generated table that is used in SQL queries, you also have an
that allows you to do this:
CustomerRecord customer = ctx.newRecord(CUSTOMER);
The above statement will generate a JDBC statement like this:
INSERT INTO customer (first_name, last_name)
VALUES (?, ?)
SQL DEFAULT expressions
… we only set the
values on the record, and only those values have been included in the insert statement. The other columns are not included, which allows for SQL
expressions to be applied. This is true for both the
serial column, as well as the
The actual database record will look something like this:
id first_name last_name vip
1337 John Doe false
Notice how the
column is nullable, which means that an unknown
status is something our database design explicitly allows for. Of course, we could easily set that value to
as well, preventing the
from applying. For example:
// Assuming you actually *need* the client round trip...
CustomerRecord customer = ctx.fetchOne(
The above query would produce the following update statement:
UPDATE customer SET vip = ? WHERE id = ?
Sidenote: Please do not use this feature if you never really needed the data in the client. In that case, just write the
UPDATE statement and save a client-server roundtrip!
Notice again that only the column that we’ve explicitly changed
will be included in the update statement, not all the others. This has advantages and disadvantages:
- Even if the
CustomerRecord was fetched incompletely (e.g. through a query), we don’t need all the other columns to modify this one.
- If a trigger listens on columns that have been included in the actual query, we can distinguish between a value being set to
- If we allow users to change arbitrary columns on such a record, there is a very high number of possible resulting queries, which can hurt execution plan cache performance in the database.
This is the tradeoff. jOOQ opted for full support of SQL
values, whose application is activated when a
flag is not set. As soon as the flag is set, jOOQ assumes that the explicit setting of the value is wanton, and it will thus be propagated to the generated SQL query.
SQL DEFAULT expressions and POJOs
jOOQ Records can encode that difference. Plain Old Java Objects (POJOs) cannot. There is only
, if you choose a reference type for your data. What does
mean in that context? It’s not possible to know. A lot of jOOQ users will want that
value to encode the SQL
value, not the SQL
value. But when you’re loading your POJO content into a jOOQ record, you’re effectively setting all the column values to
explicitly, because all the
flags will be set:
// Load the customer record from a POJO
CustomerRecord customer = ctx.newRecord(CUSTOMER, customerPojo);
Now, quite likely, the resulting query will be:
first_name = ?,
last_name = ?,
vip = ?
WHERE id = ?
But what if your
was retrieved from a JSON document, e.g. like this one?
It looks like the intent of this document is for the record “John Doe” to be renamed to “John Smith”, so this update statement should have resulted instead:
SET last_name = ?
WHERE id = ?
Clearly, we shouldn’t set the
Both the JSON document structure and the jOOQ
can encode the difference between
. But the POJO cannot. So, the only reasonable thing for jOOQ to do is to set all
flags to true.
Of course, you can manually set them to false again to work around this limitation, or perhaps don’t use the intermediary POJO data structure, but load the JSON data directly into the jOOQ