Use ResultQuery.acquire() to Implement Highly effective Mappings – Java, SQL and jOOQ.

In our opinion, any Iterable<T> ought to provide a <R> acquire(Collector<T, ?, R>) methodology to permit for reworking the the content material to one thing else utilizing normal JDK collectors, jOOλ collectors from org.jooq.lambda.Agg or your individual.

When utilizing jOOQ, you don’t have to attend for the JDK to lastly add these helpful utilities to the Iterable API. jOOQ’s ResultQuery<R> already implements Iterable<R>, and provides further comfort like acquire() on high of it.

For instance, utilizing a Java 16 file sort:

file E-book (int id, String title) {}

Listing<E-book> books =
ctx.choose(BOOK.ID, BOOK.TITLE)
   .from(BOOK)
   .acquire(Collectors.mapping(
        r -> r.into(E-book.class),
        Collectors.toList()
   ));

There are different methods to map issues, however why not use a Collector. One of the best factor about Collector varieties is, they compose, sort safely, and arbitrarily, nearly like Stream pipelines.

I discovered a really fascinating use-case not too long ago on Stack Overflow. The issue there was that fetchGroups() is kind of easy and never left-join conscious, which means that when an AUTHOR (dad or mum) has no BOOK (youngster), as an alternative of an empty checklist, there shall be a listing with a single NULL merchandise:

Map<AuthorRecord, Listing<BookRecord>> consequence =
ctx.choose()
   .from(AUTHOR)
   .leftJoin(BOOK).onKey()
   .fetchGroups(AUTHOR, BOOK);

The above works nicely for interior joins, nevertheless it doesn’t actually make sense for left joins. We should always repair this in jOOQ, after all (https://github.com/jOOQ/jOOQ/points/11888), however utilizing Collectors, you’ll be able to already work round this downside as we speak.

Merely write

Map<AuthorRecord, Listing<BookRecord>> consequence =
ctx.choose()
   .from(AUTHOR)
   .leftJoin(BOOK).onKey()
   .acquire(groupingBy(
        r -> r.into(AUTHOR), 
        filtering(
            r -> r.get(BOOK.ID) != null, 
            mapping(
                r -> r.into(BOOK), 
                toList()
            )
        )
    ));

// All assuming the same old static imports:
import static org.jooq.impl.DSL.*;
import static com.instance.generated.Tables.*;
import static java.util.stream.Collectors.*;

Step-by-step:

1. Group outcomes by AUTHOR, mapping keys to AuthorRecord similar to jOOQ’s fetchGroups()
2. For every AUTHOR filter out these BOOK data whose BOOK.ID is null. On condition that BOOK.ID is the first key, the one motive why it may very well be null is due to the left be a part of
3. Map values to BookRecord, similar to jOOQ’s fetchGroups()
4. Gather the kid data into a listing.

And also you’re achieved. Identical to if you use the ResultQuery as an Iterable in a foreach loop, the acquire() name executes your question robotically, managing all sources, bypassing the intermediate End result information construction, which isn’t wanted right here.