A Frequent Question: Does jOOQ Have a First Level Cache?

One of the more frequent questions people have when switching from JPA to jOOQ is how to migrate from using JPA’s first level cache?
There are two important things to notice here:

jOOQ is mainly used for what JPA folks call “projections”

If you’re using only JPA in your application, you may have gotten used to occasionally fetch DTOs through “projections”. The term “projection” in this context stems from relational algebra, where a projection is simply a SELECT clause in your SQL statement. Projections are useful when you know that the result of a query will only used for further data processing, but you’re not going to store any modifications to the data back into the database. There are two advantages to this:
  1. You can project arbitrary expressions, including things that cannot be mapped to entities
  2. You can bypass most of the entity management logic, including first and second level caches
When you’re doing this, you will be using SQL – mostly because JPQL (or HQL) are very limited in scope. Ideally, you would be using jOOQ as your projecting query will be type safe and vendor agnostic. You could even use jOOQ to only build the query and run by JPA, although if you’re not fetching entities, you’d lose all result type information that jOOQ would provide you with, otherwise. So, the advantage of using jOOQ for projections (rather than JPA) is obvious. Sticking to JPA is mainly justified in case you only have very few projection use-cases and they’re also very simple.

jOOQ can also be used for basic CRUD

The question from the above tweet hints at the idea that SQL is not a very good language to implement basic CRUD. Or as I tend to say:
What I mean by this is that it’s really boring to manually express individual statements like these all the time:

INSERT INTO foo (a, b) VALUES (?, ?)
INSERT INTO bar (a, b, c) VALUES (?, ?, ?)
UPDATE baz SET x = ? WHERE id = ?

With most such CRUD operations, we’re simply inserting all the columns, or a given subset of columns, into a target table. Or we’re modifying all the changed columns in that table. These statements are always the same, but they break as soon as we add / remove columns, so we need to fix them throughout our application. When you’re using an ORM like Hibernate, all you have to change is your annotated meta model, and the generated queries will adapt automatically throughout your application. That’s a huge win! Additional features Full-fledged ORMs like Hibernate come with tons of additional features, including:
  • A way to map relationships between entities
  • A way to cache entities in the client
Both of these features are very useful in more sophisticated CRUD use-cases, where an application desires to load, mutate, and persist a complex object graph with many involved entities. Is this really needed? However, in simple cases, it might be sufficient to load only 1-2 entities explicitly using jOOQ (jOOQ calls them UpdatableRecord), modify them, and store them back again into the database. In such cases, it often doesn’t make sense to cache the entity in the client, nor to model the entity relationship in the client. Instead, we can write code like this:

// Fetch an author
AuthorRecord author : create.fetchOne(AUTHOR, AUTHOR.ID.eq(1));

// Create a new author, if it doesn't exist yet
if (author == null) {
    author = create.newRecord(AUTHOR);

// Mark the author as a "distinguished" author and store it

// Executes an update on existing authors, or insert on new ones

Notice how we haven’t hand-written a single SQL statement. Instead, behind the scenes, jOOQ has generated the necessary INSERT or UPDATE statement for you. If this is sufficient, you definitely don’t need JPA, and can use a more lightweight programming model through using jOOQ directly. A few additional features are available, including:


The conclusion is, if you’ve found and read this article because you wanted to replace JPA’s first level cache while migrating to jOOQ is: Re-think your migration You don’t have to replace the entirety of JPA. If you need its more sophisticated features, by all means, keep using it along with jOOQ. However, if you don’t need its more sophisticated features and the above CRUD features in jOOQ are sufficient, let go of the idea of needing a first level cache and embrace moving more logic into your SQL queries.

4 thoughts on “A Frequent Question: Does jOOQ Have a First Level Cache?

  1. Is the AuthorRecord independent of the current JDBC Connection? For instance, can we load it on one HttpRequest, store it in the HttpSession, do changes to it over the course of other subsequent web requests, and synchronize it back with the database in the last read-write transaction of the workflow?

    1. The AuthorRecord has a reference to a jOOQ Configuration, which in turn has a reference to a ConnectionProvider. You can implement that ConnectionProvider in any way you want, including it reconnecting to some session/request scoped JDBC Connection, data source, etc.

      So yes, your use-case can work very easily.

  2. Very good, Lukas!

    Hibernate (and few other ORMs) works in stateful way, always considering the application has the same (old) instance in memory right before the user can modify and update it in database. That’s why developers miss features like the first level cache, life cycle events, dirty checking, entity listeners an so on.

    Ah! It seems like you forgot to set the URL on “RecordListeners (CRUD lifecycle event listeners)” link.

    1. Thanks Rafael. URL fixed.

      Well, the article explained (and see also Vlad’s comment) that you can have some kind of (old) instance in memory, namely an UpdatableRecord. It isn’t called first level cache, its life cycle events are simpler, it does have dirty checking, but all of this is limited to a single entity, not an entity connected to other entities through relationships.

      If that’s enough, then people aren’t really missing these features. If that’s not enough, then people shouldn’t ditch JPA for jOOQ.

Leave a Reply