jooq single query with one to many relationship

There are many ways to materialise a nested collection with SQL, and / or with jOOQ. I'm just going through some of them:

Using joins

If you don't deeply nest those collections, denormalising (flattening) your results with a JOIN might do the trick for you, without adding too much overhead as data is being duplicated. Essentially, you'll write:

Map<ExperimentRecord, Result<Record>> map =
DSL.using(configuration)
   .select()
   .from(EXPERIMENT)
   .join(TAGS)
   .on(...)
   .fetchGroups(EXPERIMENT);

The above map contains experiment records as keys, and nested collections containing all the tags as values.

Creating two queries

If you want to materialise a complex object graph, using joins might no longer be optimal. Instead, you probably want to collect the data in your client from two distinct queries:

Result<ExperimentRecord> experiments = 
DSL.using(configuration)
   .selectFrom(EXPERIMENT)
   .fetch();

And

Result<TagsRecord> tags =
DSL.using(configuration)
   .selectFrom(TAGS)
   .where(... restrict to the previous experiments ...)
   .fetch();
 

And now, merge the two results in your client's memory, e.g.

experiments.stream()
           .map(e -> new ExperimentWithTags(
                e, 
                tags.stream()
                    .filter(t -> e.getId().equals(t.getExperimentId()))
                    .collect(Collectors.toList())
           ));

Nesting collections using SQL/XML or SQL/JSON

This question didn't require it, but others may find this question in search for a way of nesting to-many relationships with jOOQ. I've provided an answer here. Starting with jOOQ 3.14, you can use your RDBMS's SQL/XML or SQL/JSON capabilities, and then use Jackson, Gson, or JAXB to nest collections like this:

List<Experiment> experiments =
ctx.select(
      EXPERIMENT.asterisk(),
      field(
        select(jsonArrayAgg(jsonObject(TAGS.fields())))
        .from(TAGS)
        .where(TAGS.EXPERIMENT_ID.eq(EXPERIMENT.ID))
      ).as("tags")
    )
   .from(EXPERIMENT)
   .fetchInto(Experiment.class);

Where Experiment is a custom Java class like this:

class Experiment {
  long id;
  String name;
  List<Tag> tags;
}

class Tag {
  long id;
  String name;
}

Nesting collections using MULTISET

Even better than the above, you can hide using SQL/XML or SQL/JSON behind jOOQ 3.15's new MULTISET operator support. Assuming the above Java classes are Java 16 records (or any other immutable classes), you can even map nested collections type safely into your DTOs:

List<Experiment> experiments =
ctx.select(
      EXPERIMENT.ID,
      EXPERIMENT.NAME,
      multiset(
        select(TAGS.ID, TAGS.NAME)
        .from(TAGS)
        .where(TAGS.EXPERIMENT_ID.eq(EXPERIMENT.ID))
      ).as("tags").convertFrom(r -> r.map(Records.mapping(Tag::new)))
    )
   .from(EXPERIMENT)
   .fetch(Records.mapping(Experiment::new));

Where Experiment is a custom Java class like this:

record Experiment(long id, String name, List<Tag> tags) {}
record Tag(long id, String name) {}

See also this blog post for more information.

You can now use SimpleFlatMapper to map your result to a Tuple2<ExperimentRecord, List<TagRecord>>. All you need to do is.

1 - create a mapper, specify the key column, assumed it would be id

JdbcMapper mapper = 
    JdbcMapperFactory
     .newInstance()
     .addKeys(EXPERIMENT.ID.getName())
     .newMapper(new TypeReference<Tuple2<ExperimentRecord, List<TagRecord>>>() {});

2 - use the mapper on the ResultSet of your query

try (ResultSet rs = DSL.using(configuration)
   .select()
   .from(EXPERIMENT)
   .join(TAGS)
   .on(...)
   .fetchResultSet()) {
    Stream<Tuple2<ExperimentRecord, List<TagRecord>>> stream = mapper.stream(rs);
    ....
}

See here for more details