使用jOOQ将关联查询结果映射到含Set集合的DTO

针对你需要将关联查询结果映射到包含Set<Table1Record>的自定义DTO场景，下面提供两种实用的实现方案，假设关联关系为：

• Table1与Table2是一对一关联（Table1.table2_id = Table2.id）
• Table1与子Table1通过关联表Table3实现一对多关联（Table3.main_table1_id = Table1.id、Table3.sub_table1_id = Table1.id）
• Table1与Table4是一对一关联（Table1.table4_id = Table4.id）

你的DTO结构示例：

public class CustomDTO {
    private Table1Record mainTable1;
    private Table2Record table2;
    private Set<Table1Record> subTable1s;
    private Table4Record table4;

    // 全参构造器、Getter/Setter
    public CustomDTO(Table1Record mainTable1, Table2Record table2, Set<Table1Record> subTable1s, Table4Record table4) {
        this.mainTable1 = mainTable1;
        this.table2 = table2;
        this.subTable1s = subTable1s;
        this.table4 = table4;
    }
}

方案一：手动分组组装（通用兼容所有数据库） #

先查询所有关联数据，再按主Table1的主键分组，手动提取各关联实体并组装DTO。

1. 构建关联查询 #

注意给子Table1的字段加别名，避免和主Table1字段冲突：

Result<Record> result = DSL.using(configuration)
    .select(
        TABLE1.fields(),
        TABLE2.fields(),
        // 给子Table1字段加别名前缀
        TABLE1.as("SUB_TABLE1").fields().stream()
            .map(f -> f.as("sub_" + f.getName()))
            .toArray(Field[]::new),
        TABLE4.fields()
    )
    .from(TABLE1)
    .join(TABLE2).on(TABLE1.TABLE2_ID.eq(TABLE2.ID))
    .leftJoin(TABLE3).on(TABLE1.ID.eq(TABLE3.MAIN_TABLE1_ID))
    .leftJoin(TABLE1.as("SUB_TABLE1")).on(TABLE3.SUB_TABLE1_ID.eq(TABLE1.as("SUB_TABLE1").ID))
    .join(TABLE4).on(TABLE1.TABLE4_ID.eq(TABLE4.ID))
    .fetch();

2. 分组并组装DTO #

// 按主Table1Record分组，聚合相同主记录的所有关联行
Map<Table1Record, List<Record>> recordGroups = result.fetchGroups(r -> r.into(TABLE1));

List<CustomDTO> dtos = new ArrayList<>();
for (Map.Entry<Table1Record, List<Record>> entry : recordGroups.entrySet()) {
    Table1Record mainTable1 = entry.getKey();
    List<Record> groupRecords = entry.getValue();

    // 提取一对一关联的Table2、Table4（取分组内第一条即可）
    Table2Record table2 = groupRecords.get(0).into(TABLE2);
    Table4Record table4 = groupRecords.get(0).into(TABLE4);

    // 提取子Table1集合，过滤空值并去重
    Set<Table1Record> subTable1s = groupRecords.stream()
        .map(r -> r.into("SUB_TABLE1", Table1Record.class))
        .filter(Objects::nonNull) // 左连接可能返回空子记录
        .collect(Collectors.toSet());

    dtos.add(new CustomDTO(mainTable1, table2, subTable1s, table4));
}

方案二：利用聚合函数嵌套映射（适合支持聚合的数据库） #

如果你的数据库支持array_agg（如PostgreSQL）或类似聚合函数，可以直接在查询中聚合子记录，再映射到DTO，代码更简洁。

1. 带聚合的关联查询 #

Result<Record4<Table1Record, Table2Record, Set<Table1Record>, Table4Record>> result = DSL.using(configuration)
    .select(
        // 映射主Table1Record
        DSL.row(TABLE1.fields()).convertFrom(r -> r.into(Table1Record.class)),
        // 映射Table2Record
        DSL.row(TABLE2.fields()).convertFrom(r -> r.into(Table2Record.class)),
        // 聚合子Table1Record为Set
        DSL.arrayAgg(DSL.row(TABLE1.as("SUB_TABLE1").fields()))
            .filter(TABLE1.as("SUB_TABLE1").ID.isNotNull())
            .convertFrom(arr -> Arrays.stream(arr)
                .map(r -> r.into(Table1Record.class))
                .collect(Collectors.toSet())),
        // 映射Table4Record
        DSL.row(TABLE4.fields()).convertFrom(r -> r.into(Table4Record.class))
    )
    .from(TABLE1)
    .join(TABLE2).on(TABLE1.TABLE2_ID.eq(TABLE2.ID))
    .leftJoin(TABLE3).on(TABLE1.ID.eq(TABLE3.MAIN_TABLE1_ID))
    .leftJoin(TABLE1.as("SUB_TABLE1")).on(TABLE3.SUB_TABLE1_ID.eq(TABLE1.as("SUB_TABLE1").ID))
    .join(TABLE4).on(TABLE1.TABLE4_ID.eq(TABLE4.ID))
    // 按主表及一对一关联表的主键分组
    .groupBy(TABLE1.ID, TABLE2.ID, TABLE4.ID)
    .fetch();

2. 直接映射到DTO #

List<CustomDTO> dtos = result.stream()
    .map(r -> new CustomDTO(
        r.value1(),
        r.value2(),
        r.value3(),
        r.value4()
    ))
    .collect(Collectors.toList());

注意事项 #

• 若使用MySQL等不支持array_agg的数据库，可改用JSON_ARRAYAGG聚合为JSON数组，再通过自定义转换器将JSON转换为Set<Table1Record>。
• 左连接场景下需过滤空子记录，避免Set中混入null。
• 字段别名必须唯一，防止主表与子表字段冲突导致映射错误。

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