如何用jOOQ将左连接查询结果映射为Map<A, List<Record2<B,C>>>

Let’s walk through how to achieve this exact mapping using jOOQ’s result handling and Java streams. Assuming you’re working with jOOQ’s generated table records (ARecord, BRecord, CRecord for tables A, B, C respectively), here’s a step-by-step solution:

1. Construct the jOOQ Query #

First, build your left join query to fetch all required fields from the three tables:

// Assuming `dsl` is your configured DSLContext instance
Result<Record> result = dsl.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();

2. Map the Result to Your Desired Structure #

Use Java streams with Collectors.groupingBy to group results by the ARecord, and Collectors.mapping to convert each row into a Record2<BRecord, CRecord> pair:

import org.jooq.Record2;
import org.jooq.Records;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

Map<ARecord, List<Record2<BRecord, CRecord>>> resultMap = result.stream()
    .collect(Collectors.groupingBy(
        // Extract the full A record from each combined result row
        record -> record.into(A),
        // Convert each row to a B-C pair and collect into a list
        Collectors.mapping(
            record -> Records.record(record.into(B), record.into(C)),
            Collectors.toList()
        )
    ));

Key Details to Consider: #

• Handling Null Entries: Since this is a left join, some rows will have BRecord (and corresponding CRecord) with all fields null. If you want to exclude these empty B entries, add a filter before mapping:

  Collectors.mapping(
      record -> Records.record(record.into(B), record.into(C)),
      Collectors.filtering(
          pair -> !pair.value1().getAid().isNull(), // Check if B's aId exists
          Collectors.toList()
      )
  )
  

• Grouping Logic: jOOQ’s generated records have proper equals() and hashCode() implementations based on all fields, so grouping by the full ARecord works reliably. If you prefer grouping by a specific key (like A’s primary ID), replace record.into(A) with record.get(A.AID) and adjust the map key type to match.
• Record2 Creation: Records.record() is a jOOQ utility that creates a Record2 instance from two objects, which perfectly fits your need to pair each B with its associated C.

内容的提问来源于stack exchange，提问作者Rico