The numerous the explanation why you need to execute your jOOQ queries with jOOQ

Beforehand on this weblog, I’ve written a publish explaining why you need to use jOOQ’s code generator, regardless of the opportunity of utilizing jOOQ with out it. Similarly, as I’ve answered quite a few jOOQ questions on Stack Overflow, the place somebody used jOOQ to construct a question, however then executed it elsewhere, together with on:

• JPA
• JDBC / R2DBC
• JdbcTemplate (by Spring)
• And so forth.

jOOQ itself isn’t opinionated and tries to accommodate all potential use-cases. Each jOOQ Question can render its SQL utilizing Question.getSQL(), and produce bind values with Question.getBindValues(), so in precept, executing jOOQ queries elsewhere is completely potential.

Some legitimate use-cases for doing this:

• You employ jOOQ just for 2-3 dynamic queries in an in any other case JPA based mostly utility, and it is advisable to fetch entities (not DTOs) with these queries. An instance from the handbook, right here.

  (When you use jOOQ for tons of queries, you’ll most likely begin questioning in the event you nonetheless want entities within the first place.)

That’s just about it. An invalid approach overrated use-case is:

• You wish to migrate slowly to utilizing jOOQ, as a result of every thing else remains to be utilizing JdbcTemplate, for instance. I’ll clarify later why this isn’t a very good use-case for extracting SQL from jOOQ.

Within the following article, I wish to present by instance the quite a few advantages of executing queries with jOOQ, and by consequence, why you need to go “all in” on utilizing jOOQ.

This text tries to omit all the advantages of constructing a question with jOOQ, assuming you’ve already made the choice that jOOQ is the fitting alternative for question constructing.

Sort security

Certainly one of jOOQ’s fundamental advantages is its kind security each when writing SQL in addition to when sustaining it. Quite a lot of it’s achieved utilizing jOOQ’s DSL and code technology, however that’s not all. It’s also possible to revenue from kind security when executing queries with jOOQ. For instance, right here’s a question that kind safely fetches a nested SQL assortment right into a Java Map:

// That is the goal knowledge kind
report Movie(
    String title,
    Map income
) {}

// This question is totally kind protected. Change it, it will not compile anymore
Checklist end result =
ctx.choose(
        FILM.TITLE,
        multiset(
            choose(
                PAYMENT.PAYMENT_DATE.forged(LOCALDATE), 
                sum(PAYMENT.AMOUNT))
            .from(PAYMENT)
            .the place(PAYMENT.rental().stock().FILM_ID
                .eq(FILM.FILM_ID))
            .groupBy(PAYMENT.PAYMENT_DATE.forged(LOCALDATE))
            .orderBy(PAYMENT.PAYMENT_DATE.forged(LOCALDATE))
        )
        // Convert Subject>>
        // to Subject

Once more, the constructing of the question is already kind protected and that’s nice. However way more than that, the ultimate fetch(mapping(Movie::new)) name can also be kind protected! It should produce a worth that adheres to the construction (String, Map), which is what the question produces. Extra within the linked weblog publish.

You possibly can’t get this stage of kind security (and mapping) from another execution engine. When you extract the SQL string and bind values, you’re again to the JDBC stage, the place the end result set isn’t identified:

• In JDBC (together with JdbcTemplate), all ResultSet content material is tremendous generic. The variety of columns isn’t identified, their positions aren’t identified, their knowledge varieties aren’t identified to the compiler.
• In JPA’s DTO fetching APIs, you’ll simply get an Object[], which isn’t significantly better than with JDBC. I’d argue it’s a step again from JDBC, since you don’t even get an API anymore.

You don’t have to make use of jOOQ’s kind security on a regular basis, you’ll be able to at all times choose out of it, however at the very least, by default, it’s there!

Instance: Reactive querying

An amazing instance for this kind security is once you work with R2DBC to run a reactive question. I don’t assume anybody prefers executing the question on R2DBC immediately, provided that with jOOQ, a question can simply be embedded e.g. in a reactor Flux, for computerized execution and mapping.

report Desk(String schema, String desk) {}
 
Flux.from(ctx
        .choose(
            INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
            INFORMATION_SCHEMA.TABLES.TABLE_NAME)
        .from(INFORMATION_SCHEMA.TABLES))
 
    // Sort protected mapping from Record2 to Desk::new
    .map(Information.mapping(Desk::new))
    .doOnNext(System.out::println)
    .subscribe();

Mapping

The earlier instance already implied that mapping is out there robotically in jOOQ. There are quite a few methods to map a jOOQ Document or Document[N] kind to some consumer kind. The most well-liked methods embrace:

• The historic DefaultRecordMapper, which is reflection based mostly and makes use of the End result.into(Class) API
• The extra lately added kind protected report mapper that maps Document[N] varieties onto constructor references (or another operate), as within the above instance.

However mapping of information is just not every thing there’s, there’s additionally knowledge kind conversion!

Execution emulations

Some SQL options are primarily emulated at runtime when executing queries utilizing jOOQ. These embrace:

These options that quite a few jOOQ customers have come to like will not be usable outdoors of jOOQ. The generated SQL for these queries encodes the nested collections and information utilizing SQL/XML or SQL/JSON, relying on the dialect. In fact, you would re-implement the unmarshalling of JSON to a Java object once more in your personal knowledge entry layer, however why? jOOQ’s works very effectively, and as talked about above, is even kind protected. When you reimplemented this your self, you’d most likely not obtain the identical kind security stage.

One other cool execution factor is the:

Which emulates batching of consecutive SQL statements robotically, with none API intervention.

Consumer outlined varieties

If you wish to work with consumer outlined varieties each on the server facet in addition to on the consumer facet, all the information kind bindings are built-in in jOOQ and work out of the field. For instance, in PostgreSQL or Oracle (barely completely different syntax):

CREATE TYPE title AS (
  first_name TEXT,
  last_name TEXT
);

CREATE TABLE consumer (
  id BIGINT PRIMARY KEY,
  title title NOT NULL
);

Not solely will the code generator choose up these varieties for you, however it’s also possible to fetch them in a sort protected approach:

End result> r =
ctx.choose(USER.ID, USER.NAME)
   .from(USER)
   .fetch();

After which, clearly, apply kind protected or reflective mapping on that report, no matter you favor. I don’t assume such UDT assist would work as effectively with different execution modes. You would attempt it. The generated UDT varieties implement JDBC’s SQLData, so you need to be capable of bind them to a JDBC assertion out of the field. However there are nonetheless edge circumstances.

Saved procedures

Binding OUT or IN OUT parameters is a little bit of a trouble through the decrease stage APIs of JDBC, R2DBC, or JPA. Why not simply use jOOQ, once more, to execute a saved process name? Given:

CREATE OR REPLACE PROCEDURE my_proc (
  i1 NUMBER,
  io1 IN OUT NUMBER,
  o1 OUT NUMBER,
  o2 OUT NUMBER,
  io2 IN OUT NUMBER,
  i2 NUMBER
) IS
BEGIN
  o1 := io1;
  io1 := i1;
 
  o2 := io2;
  io2 := i2;
END my_proc;

What do you favor? This (JDBC)?

attempt (CallableStatement s = c.prepareCall(
    "{ name my_proc(?, ?, ?, ?, ?, ?) }"
)) {
 
    // Set all enter values
    s.setInt(1, 1); // i1
    s.setInt(2, 2); // io1
    s.setInt(5, 5); // io2
    s.setInt(6, 6); // i2
 
    // Register all output values with their varieties
    s.registerOutParameter(2, Sorts.INTEGER); // io1
    s.registerOutParameter(3, Sorts.INTEGER); // o1
    s.registerOutParameter(4, Sorts.INTEGER); // o2
    s.registerOutParameter(5, Sorts.INTEGER); // io2
 
    s.executeUpdate();
 
    System.out.println("io1 = " + s.getInt(2));
    System.out.println("o1 = " + s.getInt(3));
    System.out.println("o2 = " + s.getInt(4));
    System.out.println("io2 = " + s.getInt(5));
}

Or this?

// Quick type, passing arguments by index (kind protected):
MyProc end result = Routines.myProc(configuration, 1, 2, 5, 6);

// Specific type, passing arguments by title (kind protected):
MyProc name = new MyProc();
name.setI1(1);
name.setIo1(2);
name.setIo2(5);
name.setI2(6);
name.execute(configuration);
 
System.out.println("io1 = " + name.getIo1());
System.out.println("o1 = " + name.getO1());
System.out.println("o2 = " + name.getO2());
System.out.println("io2 = " + name.getIo2());

This comparability turns into much more apparent, once you attempt to name saved procedures that settle for / return consumer outlined varieties.

Fetching identification values

That is so painful throughout SQL dialects and JDBC drivers! Some SQL dialects have native assist, together with:

• Db2, H2: FINAL TABLE (the information change delta desk)
• Firebird, MariaDB, Oracle, PostgreSQL: RETURNING (although, in Oracle, there are lots of challenges)
• SQL Server: OUTPUT

However in any other case, usually a number of queries have to be executed, or various JDBC API must be used. If you wish to have a glimpse on the painful work jOOQ does for you, look right here.

Easy CRUD

In case you’re utilizing JPA, that is most likely not jOOQ’s killer characteristic, as JPA is a extra refined ORM than jOOQ, mapping associations and all. However in the event you’re not utilizing JPA (e.g. JdbcTemplate or JDBC immediately), you then is perhaps very repetitively writing INSERT, UPDATE, DELETE, MERGE statements, questioning life decisions, slightly than merely utilizing the jOOQ API for CRUD utilizing the UpdatableRecord API.

Handbook DML has its place, particularly for bulk knowledge processing, however apart from that, which do you favor?

IF new_record THEN
  INSERT INTO t (a, b, c) VALUES (1, 2, 3) RETURNING id INTO :id;
ELSE
  UPDATE t SET a = 1, b = 2, c = 3 WHERE id = :id;
END IF;

Or simply:

t.setA(1);
t.setB(2);
t.setC(3);
t.retailer();

By the best way, your TRecord is in fact generated, and it may be imported from JSON or no matter, see under!

Import and export of information

jOOQ helps out of the field import/export of information from/to quite a few knowledge codecs, together with:

Higher defaults

In comparison with JDBC, jOOQ implements higher defaults for many builders. This doesn’t imply that JDBC obtained it incorrect. JDBC made the fitting decisions for the aim it was made for: A low stage community protocol abstraction SPI. For jOOQ, utilizing JDBC below the hood has been tremendous highly effective.

However for customers, it’s annoying that every thing is at all times:

The above results in quite a lot of:

• Useful resource administration with try-with-resources
• Handbook reuse of assets, reminiscent of PreparedStatement, which produces laborious to keep up stateful code

With jOOQ, every thing a question produces is fetched into reminiscence eagerly by default, which is the default most customers want, permitting for quicker closing of assets (together with ResultSet, Assertion, Connection, behind the scenes). In fact, you’ll be able to nonetheless choose into lazy streaming processing of information in the event you want that, together with reactively utilizing R2DBC!

Far more

There’s much more, which is value mentioning:

Hardly any advantage of executing outdoors of jOOQ

As I promised, I wished to elucidate why there’s hardly any advantage of executing outdoors of jOOQ, until you wish to fetch knowledge right into a JPA entity, in case of which you want JPA to handle the entity lifecycle for you.

However when fetching DTOs, you don’t profit from utilizing JPA to execute a jOOQ question. It’s very straightforward to let jOOQ run a question immediately on a JPA managed transaction. Flushing is important both approach, so there’s no profit. Aside from that, JPA, JDBC, JdbcTemplate don’t do something:

• That jOOQ can’t do equally effectively or higher
• That jOOQ doesn’t match into (transactions, connection lifecycle, mapping, and so forth.)

jOOQ can be utilized as a drop-in alternative for another approach of executing a value-based SQL question, i.e. whenevery you map knowledge into DTOs slightly than entities. It might map knowledge to any goal knowledge construction together with any type of DTO (traditional POJO, Java 16 information, kotlin knowledge lessons, scala case lessons, and so forth. and so forth.) or XML, JSON, CSV as seen earlier than.

Actually, likelihood is you’ll be eradicating tons of repetitive boilerplate in the event you’re shifting to jOOQ from the earlier lower-level fetching and mapping code.

Conclusion

Identical to within the earlier article about why you need to use jOOQ with code technology, this text ought to have satisfied you to go all in on all of jOOQ’s advantages, not simply the question constructing. Quite a lot of thought (and I imply A LOT) has gone into the design of those options and APIs. I’m constructive that you will see that them higher than the handbook plumbing, when you get the grasp of it.