Beforehand on this weblog, I’ve written a submit explaining why it is best to use jOOQ’s code generator, regardless of the opportunity of utilizing jOOQ with out it. In a similar way, 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 many others.
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 utilize jOOQ just for 2-3 dynamic queries in an in any other case JPA based mostly software, and you must 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 in all probability begin questioning in case you nonetheless want entities within the first place.)
That’s just about it. An invalid use-case is:
- You need to migrate slowly to utilizing jOOQ, as a result of every part else remains to be utilizing JdbcTemplate, for instance. I’ll clarify later why this isn’t a superb use-case for extracting SQL from jOOQ.
Within the following article, I need 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 fitting selection for question constructing.
Sort security
Certainly one of jOOQ’s important advantages is its sort security each when writing SQL in addition to when sustaining it. A number of it’s achieved utilizing jOOQ’s DSL and code era, 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 knowledge sort
document Movie(
String title,
Map<LocalDate, BigDecimal> income
) {}
// This question is fully sort secure. Change it, it will not compile anymore
Checklist<Movie> 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<Consequence<Record2<LocalDate, BigDecimal>>>
// to Subject<Map<LocalDate, BigDecimal>>
.convertFrom(r -> r.gather(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 also be sort secure! It should produce a price that adheres to the construction (String, Map<LocalDate, BigDecimal>), which is what the question produces. Extra within the linked weblog submit.
You possibly can’t get this stage of sort 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 recognized:
- In JDBC (together with JdbcTemplate), all
ResultSetcontent material is tremendous generic. The variety of columns isn’t recognized, their positions aren’t recognized, their knowledge varieties 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 may all the time choose out of it, however at the very least, by default, it’s there!
Instance: Reactive querying
An incredible instance for this sort security is whenever you work with R2DBC to run a reactive question. I don’t assume anybody prefers executing the question on R2DBC instantly, on condition that with jOOQ, a question can simply be embedded e.g. in a reactor Flux, for automated execution and mapping.
document Desk(String schema, String desk) {}
Flux.from(ctx
.choose(
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
INFORMATION_SCHEMA.TABLES.TABLE_NAME)
.from(INFORMATION_SCHEMA.TABLES))
// Sort 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 obtainable robotically in jOOQ. There are quite a few methods to map a jOOQ Document or Document[N] sort to some person sort. The most well-liked methods embrace:
- The historic
DefaultRecordMapper, which is reflection based mostly and makes use of theConsequence.into(Class)API - The extra not too long ago added sort secure document mapper that maps
Document[N]varieties onto constructor references (or another perform), as within the above instance.
However mapping of information shouldn’t be every part there may be, there may be additionally knowledge 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 should not usable exterior 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 can re-implement the unmarshalling of JSON to a Java object once more in your personal knowledge 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 in all probability not obtain the identical sort 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 person outlined varieties each on the server facet in addition to on the shopper facet, all the information sort 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 person (
id BIGINT PRIMARY KEY,
title title NOT NULL
);
Not solely will the code generator decide up these varieties for you, however you too can fetch them in a kind secure manner:
Consequence<Record2<Lengthy, NameRecord>> r =
ctx.choose(USER.ID, USER.NAME)
.from(USER)
.fetch();
After which, clearly, apply sort secure or reflective mapping on that document, no matter you like. I don’t assume such UDT help would work as properly with different execution modes. You may strive it. The generated UDT varieties implement JDBC’s SQLData, so it is best to be capable to bind them to a JDBC assertion out of the field. However there are nonetheless edge instances.
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 like? 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 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 (sort secure):
MyProc end result = Routines.myProc(configuration, 1, 2, 5, 6);
// Express type, 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, whenever you attempt to name saved procedures that settle for / return person outlined varieties.
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 various challenges) - SQL Server:
OUTPUT
However in any other case, usually a number of queries should 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 in all probability not jOOQ’s killer characteristic, as JPA is a extra refined ORM than jOOQ, mapping associations and all. However in case you’re not utilizing JPA (e.g. JdbcTemplate or JDBC instantly), then you definitely could be very repetitively writing INSERT, UPDATE, DELETE, MERGE statements, questioning life decisions, reasonably than merely utilizing the jOOQ API for CRUD utilizing the UpdatableRecord API.
Guide DML has its place, particularly for bulk knowledge processing, however apart from that, which do you like?
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 beneath!
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 bought it improper. JDBC made the fitting decisions for the aim it was made for: A low stage community protocol abstraction SPI. For jOOQ, utilizing JDBC underneath the hood has been tremendous highly effective.
However for customers, it’s annoying that every part is all the time:
The above results in numerous:
- Useful resource administration with
try-with-resources - Guide reuse of sources, akin to
PreparedStatement, which produces exhausting to keep up stateful code
With jOOQ, every part 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). In fact, you may nonetheless choose into lazy streaming processing of information in case you want that, together with reactively utilizing R2DBC!
Way more
There’s much more, which is value mentioning:
Hardly any good thing about executing exterior of jOOQ
As I promised, I wished to clarify why there may be hardly any good thing about executing exterior of jOOQ, except you need 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 instantly on a JPA managed transaction. Flushing is critical both manner, 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 many others.)
jOOQ can be utilized as a drop-in substitute for another manner of executing a value-based SQL question, i.e. whenevery you map knowledge into DTOs reasonably than entities. It could actually 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 many others. and many others.) or XML, JSON, CSV as seen earlier than.
The truth is, likelihood is you’ll be eradicating tons of repetitive boilerplate in case you’re shifting to jOOQ from the earlier lower-level fetching and mapping code.
Conclusion
Identical to within the earlier article about why it is best to use jOOQ with code era, this text ought to have satisfied you to go all in on all of jOOQ’s advantages, not simply the question constructing. A number of thought (and I imply A LOT) has gone into the design of those options and APIs. I’m constructive that you’ll discover them higher than the handbook plumbing, when you get the cling of it.
