Previously on this blog, I’ve written a post explaining why you should use jOOQ’s code generator, despite the possibility of using jOOQ without it. In a similar fashion, as I’ve answered numerous jOOQ questions on Stack Overflow, where someone used jOOQ to build a query, but then executed it elsewhere, including on:
- JPA
- JDBC / R2DBC
- JdbcTemplate (by Spring)
- Etc.
jOOQ itself isn’t opinionated and tries to accommodate all possible use-cases. Every jOOQ Query can render its SQL using Query.getSQL(), and produce bind values with Query.getBindValues(), so in principle, executing jOOQ queries elsewhere is totally possible.
Some valid use-cases for doing this:
- You use jOOQ only for 2-3 dynamic queries in an otherwise JPA based application, and you need to fetch entities (not DTOs) with those queries. An example from the manual, here.
(If you use jOOQ for tons of queries, you’ll probably start wondering if you still need entities in the first place.)
That’s pretty much it. An invalid way overrated use-case is:
- You want to migrate slowly to using jOOQ, because everything else is still using JdbcTemplate, for example. I’ll explain later why this isn’t a good use-case for extracting SQL from jOOQ.
In the following article, I want to show by example the numerous benefits of executing queries with jOOQ, and by consequence, why you should go “all in” on using jOOQ.
This article tries to omit all the benefits of building a query with jOOQ, assuming you’ve already made the decision that jOOQ is the right choice for query building.
Type safety
One of jOOQ’s main benefits is its type safety both when writing SQL as well as when maintaining it. A lot of it is achieved using jOOQ’s DSL and code generation, but that’s not all. You can also profit from type safety when executing queries with jOOQ. For example, here’s a query that type safely fetches a nested SQL collection into a Java Map:
// This is the target data type
record Film(
String title,
Map<LocalDate, BigDecimal> revenue
) {}
// This query is entirely type safe. Change it, it won't compile anymore
List<Film> result =
ctx.select(
FILM.TITLE,
multiset(
select(
PAYMENT.PAYMENT_DATE.cast(LOCALDATE),
sum(PAYMENT.AMOUNT))
.from(PAYMENT)
.where(PAYMENT.rental().inventory().FILM_ID
.eq(FILM.FILM_ID))
.groupBy(PAYMENT.PAYMENT_DATE.cast(LOCALDATE))
.orderBy(PAYMENT.PAYMENT_DATE.cast(LOCALDATE))
)
// Convert Field<Result<Record2<LocalDate, BigDecimal>>>
// to Field<Map<LocalDate, BigDecimal>>
.convertFrom(r -> r.collect(Records.intoMap())
)
.from(FILM)
.orderBy(FILM.TITLE)
// Convert Record2<String, Map<LocalDate, BigDecimal>>
// to List<Film>
.fetch(Records.mapping(Film::new))
Again, the building of the query is already type safe and that’s great. But much more than that, the final fetch(mapping(Film::new)) call is also type safe! It must produce a value that adheres to the structure (String, Map<LocalDate, BigDecimal>), which is what the query produces. More in the linked blog post.
You can’t get this level of type safety (and mapping) from any other execution engine. Once you extract the SQL string and bind values, you’re back to the JDBC level, where the result set isn’t known:
- In JDBC (including JdbcTemplate), all
ResultSetcontent is super generic. The number of columns isn’t known, their positions aren’t known, their data types aren’t known to the compiler. - In JPA’s DTO fetching APIs, you’ll just get an
Object[], which isn’t much better than with JDBC. I’d argue it’s a step back from JDBC, because you don’t even get an API anymore.
You don’t have to use jOOQ’s type safety all the time, you can always opt out of it, but at least, by default, it’s there!
Example: Reactive querying
A great example for this type safety is when you work with R2DBC to run a reactive query. I don’t think anyone prefers executing the query on R2DBC directly, given that with jOOQ, a query can just be embedded e.g. in a reactor Flux, for automatic execution and mapping.
record Table(String schema, String table) {}
Flux.from(ctx
.select(
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA,
INFORMATION_SCHEMA.TABLES.TABLE_NAME)
.from(INFORMATION_SCHEMA.TABLES))
// Type safe mapping from Record2<String, String> to Table::new
.map(Records.mapping(Table::new))
.doOnNext(System.out::println)
.subscribe();
Mapping
The previous example already implied that mapping is available automatically in jOOQ. There are numerous ways to map a jOOQ Record or Record[N] type to some user type. The most popular ways include:
- The historic
DefaultRecordMapper, which is reflection based and uses theResult.into(Class)API - The more recently added type safe record mapper that maps
Record[N]types onto constructor references (or any other function), as in the above example.
But mapping of records is not everything there is, there is also data type conversion!
Execution emulations
Some SQL features are mainly emulated at runtime when executing queries using jOOQ. These include:
These features that numerous jOOQ users have come to love are not usable outside of jOOQ. The generated SQL for these queries encodes the nested collections and records using SQL/XML or SQL/JSON, depending on the dialect. Of course, you could re-implement the unmarshalling of JSON to a Java object again in your own data access layer, but why? jOOQ’s works very well, and as mentioned above, is even type safe. If you reimplemented this yourself, you’d probably not achieve the same type safety level.
Another cool execution thing is the:
Which emulates batching of consecutive SQL statements automatically, without any API intervention.
User defined types
If you want to work with user defined types both on the server side as well as on the client side, all the data type bindings are built-in in jOOQ and work out of the box. For example, in PostgreSQL or Oracle (slightly different syntax):
CREATE TYPE name AS (
first_name TEXT,
last_name TEXT
);
CREATE TABLE user (
id BIGINT PRIMARY KEY,
name name NOT NULL
);
Not only will the code generator pick up these types for you, but you can also fetch them in a type safe way:
Result<Record2<Long, NameRecord>> r =
ctx.select(USER.ID, USER.NAME)
.from(USER)
.fetch();
And then, obviously, apply type safe or reflective mapping on that record, whatever you prefer. I don’t think such UDT support would work as well with other execution modes. You could try it. The generated UDT types implement JDBC’s SQLData, so you should be able to bind them to a JDBC statement out of the box. But there are still edge cases.
Stored procedures
Binding OUT or IN OUT parameters is a bit of a hassle via the lower level APIs of JDBC, R2DBC, or JPA. Why not just use jOOQ, again, to execute a stored procedure call? 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 prefer? This (JDBC)?
try (CallableStatement s = c.prepareCall(
"{ call my_proc(?, ?, ?, ?, ?, ?) }"
)) {
// Set all input 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 types
s.registerOutParameter(2, Types.INTEGER); // io1
s.registerOutParameter(3, Types.INTEGER); // o1
s.registerOutParameter(4, Types.INTEGER); // o2
s.registerOutParameter(5, Types.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?
// Short form, passing arguments by index (type safe):
MyProc result = Routines.myProc(configuration, 1, 2, 5, 6);
// Explicit form, passing arguments by name (type safe):
MyProc call = new MyProc();
call.setI1(1);
call.setIo1(2);
call.setIo2(5);
call.setI2(6);
call.execute(configuration);
System.out.println("io1 = " + call.getIo1());
System.out.println("o1 = " + call.getO1());
System.out.println("o2 = " + call.getO2());
System.out.println("io2 = " + call.getIo2());
This comparison becomes even more obvious, when you try to call stored procedures that accept / return user defined types.
Fetching identity values
This is so painful across SQL dialects and JDBC drivers! Some SQL dialects have native support, including:
- Db2, H2:
FINAL TABLE(the data change delta table) - Firebird, MariaDB, Oracle, PostgreSQL:
RETURNING(though, in Oracle, there are many challenges) - SQL Server:
OUTPUT
But otherwise, often multiple queries need to be executed, or alternative JDBC API needs to be used. If you want to have a glimpse at the painful work jOOQ does for you, look here.
Simple CRUD
In case you’re using JPA, this is probably not jOOQ’s killer feature, as JPA is a more sophisticated ORM than jOOQ, mapping associations and all. But if you’re not using JPA (e.g. JdbcTemplate or JDBC directly), then you might be very repetitively writing INSERT, UPDATE, DELETE, MERGE statements, questioning life choices, rather than simply using the jOOQ API for CRUD using the UpdatableRecord API.
Manual DML has its place, especially for bulk data processing, but other than that, which do you prefer?
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 just:
t.setA(1);
t.setB(2);
t.setC(3);
t.store();
By the way, your TRecord is of course generated, and it can be imported from JSON or whatever, see below!
Import and export of data
jOOQ supports out of the box import/export of data from/to numerous data formats, including:
Better defaults
Compared to JDBC, jOOQ implements better defaults for most developers. This doesn’t mean that JDBC got it wrong. JDBC made the right choices for the purpose it was made for: A low level network protocol abstraction SPI. For jOOQ, using JDBC under the hood has been super powerful.
But for users, it’s annoying that everything is always:
The above leads to a lot of:
- Resource management with
try-with-resources - Manual reuse of resources, such as
PreparedStatement, which produces hard to maintain stateful code
With jOOQ, everything a query produces is fetched into memory eagerly by default, which is the default most users need, allowing for faster closing of resources (including ResultSet, Statement, Connection, behind the scenes). Of course, you can still opt into lazy streaming processing of data if you need that, including reactively using R2DBC!
Much more
There’s a lot more, which is worth mentioning:
Hardly any benefit of executing outside of jOOQ
As I promised, I wanted to explain why there is hardly any benefit of executing outside of jOOQ, unless you want to fetch data into a JPA entity, in case of which you need JPA to manage the entity lifecycle for you.
But when fetching DTOs, you don’t benefit from using JPA to execute a jOOQ query. It’s very easy to let jOOQ run a query directly on a JPA managed transaction. Flushing is necessary either way, so there’s no benefit. Other than that, JPA, JDBC, JdbcTemplate don’t do anything:
- That jOOQ can’t do equally well or better
- That jOOQ doesn’t fit into (transactions, connection lifecycle, mapping, etc.)
jOOQ can be used as a drop-in replacement for any other way of executing a value-based SQL query, i.e. whenevery you map data into DTOs rather than entities. It can map data to any target data structure including any form of DTO (classic POJO, Java 16 records, kotlin data classes, scala case classes, etc. etc.) or XML, JSON, CSV as seen before.
In fact, chances are you’ll be removing tons of repetitive boilerplate if you’re moving to jOOQ from the previous lower-level fetching and mapping code.
Conclusion
Just like in the previous article about why you should use jOOQ with code generation, this article should have convinced you to go all in on all of jOOQ’s benefits, not just the query building. A lot of thought (and I mean A LOT) has gone into the design of these features and APIs. I’m positive that you will find them better than the manual plumbing, once you get the hang of it.
