I am trying to fetch complex structure from the database using jOOQ. I will try to explain on an example where the problem is.
So, let’s imagine we have classes Foo, Bar and Bazz:
public class Foo {
private String fooName;
private List<Bar> bars;
}
public class Bar {
private Bazz bazz;
}
public class Bazz {
private String name;
}
I want to fetch and populate Foo -> List<Bar> -> Bazz relationship.
I tried with MULTISET
and it works, but I want to avoid subqueries. I want to fetch and map that using JOIN
. I wonder if it is possible to fetch that using reflection, something like this.
Here is an example with MULTISET
:
public List<FooResponse> findByFooName(String fooName) {
return ctx().select(FOO.asterisk(),
multiset(select(BAR.asterisk(),
BAR.bazz().as("bazz"))
.from(BAR)
.where(BAR.FOO_ID.eq(FOO.ID))).as("bars"))
.from(FOO)
.where(FOO.FOO_NAME.eq(fooName))
.fetchInto(FooResponse.class);
}
2
Answers
I've made a little research and found several solutions to the aforementioned problem.
Using multiset() -> the previous example
DSL.multiset()
worksUsing multisetAgg()
DSL.multisetAgg()
worksSELECT
statement must match the order of the parameters in constructorsUsing modelmapper
Using SimpleFlatMapper
In your particular case, you can nest with
JOIN
andGROUP BY
/MULTISET_AGG
, instead of using theMULTISET
value constructor, because you’re nesting only one level of nested collections (the second nesting is not a collection). The main difference will be what happens when you don’t have anyBAR
for aFOO
. With aggregation, this produces aNULL
value, not an empty collection.However, there are a few limitations in the API (e.g. #15008, #15007, #13937) making this a bit difficult using the
DefaultRecordMapper
(thefetchInto(X.class)
call). Here’s a solution using ad-hoc conversion and type safety:This is assuming you have the relevant constructors. If you don’t, you can always revert to
into(X.class)
calls on each level.One of the benefits of using jOOQ’s code generator is that you have type safe nested collection mapping as shown above.