Use ResultQuery.collect() to Implement Powerful Mappings

Posted on by lukaseder

In our opinion, any Iterable<T> should offer a <R> collect(Collector<T, ?, R>) method to allow for transforming the the content to something else using standard JDK collectors, jOOλ collectors from org.jooq.lambda.Agg or your own.

When using jOOQ, you don’t have to wait for the JDK to finally add these useful utilities to the Iterable API. jOOQ’s ResultQuery<R> already implements Iterable<R>, and offers additional convenience like collect() on top of it.

For example, using a Java 16 record type:

record Book (int id, String title) {}

List<Book> books =
ctx.select(BOOK.ID, BOOK.TITLE)
   .from(BOOK)
   .collect(Collectors.mapping(
        r -> r.into(Book.class),
        Collectors.toList()
   ));

There are other ways to map things, but why not use a Collector. The best thing about Collector types is, they compose, type safely, and arbitrarily, almost like Stream pipelines.

I found a very interesting use-case recently on Stack Overflow. The problem there was that fetchGroups() is quite simple and not left-join aware, meaning that when an AUTHOR (parent) has no BOOK (child), instead of an empty list, there will be a list with a single NULL item:

Map<AuthorRecord, List<BookRecord>> result =
ctx.select()
   .from(AUTHOR)
   .leftJoin(BOOK).onKey()
   .fetchGroups(AUTHOR, BOOK);

The above works well for inner joins, but it doesn’t really make sense for left joins. We should fix this in jOOQ, of course (https://github.com/jOOQ/jOOQ/issues/11888), but using Collectors, you can already work around this problem today.

Simply write

Map<AuthorRecord, List<BookRecord>> result =
ctx.select()
   .from(AUTHOR)
   .leftJoin(BOOK).onKey()
   .collect(groupingBy(
        r -> r.into(AUTHOR), 
        filtering(
            r -> r.get(BOOK.ID) != null, 
            mapping(
                r -> r.into(BOOK), 
                toList()
            )
        )
    ));

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

Step by step:

  1. Group results by AUTHOR, mapping keys to AuthorRecord just like jOOQ’s fetchGroups()
  2. For each AUTHOR filter out those BOOK records whose BOOK.ID is null. Given that BOOK.ID is the primary key, the only reason why it could be null is because of the left join
  3. Map values to BookRecord, just like jOOQ’s fetchGroups()
  4. Collect the child records into a list.

And you’re done. Just like when you use the ResultQuery as an Iterable in a foreach loop, the collect() call executes your query automatically, managing all resources, bypassing the intermediate Result data structure, which isn’t needed here.

Published by lukaseder

I made jOOQ

5 thoughts on “Use ResultQuery.collect() to Implement Powerful Mappings”

  1. Great interaction with record construction!

    I want to solve similar issue but I have some additional relations involved.

    To adopt this Author and Book example let’s assume that we have third table like ARTICLE and we want to find Article with its Author and with all Book of given Author and we want to store it in java record:

    record ArticleAndAuthorAndBooks (Article article, Author author, List books)

    (I know that’s silly but I didn’t find a better extension to provided example)
    Lets assume that we want to search by a name of the Article. I have such code but I don’t see an option to remove stream on keySet and solve the issue with single jooq “flow”:

    dslContext.select(DSL.asterisk())
		.from(ARTICLE)
		.join(AUTHOR).onKey(ARTICLE.AUTHOR_ID)
		.join(BOOK).onKey(BOOK.AUTHOR_ID)
		.where(ARTICLE.NAME.eq("FOO"))
		.collect(Collectors.groupingBy(
				r -> new ArticleAndAuthorAndBooks(r.into(ARTICLE), r.into(AUTHOR), null),
				Collectors.mapping(r -> r.into(BOOK), Collectors.toList())
		))
		.entrySet().stream()
		.map(entry -> new ArticleAndAuthorAndBooks(entry.getKey().article, entry.getKey().author, entry.getValue()))
		.findFirst();

    For me this null in grouping is a hack. Is there any option to simplify this solution? Maybe there is a different construction for it and I just treat “groupingBy” as a hammer and want to resolve every issue with it? :D

    Reply

    1. Have you considered using MULTISET or MULTISET_AGG, instead, to nest your collections? See: https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/

      Of course, you can still join and deduplicate your denormalised result set, at least as long as you only join down a single path (otherwise there’d be cartesian products that you cannot deduplicate anymore). In your particular case, I think that Collectors.teeing() could help, or jOOλ’s tuple collectors, in order to collect several things in parallel, and combine them to your ArticleAndAuthorAndBooks wrapper only in the finisher function.

      Reply

      1. Hmm MULTISET looks like a best solution. I haven’t use this feature yet. I will give it a try for sure.
        Thx for quick reply and proposed options.

        Reply

Leave a Reply