Enterprise Java

Perform CRUD with Active Records

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

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();

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:

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);

// Read the record by refreshing it based on the primary key value
author = dsl.newRecord(AUTHOR);

// Update the record with a new value

// Delete the record again

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");

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");

book2.setTitle("Another Title");

… 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

Want to know how to develop your skillset to become a Java Rockstar?

Join our newsletter to start rocking!

To get you started we give you our best selling eBooks for FREE!


1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design


and many more ....


Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions


Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments
Back to top button