Perform CRUD with Active Records

Lukas EderSeptember 15th, 2015Last Updated: December 6th, 2023

0 270 3 minutes read

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!

1. Introduction
2. Simple active record operations
3. Optimistic locking

1. Introduction

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.

2. Simple active record operations

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. AuthorRecord implements UpdatableRecord, which has a variety of useful methods:

store() to INSERT or UPDATE the record
insert() to INSERT the record
update() to UPDATE the record
delete() to DELETE the record
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();

jOOQ’s 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 UpdatableRecord.

3. Optimistic locking

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 REC_TIMESTAMP values:

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)

Notice how 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 WHERE clause.

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 UpdatableRecord, including insert(), update(), and delete().

jOOQ supports three modes of optimistic locking:

Using a dedicated TIMESTAMP column that tracks the modification date
Using a dedicated NUMBER column that tracks the version number
Using value comparison. This is the default if no timestamp or version columns are configured for the code generator