How to get the best of both jOOQ and native SQL worlds

A frequently encountered doubt people have when using jOOQ is to decide when a “complex” query should be written using jOOQ API vs. when it should be implemented using native SQL.

The jOOQ manual is full of side by side examples of the same query, e.g.

Using jOOQ:

ctx.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
   .from(AUTHOR)
   .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
   .groupBy(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .fetch();

Using native SQL:

SELECT author.first_name, author.last_name, COUNT(*)
FROM author
JOIN book ON author.id = book.author_id
GROUP BY author.id, author.first_name, author.last_name;

In the native SQL case, do note that you can still use jOOQ’s plain SQL templating API, ideally using Java text blocks, so you can still profit from a few jOOQ things, including:

• Simpler bind values
• Templating for dynamic text based SQL
• All the mapping utilities
• The transaction API or R2DBC support

With jOOQ, you’d then write:

ctx.fetch(
    """
    SELECT author.first_name, author.last_name, COUNT(*)
    FROM author
    JOIN book ON author.id = book.author_id
    GROUP BY author.id, author.first_name, author.last_name
    """
);

The obvious pros and cons

First off, there are some obvious pros and cons of using jOOQ in any given setting.

Pros:

You’ll hardly find anything better than jOOQ:

All the pros are explained in the articles linked from the above links, so I won’t repeat the benefits here anymore.

Cons:

jOOQ can get in the way occasionally:

Let’s quickly look at these two items.

• CTE and derived tables have to be declared up front in jOOQ, rather than embedding them in the query. This means that in most cases, there’s no easy way to keep jOOQ’s usual type safety working, and you’re back to composing queries using string identifiers. When the query is dynamic, this approach is still very strong. But when it’s static, then jOOQ may appear to be causing more usability issues than solving problems.
• While it is totally possible to follow a test driven development (TDD) approach to developing your jOOQ queries purely in Java, and running them preferably on testcontainers as we’ve described here, it may well be that you’re more at ease writing your SQL query in native SQL, e.g. in Dbeaver or any other SQL editor of your choice. In that case, you’d have to translate your completed query to jOOQ once it works. We do offer an automated translation service (use the “Java dialect”), but that may still not feel “right,” especially when you have to edit the query again later.

The best of both worlds

jOOQ won’t try to make you use jOOQ where it doesn’t fit. In a previous article, we’ve already elaborated when an ORM (implementing object graph persistence) works better. In this case, we’re discussing pure SQL, where jOOQ shines compared to ORMs, but it may not be right for certain “kinds of SQL queries.”

Those kinds are complex static queries. From a jOOQ perspective, you can get very very far, unless you’re using some really advanced vendor specific SQL feature, like the Oracle MODEL clause. But these things are a matter of taste. What jOOQ recommends you do in case you feel a certain query is too complex for jOOQ is to either:

Or maybe even better, extract its logic into:

• A SQL view
• A SQL table valued function

Views are supported by all major RDBMS and very underappreciated. Table valued functions can be even more composable, as they take arguments, and can even be inlined by some RDBMS (e.g. SQL Server). In both cases, you get to keep your native SQL syntax, but at the same time, you profit from type safety as the objects are compiled by the RDBMS.

Plus, as I’ve mentioned before, we really recommend you follow a TDD approach to developing your application, using integration tests. If you do that, then adding views and table valued functions to your schema using Flyway or Liquibase or any other means of database change management will be straightforward.

After the change is applied, you’ll regenerate your jOOQ code, and you can immediately use the new schema object in your Java application without any loss of type safety.

Using such a pragmatic approach, you can get the best of both the jOOQ and the native SQL worlds.