The right way to get the most effective of each jOOQ and native SQL worlds

A continuously encountered doubt individuals have when utilizing jOOQ is to determine when a “advanced” question needs to be written utilizing jOOQ API vs. when it needs to be carried out utilizing native SQL.

The jOOQ handbook is filled with aspect by aspect examples of the identical question, e.g.

Utilizing jOOQ:

ctx.choose(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, rely())
   .from(AUTHOR)
   .be a part of(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
   .groupBy(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
   .fetch();

Utilizing native SQL:

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

Within the native SQL case, do notice that you would be able to nonetheless use jOOQ’s plain SQL templating API, ideally utilizing Java textual content blocks, so you’ll be able to nonetheless revenue from just a few jOOQ issues, together with:

• Easier bind values
• Templating for dynamic textual content based mostly SQL
• All of the mapping utilities
• The transaction API or R2DBC help

With jOOQ, you’d then write:

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

The plain execs and cons

First off, there are some apparent execs and cons of utilizing jOOQ in any given setting.

Professionals:

You’ll hardly discover something higher than jOOQ:

All the professionals are defined within the articles linked from the above hyperlinks, so I received’t repeat the advantages right here anymore.

Cons:

jOOQ can get in the way in which sometimes:

Let’s rapidly have a look at these two gadgets.

• CTE and derived tables need to be declared up entrance in jOOQ, reasonably than embedding them within the question. Because of this usually, there’s no simple option to hold jOOQ’s normal kind security working, and also you’re again to composing queries utilizing string identifiers. When the question is dynamic, this method remains to be very robust. However when it’s static, then jOOQ might look like inflicting extra usability points than fixing issues.
• Whereas it’s completely doable to observe a take a look at pushed growth (TDD) method to growing your jOOQ queries purely in Java, and operating them ideally on testcontainers as we’ve described right here, it might be that you simply’re extra comfy writing your SQL question in native SQL, e.g. in Dbeaver or another SQL editor of your selection. In that case, you’d need to translate your accomplished question to jOOQ as soon as it really works. We do provide an automatic translation service (use the “Java dialect”), however which will nonetheless not really feel “proper,” particularly when you need to edit the question once more later.

The most effective of each worlds

jOOQ received’t attempt to make you employ jOOQ the place it doesn’t match. In a earlier article, we’ve already elaborated when an ORM (implementing object graph persistence) works higher. On this case, we’re discussing pure SQL, the place jOOQ shines in comparison with ORMs, but it surely will not be proper for sure “sorts of SQL queries.”

These sorts are advanced static queries. From a jOOQ perspective, you may get very very far, until you’re utilizing some actually superior vendor particular SQL characteristic, just like the Oracle MODEL clause. However these items are a matter of style. What jOOQ recommends you do in case you’re feeling a sure question is just too advanced for jOOQ is to both:

Or perhaps even higher, extract its logic into:

• A SQL view
• A SQL desk valued operate

Views are supported by all main RDBMS and really underappreciated. Desk valued features will be much more composable, as they take arguments, and might even be inlined by some RDBMS (e.g. SQL Server). In each circumstances, you get to maintain your native SQL syntax, however on the similar time, you revenue from kind security because the objects are compiled by the RDBMS.

Plus, as I’ve talked about earlier than, we actually suggest you observe a TDD method to growing your utility, utilizing integration assessments. For those who do this, then including views and desk valued features to your schema utilizing Flyway or Liquibase or another technique of database change administration will likely be simple.

After the change is utilized, you’ll regenerate your jOOQ code, and you’ll instantly use the brand new schema object in your Java utility with none lack of kind security.

Utilizing such a practical method, you may get the most effective of each the jOOQ and the native SQL worlds.