The numerous the explanation why it is best to execute your jOOQ queries with jOOQ

Beforehand on this weblog, I’ve written a put up explaining why it is best 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 on.

jOOQ itself isn’t opinionated and tries to accommodate all doable 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 doable.

Some legitimate use-cases for doing this:

• You employ jOOQ just for 2-3 dynamic queries in an in any other case JPA primarily based software, and it’s essential fetch entities (not DTOs) with these queries. An instance from the handbook, right here.

  (In the event 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 method overrated use-case is:

• You wish to migrate slowly to utilizing jOOQ, as a result of every little thing else continues to be utilizing JdbcTemplate, for instance. I’ll clarify later why this isn’t an excellent 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 it is best 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 appropriate alternative for question constructing.

Kind security

One among jOOQ’s predominant advantages is its sort security each when writing SQL in addition to when sustaining it. A variety of it’s achieved utilizing jOOQ’s DSL and code technology, however that’s not all. You can even revenue from sort security when executing queries with jOOQ. For instance, right here’s a question that sort safely fetches a nested SQL assortment right into a Java Map:

// That is the goal information sort
report Movie(
    String title,
    Map<LocalDate, BigDecimal> income
) {}

// This question is completely sort secure. Change it, it will not compile anymore
Checklist<Movie> consequence =
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<Outcome<Record2<LocalDate, BigDecimal>>>
        // to Subject<Map<LocalDate, BigDecimal>>
        .convertFrom(r -> r.accumulate(Information.intoMap())
   )
   .from(FILM)
   .orderBy(FILM.TITLE)
 
   // Convert Record2<String, Map<LocalDate, BigDecimal>>
   // to Checklist<Movie>
   .fetch(Information.mapping(Movie::new))

Once more, the constructing of the question is already sort secure and that’s nice. However way more than that, the ultimate fetch(mapping(Movie::new)) name can be sort secure! It should produce a worth that adheres to the construction (String, Map<LocalDate, BigDecimal>), which is what the question produces. Extra within the linked weblog put up.

You’ll be able to’t get this degree of sort security (and mapping) from another execution engine. When you extract the SQL string and bind values, you’re again to the JDBC degree, the place the consequence set isn’t recognized:

• In JDBC (together with JdbcTemplate), all ResultSet content material is tremendous generic. The variety of columns isn’t recognized, their positions aren’t recognized, their information sorts aren’t recognized 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 sort security on a regular basis, you possibly can all the time decide out of it, however at the very least, by default, it’s there!

Instance: Reactive querying

A terrific instance for this sort security is while you work with R2DBC to run a reactive question. I don’t suppose anybody prefers executing the question on R2DBC straight, 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))
 
    // Kind secure mapping from Record2<String, String> to Desk::new
    .map(Information.mapping(Desk::new))
    .doOnNext(System.out::println)
    .subscribe();

Mapping

The earlier instance already implied that mapping is on the market routinely in jOOQ. There are quite a few methods to map a jOOQ File or File[N] sort to some person sort. The most well-liked methods embrace:

• The historic DefaultRecordMapper, which is reflection primarily based and makes use of the Outcome.into(Class) API
• The extra just lately added sort secure report mapper that maps File[N] sorts onto constructor references (or another perform), as within the above instance.

However mapping of data just isn’t every little thing there’s, there’s additionally information sort 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 exterior of jOOQ. The generated SQL for these queries encodes the nested collections and data utilizing SQL/XML or SQL/JSON, relying on the dialect. After all, you may re-implement the unmarshalling of JSON to a Java object once more in your individual information entry layer, however why? jOOQ’s works very properly, and as talked about above, is even sort secure. In the event you reimplemented this your self, you’d most likely not obtain the identical sort security degree.

One other cool execution factor is the:

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

Consumer outlined sorts

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

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

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

Not solely will the code generator decide up these sorts for you, however you may also fetch them in a kind secure method:

Outcome<Record2<Lengthy, NameRecord>> r =
ctx.choose(USER.ID, USER.NAME)
   .from(USER)
   .fetch();

After which, clearly, apply sort secure or reflective mapping on that report, no matter you favor. I don’t suppose such UDT help would work as properly with different execution modes. You may strive it. The generated UDT sorts implement JDBC’s SQLData, so it is best to have the ability to 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 problem through the decrease degree 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)?

strive (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 sorts
    s.registerOutParameter(2, Varieties.INTEGER); // io1
    s.registerOutParameter(3, Varieties.INTEGER); // o1
    s.registerOutParameter(4, Varieties.INTEGER); // o2
    s.registerOutParameter(5, Varieties.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 kind, passing arguments by index (sort secure):
MyProc consequence = Routines.myProc(configuration, 1, 2, 5, 6);

// Express kind, passing arguments by title (sort secure):
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, while you attempt to name saved procedures that settle for / return person outlined sorts.

Fetching identification values

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

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

However in any other case, typically a number of queries have to be executed, or different 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 subtle ORM than jOOQ, mapping associations and all. However in the event you’re not utilizing JPA (e.g. JdbcTemplate or JDBC straight), then you definately is perhaps very repetitively writing INSERT, UPDATE, DELETE, MERGE statements, questioning life selections, reasonably than merely utilizing the jOOQ API for CRUD utilizing the UpdatableRecord API.

Guide DML has its place, particularly for bulk information processing, however aside 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 after all 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 information codecs, together with:

Higher defaults

In comparison with JDBC, jOOQ implements higher defaults for many builders. This doesn’t imply that JDBC acquired it unsuitable. JDBC made the appropriate selections for the aim it was made for: A low degree community protocol abstraction SPI. For jOOQ, utilizing JDBC beneath the hood has been tremendous highly effective.

However for customers, it’s annoying that every little thing is all the time:

The above results in quite a lot of:

• Useful resource administration with try-with-resources
• Guide reuse of sources, comparable to PreparedStatement, which produces exhausting to keep up stateful code

With jOOQ, every little thing a question produces is fetched into reminiscence eagerly by default, which is the default most customers want, permitting for quicker closing of sources (together with ResultSet, Assertion, Connection, behind the scenes). After all, you possibly can nonetheless decide into lazy streaming processing of information in the event you want that, together with reactively utilizing R2DBC!

Rather more

There’s much more, which is value mentioning:

Hardly any good thing about executing exterior of jOOQ

As I promised, I wished to elucidate why there’s hardly any good thing about executing exterior of jOOQ, except you wish to fetch information 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 straight on a JPA managed transaction. Flushing is critical both method, so there’s no profit. Apart from that, JPA, JDBC, JdbcTemplate don’t do something:

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

jOOQ can be utilized as a drop-in substitute for another method of executing a value-based SQL question, i.e. whenevery you map information into DTOs reasonably than entities. It will possibly map information to any goal information construction together with any type of DTO (basic POJO, Java 16 data, kotlin information courses, scala case courses, 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 transferring to jOOQ from the earlier lower-level fetching and mapping code.

Conclusion

Similar to within the earlier article about why it is best 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. A variety of thought (and I imply A LOT) has gone into the design of those options and APIs. I’m constructive that you’ll find them higher than the handbook plumbing, when you get the cling of it.