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.
That’s the
UpdatableRecord
API. It has a few very useful features, including:
A 1:1 mapping to the underlying table
Every
UpdatableRecord
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
UpdatableRecord
that allows you to do this:
CustomerRecord customer = ctx.newRecord(CUSTOMER);
customer.setFirstName("John");
customer.setLastName("Doe");
customer.store();
The above statement will generate a JDBC statement like this:
INSERT INTO customer (first_name, last_name)
VALUES (?, ?)
Notice…
SQL DEFAULT expressions
… we only set the
FIRST_NAME
and
LAST_NAME
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
DEFAULT
expressions to be applied. This is true for both the
ID
serial column, as well as the
VIP
defaulted column.
The actual database record will look something like this:
id first_name last_name vip
-------------------------------------
1337 John Doe false
Notice how the
VIP
column is nullable, which means that an unknown
VIP
status is something our database design explicitly allows for. Of course, we could easily set that value to
NULL
as well, preventing the
DEFAULT
from applying. For example:
// Assuming you actually *need* the client round trip...
CustomerRecord customer = ctx.fetchOne(
CUSTOMER, CUSTOMER.ID.eq(1337));
customer.setVip(null);
customer.store();
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:
Advantages
- 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
NULL
explicitly, and a value not being available from the DML statement. Think of JavaScript’s difference between null
and undefined
.
Disadvantages
- 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
DEFAULT
values, whose application is activated when a
Record.changed()
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
null
, if you choose a reference type for your data. What does
null
mean in that context? It’s not possible to know. A lot of jOOQ users will want that
null
value to encode the SQL
DEFAULT
value, not the SQL
NULL
value. But when you’re loading your POJO content into a jOOQ record, you’re effectively setting all the column values to
NULL
explicitly, because all the
Record.changed()
flags will be set:
// Load the customer record from a POJO
CustomerRecord customer = ctx.newRecord(CUSTOMER, customerPojo);
customer.update();
Now, quite likely, the resulting query will be:
UPDATE customer
SET
first_name = ?,
last_name = ?,
vip = ?
WHERE id = ?
But what if your
CustomerPojo
was retrieved from a JSON document, e.g. like this one?
{
"id": 1337,
"lastName": "Smith"
}
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:
UPDATE customer
SET last_name = ?
WHERE id = ?
Clearly, we shouldn’t set the
FIRST_NAME
nor
VIP
values to
NULL
.
Both the JSON document structure and the jOOQ
UpdatableRecord
can encode the difference between
undefined
/
DEFAULT
and
null
/
NULL
. But the POJO cannot. So, the only reasonable thing for jOOQ to do is to set all
Record.changed()
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
Record
.
Like this:
Like Loading...
Thank you for the post, you helped me a lot.