A steadily encountered doubt folks have when utilizing jOOQ is to determine when a “complicated” question needs to be written utilizing jOOQ API vs. when it needs to be carried out utilizing native SQL.
The jOOQ guide is stuffed with facet by facet examples of the identical question, e.g.
Utilizing jOOQ:
ctx.choose(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, rely())
.from(AUTHOR)
.be 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 word 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 a number of jOOQ issues, together with:
- Easier bind values
- Templating for dynamic textual content primarily based 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.
Execs:
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 best way sometimes:
Let’s shortly take a look at these two gadgets.
- CTE and derived tables need to be declared up entrance in jOOQ, moderately than embedding them within the question. Which means that generally, there’s no straightforward technique to maintain jOOQ’s typical kind security working, and also you’re again to composing queries utilizing string identifiers. When the question is dynamic, this strategy 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 comply with a check pushed improvement (TDD) strategy to creating your jOOQ queries purely in Java, and working them ideally on testcontainers as we’ve described right here, it could be that you just’re extra comfy writing your SQL question in native SQL, e.g. in Dbeaver or every other SQL editor of your alternative. 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 that will nonetheless not really feel “proper,” particularly when you need to edit the question once more later.
The perfect 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, nevertheless it will not be proper for sure “sorts of SQL queries.”
These sorts are complicated static queries. From a jOOQ perspective, you will get very very far, except 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 are feeling a sure question is simply too complicated for jOOQ is to both:
Or perhaps even higher, extract its logic into:
- A SQL view
- A SQL desk valued perform
Views are supported by all main RDBMS and really underappreciated. Desk valued features could be much more composable, as they take arguments, and might even be inlined by some RDBMS (e.g. SQL Server). In each instances, 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 advocate you comply with a TDD strategy to creating your software, utilizing integration assessments. In case you do this, then including views and desk valued features to your schema utilizing Flyway or Liquibase or every other technique of database change administration will likely be simple.
After the change is utilized, you’ll regenerate your jOOQ code, and you may instantly use the brand new schema object in your Java software with none lack of kind security.
Utilizing such a realistic strategy, you will get one of the best of each the jOOQ and the native SQL worlds.
