This article is part of our Academy Course titled jOOQ – Type safe DB querying.
jOOQ is a good choice in a Java application where SQL and the specific relational database are important. It is an alternative when JPA / Hibernate abstract too much, JDBC too little. It shows, how a modern domain-specific language can greatly increase developer productivity, internalising SQL into Java.
In this course, we’ll see how we can efficiently query databases using jOOQ. Check it out here!
While SQL is a very expressive language, most of your SQL is probably CRUD (Create, Read, Update, Delete). Writing such CRUD is boring and repetitive, which is why ORMs like Hibernate have emerged and have been successful at increasing developer productivity. But Hibernate makes a lot of assumptions (and restrictions) about your architecture, when often you really just want to operate on single records from tables.
Examples displayed in this section are also available from the org.jooq.academy.section2 package.
jOOQ knows “active records”, or also
UpdatableRecords, which can be loaded with “special” kinds of
SELECT statements, and which then keep track of dirty flags, internally. Here’s how you can update an author’s
DATE_OF_BIRTH without writing too much SQL:
AuthorRecord author = dsl.selectFrom(AUTHOR).where(AUTHOR.ID.eq(1)).fetchOne(); author.setDateOfBirth(Date.valueOf("2000-01-01")); author.store();
As the above example is only selecting from one table via
selectFrom(), jOOQ knows that the resulting record type will be an
AuthorRecord, i.e. an object generated by the code generator.
UpdatableRecord, which has a variety of useful methods:
- store() to
- insert() to
- update() to
- delete() to
- refresh() to refresh the record from the database
The following example section guides you through the complete lifecycle of creating, reading, updating, deleting such a record:
AuthorRecord author; // Create a new record and store it to the database. This will perform an INSERT statement author = dsl.newRecord(AUTHOR); author.setId(3); author.setFirstName("Alfred"); author.setLastName("Hitchcock"); author.store(); // Read the record by refreshing it based on the primary key value author = dsl.newRecord(AUTHOR); author.setId(3); author.refresh(); // Update the record with a new value author.setDateOfBirth(Date.valueOf("1899-08-13")); author.store(); // Delete the record again author.delete();
UpdatableRecords keep track of an internal “dirty” or “changed” state per column, which is used when calling
store() in order to only insert / update those values that have been changed in the
When performing CRUD, concurrent data access is often an issue that can be resolved in two ways:
- By using pessimistic locking
- By using optimistic locking
Pessimistic locking is rarely a good choice as deadlocks may occur easily when two processes lock several rows in the table in different order waiting for each other to complete. Optimistic locking is a much better suited solution. One process might just be lucky enough to finish the transaction before the other process tries (and fails). Here’s how this works with jOOQ.
In our sample data, the
BOOK table has a special “system” column called
REC_TIMESTAMP. The content of this column is fully managed by jOOQ whenever you run a CRUD operation on a
BookRecord, you don’t have to keep it up to date. Consider the following code example:
// Enable optimistic locking DSLContext dsl = DSL.using(connection, new Settings().withExecuteWithOptimisticLocking(true)); // Perform the CRUD with the above setting BookRecord book1 = dsl.selectFrom(BOOK).where(BOOK.ID.eq(1)).fetchOne(); book1.setTitle("New Title"); book1.store();
jOOQ will now execute an
UPDATE statement that also updates and checks the
update "PUBLIC"."BOOK" set "PUBLIC"."BOOK"."TITLE" = 'New Title', "PUBLIC"."BOOK"."REC_TIMESTAMP" = timestamp '2014-09-08 18:40:39.416' where ("PUBLIC"."BOOK"."ID" = 1 and "PUBLIC"."BOOK"."REC_TIMESTAMP" is null)
REC_TIMESTAMP is set to the current time in the
SET clause, while it is also checked to be
NULL (the initial value in the sample database) in the
If we now have two competing processes (or code sections in the same process) for this update, like this:
BookRecord book1 = dsl.selectFrom(BOOK).where(BOOK.ID.eq(1)).fetchOne(); BookRecord book2 = dsl.selectFrom(BOOK).where(BOOK.ID.eq(1)).fetchOne(); book1.setTitle("New Title"); book1.store(); book2.setTitle("Another Title"); book2.store();
… then we’ll witness a
DataChangedException on the second call to
store() (shortened stack trace):
org.jooq.exception.DataChangedException: Database record has been changed or doesn't exist any longer at org.jooq.impl.UpdatableRecordImpl.checkIfChanged(UpdatableRecordImpl.java:420) at org.jooq.impl.UpdatableRecordImpl.storeUpdate(UpdatableRecordImpl.java:193) at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:129) at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:121)
Optimistic locking is applied for operations of
jOOQ supports three modes of optimistic locking:
- Using a dedicated
TIMESTAMPcolumn that tracks the modification date
- Using a dedicated
NUMBERcolumn that tracks the version number
- Using value comparison. This is the default if no timestamp or version columns are configured for the code generator