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.
from Java, SQL and jOOQ. https://ift.tt/3eU3Wjm
via IFTTT
No comments:
Post a Comment