PostHole
Compose Login
You are browsing eu.zone1 in read-only mode. Log in to participate.
rss-bridge 2023-01-18T11:46:25+00:00

Why You Should Execute jOOQ Queries With jOOQ

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: jOOQ itself isn’t … Continue reading Why You Should Execute jOOQ Queries With jOOQ →

Why You Should Execute jOOQ Queries With jOOQ

Posted on January 18, 2023January 18, 2023 by lukaseder

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 ResultSet content 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 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!

  • The Converter and Binding SPIs that can be attached to generated code to always auto-convert things for you. JPA has a similar mechanism for entities, but you cannot access these things when you extract the jOOQ SQL query in string form.

Execution emulations

Some SQL features are mainly emulated at runtime when executing queries using jOOQ. These include:

[...]

Original source

Reply