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:
- Group results by
AUTHOR
, mapping keys toAuthorRecord
just like jOOQ’sfetchGroups()
- For each
AUTHOR
filter out thoseBOOK
records whoseBOOK.ID
isnull
. Given thatBOOK.ID
is the primary key, the only reason why it could benull
is because of the left join - Map values to
BookRecord
, just like jOOQ’sfetchGroups()
- 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.
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:
(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”:
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
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.
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.
Anyway. Great feedback to write a follow-up article on how to do this with tuple collectors!
+1
I think that would be a good complement to the topic