How to lazily fetch groups in JOOQ?

Question

I'm trying to write a query in which I fetch child records for my aggregate root using leftJoin like this:

dslContext.select()
    .from(PARENT)
    .leftJoin(CHILD)
    .on(CHILD.PARENT_ID.eq(PARENT.ID))
    .stream()

My problem is that I need to use stream because there are a very big number of records but this way I can't use fetchGroups which I would normally use when I need parent-child records.

This is how I'd use fetchGroups:

dslContext.select()
    .from(PARENT)
    .leftJoin(CHILD)
    .on(CHILD.PARENT_ID.eq(PARENT.ID))
    .fetchGroups(Parent.class, Child.class)

which would create a nice Map<Parent, Child> for me.

I tried to collect using Collectors.groupingBy but it is not straightforward how to use it and the docs don't explain it either.

How can I fetch parent-child records in one go lazily?

Answer 1

In jOOQ 3.11, we have added a new ResultQuery.collect() method, and we are planning on re-implementing most of our fetchXYZ() logic as standard Collector implementations, which can then be used with Stream to achieve exactly what you're looking for. The main driver for this is to avoid the proliferation of more of these fetchXYZ() overloads, and to offer more composability.

You can do it manually, of course:

Using Stream, in case you want to add additional operations to the stream pipeline

// Don't forget, this is a resourceful stream!
try (Stream<Record> stream = dslContext.select()
    .from(PARENT)
    .leftJoin(CHILD)
    .on(CHILD.PARENT_ID.eq(PARENT.ID))
    .stream()) {

    Map<Parent, List<Child>> result = stream.collect(
        Collectors.groupingBy(r -> r.into(Parent.class),
            Collectors.mapping(r -> r.into(Child.class), Collectors.toList())
        )
    );
}

The Javadoc of Collectors.groupingBy() has a similar example.

Using ResultQuery.collect(), if you don't need the stream pipeline.

Alternatively, using jOOQ 3.11's ResultQuery.collect()

Map<Parent, List<Child>> result = dslContext
    .select()
    .from(PARENT)
    .leftJoin(CHILD)
    .on(CHILD.PARENT_ID.eq(PARENT.ID))
    .collect(
        Collectors.groupingBy(r -> r.into(Parent.class),
            Collectors.mapping(r -> r.into(Child.class), Collectors.toList())
        )
    );