CODEWITHSUNDEEP
javajooq
Rico
Rico

Reputation: 634

jOOQ: fetch into Map<K, List<Record2<V,W>>

Abstract query

select A.*, B.*, C.*
from A
left join B on B.aId = A.aId
left join C on C.cId = B.cId

Idea
I want to fetch this into some object, which is grouped by A (each B has a unique C connected to it). For me the most logical type of object to fetch into, would be something like Map<A, List<Record2<B,C>>.

Code
I tried something like

using(configuration()).select(A.fields())
        .select(B.fields())
        .select(C.fields())
        .from(A)
        .leftJoin(B).on(B.aId.eq(A.aId)
        .leftJoin(C).on(C.cId.eq(B.cId)
        .fetchGroups(
            r -> r.into(A).into(APojo.class),
            r -> r.into(B).into(BPojo.class),
            r -> r.into(C).into(CPojo.class)); // Goes wrong because fetchGroups only accepts 2 arguments

Background of solution
I don't want to use fetch(), because all the records would contain duplicate data of A, which I want to avoid. I am converting it to a JSON object, where A would contain a list of B's and in which B contains the object C. To get this structure, Map<A, List<Result2<B,C>> would be perfect.

Upvotes: 5

Views: 7923

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

You have to manually group those <B, C> types into some data structure, e.g. jOOλ's Tuple2 type or also AbstractMap.SimpleEntry

Map<APojo, List<Tuple<BPojo, CPojo>>> result =
using(configuration()).select(A.fields())
        .select(B.fields())
        .select(C.fields())
        .from(A)
        .leftJoin(B).on(B.aId.eq(A.aId))
        .leftJoin(C).on(C.cId.eq(B.cId))
        .fetchGroups(
            r -> r.into(A).into(APojo.class),
            r -> tuple(
                     r.into(B).into(BPojo.class),
                     r.into(C).into(CPojo.class)));

An alternative would be to resort to using streams and nested maps:

Map<APojo, Map<BPojo, CPojo>> result =
using(configuration()).select(A.fields())
        .select(B.fields())
        .select(C.fields())
        .from(A)
        .leftJoin(B).on(B.aId.eq(A.aId))
        .leftJoin(C).on(C.cId.eq(B.cId))
        .fetch()
        .stream()
        .collect(Collectors.groupingBy(
            r -> r.into(A).into(APojo.class),
            Collectors.toMap(
                r -> r.into(B).into(BPojo.class),
                r -> r.into(C).into(CPojo.class))));

jOOQ 3.11 will include a ResultQuery.collect() method, so the fetchStream() call can be omitted:

Map<APojo, Map<BPojo, CPojo>> result =
using(configuration()).select(A.fields())
        .select(B.fields())
        .select(C.fields())
        .from(A)
        .leftJoin(B).on(B.aId.eq(A.aId))
        .leftJoin(C).on(C.cId.eq(B.cId))
        .collect(Collectors.groupingBy(
            r -> r.into(A).into(APojo.class),
            Collectors.toMap(
                r -> r.into(B).into(BPojo.class),
                r -> r.into(C).into(CPojo.class))));

Upvotes: 6

Related Questions