jOOQ 3.15 introduced the concept of an ad-hoc converter, a converter that is applied "ad-hoc" to a single query. It uses the same underlying mechanisms as any ordinary Converter that is attached to generated code for use in every query. An example of such an ad-hoc converter is this: // Without the converter, assuming BOOK.ID … Continue reading How to use jOOQ’s Converters with UNION Operations
Tag: Multiset
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: JPA JDBC / … Continue reading Why You Should Execute jOOQ Queries With jOOQ
How to Filter a SQL Nested Collection by a Value
I stumbled upon a very interesting question on Stack Overflow about how to use jOOQ's MULTISET operator to nest a collection, and then filter the result by whether that nested collection contains a value. The question is jOOQ specific, but imagine, you have a query that nests collections using JSON in PostgreSQL. Assuming, as always, … Continue reading How to Filter a SQL Nested Collection by a Value
The Performance of Various To-Many Nesting Algorithms
It's been a while since jOOQ 3.15 has been released with its revolutionary standard SQL MULTISET emulation feature. A thing that has been long overdue and which I promised on twitter a few times is to run a few benchmarks comparing the performance of various approaches to nesting to-many relationships with jOOQ. This article will … Continue reading The Performance of Various To-Many Nesting Algorithms
How to Typesafely Map a Nested SQL Collection into a Nested Java Map with jOOQ
A really cool, recent question on Stack Overflow was about how to map a nested collection into a Java Map with jOOQ. In the past, I've blogged about the powerful MULTISET operator many times, which allows for nesting collections in jOOQ. This time, instead of nesting data into a List<UserType>, why not nest it in … Continue reading How to Typesafely Map a Nested SQL Collection into a Nested Java Map with jOOQ
Use MULTISET Predicates to Compare Data Sets
Questions that might be a bit more difficult to solve using ordinary SQL are questions of the kind: What films have the same actors as a given film X? As always, we're using the sakila database for this example. What would be a possible way to solve this with SQL (for example, PostgreSQL, to be … Continue reading Use MULTISET Predicates to Compare Data Sets
Projecting Type Safe Nested TableRecords with jOOQ 3.17
A long standing feature request has seen little love from the jOOQ community, despite a lot of people probably wanting it. It goes by the unimpressive title Let Table<R> extend SelectField<R>: https://github.com/jOOQ/jOOQ/issues/4727 What does the feature mean, specifically? The awesome PostgreSQL Let's have a look at a really cool PostgreSQL feature. In PostgreSQL, it is … Continue reading Projecting Type Safe Nested TableRecords with jOOQ 3.17
No More MultipleBagFetchException Thanks to Multiset Nested Collections
I've recently stumbled upon this interesting Stack Overflow question about Hibernate's popular MultipleBagFetchException. The question is super popular, and the answers are plenty. The various limitations are discussed throughout the question, it all boils down to a simple fact: Joins are the wrong tool to nest collections. Given a schema like the Sakila database: There … Continue reading No More MultipleBagFetchException Thanks to Multiset Nested Collections
Using JDK Collectors to De-duplicate parent/child nested collections
In classic SQL (i.e. before jOOQ's awesome MULTISET operator), nested collections were fetched using ordinary (outer) joins. An example of such a query would be a query running against the sakila database to fetch actors and their films. Using jOOQ: Result<?> result = ctx.select( ACTOR.ACTOR_ID, ACTOR.FIRST_NAME, ACTOR.LAST_NAME, FILM.FILM_ID, FILM.TITLE) .from(ACTOR) .leftJoin(FILM_ACTOR).on(ACTOR.ACTOR_ID.eq(FILM_ACTOR.ACTOR_ID)) .leftJoin(FILM).on(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID)) .orderBy( ACTOR.ACTOR_ID, FILM.FILM_ID) … Continue reading Using JDK Collectors to De-duplicate parent/child nested collections
3.15.0 Release with Support for R2DBC, Nested ROW, ARRAY, and MULTISET types, 5 new SQL dialects, CREATE PROCEDURE, FUNCTION, and TRIGGER support and Much More
R2DBC What a lot of users have been waiting for: jOOQ 3.15 is reactive, thanks to the new native R2DBC integration. Recent versions already implemented the reactive streams Publisher SPI, but now we're not cheating anymore. We're not longer blocking. Just wrap your R2DBC ConnectionFactory configured jOOQ query in a Flux (or any reactive streams … Continue reading 3.15.0 Release with Support for R2DBC, Nested ROW, ARRAY, and MULTISET types, 5 new SQL dialects, CREATE PROCEDURE, FUNCTION, and TRIGGER support and Much More