JOOQ多层一对多关联查询映射POJO及MULTISET使用报错问题

问题1：JOOQ 3.14 JSON聚合查询TagDto字段全空修复 #

错误原因 #

你原有写法的核心问题是jsonEntry(jsonObject(Tag.fields()))的语法错误：

• jsonEntry()需要传入key, value两个参数，你只传了一个JSON对象，没有指定key，导致生成的JSON结构不符合TagDto的字段映射规则
• 另外你代码中TagDescription的子查询误写了from(orderDetailAddition)，应该是from(TagDescription)

修复后的代码 #

dslContext.select(
    Experiment.asterisk(),
    field(
        select(
            jsonArrayAgg(jsonObject(
                // 直接传入Tag表的所有字段，jsonObject会自动以字段名作为key，字段值作为value
                Tag.fields(),
                // 追加tagDescription字段
                jsonEntry("tagDescription", field(
                        select(jsonArrayAgg(jsonObject(TagDescription.fields())))
                            .from(TagDescription)
                            // 注意外键关联条件要和你的实际表结构对应
                            .where(TagDescription.TAG_ID.eq(Tag.ID))
                ))
            ))
        ).from(Tag)
         .where(Tag.EXPERIMENT_ID.eq(Experiment.ID))
    ).as("tags")
).from(Experiment)
.where(Experiment.NAME.eq("Name"))
.fetchInto(ExperimentDto.class);

JSON映射方式合理性说明 #

这种方案是合理的：

• 优势：仅需一次数据库查询即可拿到全量嵌套数据，IO成本低，对于中小数据量场景性能优异
• 劣势：SQL结构复杂，调试成本高，超大结果集下JSON序列化/反序列化会有额外性能开销

问题2：JOOQ 3.15+ MULTISET在MySQL 5.7下报错修复 #

错误原因 #

这个是MySQL 5.7的固有语法限制：MySQL 5.7及更早版本不允许FROM子句中的派生表引用外层查询的列。JOOQ 3.15+的MULTISET在适配MySQL 5.7时，默认会用派生表+GROUP_CONCAT的方式生成SQL，就会触发这个限制，导致找不到外层的experiment.id字段。

解决方案 #

方案1：升级MySQL到8.0+（推荐）

MySQL 8.0支持派生表关联外层字段，且提供原生JSON函数支持，JOOQ的MULTISET会生成更简洁高效的SQL，直接使用即可，无需额外配置。

方案2：继续使用修复后的JSON聚合方案

就是上文给出的3.14版本修复后的代码，在MySQL 5.7下可以正常运行。

方案3：批量查询+内存组装（避免N+1）

如果不想写复杂的JSON聚合SQL，可以分三次批量查询后在内存组装：

// 1. 查询符合条件的Experiment列表
List<ExperimentDto> experiments = dslContext.selectFrom(Experiment)
    .where(Experiment.NAME.eq("Name"))
    .fetchInto(ExperimentDto.class);
List<Long> expIds = experiments.stream().map(ExperimentDto::getId).toList();

// 2. 批量查询关联的Tag
List<TagDto> tags = dslContext.selectFrom(Tag)
    .where(Tag.EXPERIMENT_ID.in(expIds))
    .fetchInto(TagDto.class);
List<Long> tagIds = tags.stream().map(TagDto::getId).toList();

// 3. 批量查询关联的TagDescription
List<TagDescriptionDto> descriptions = dslContext.selectFrom(TagDescription)
    .where(TagDescription.TAG_ID.in(tagIds))
    .fetchInto(TagDescriptionDto.class);

// 4. 内存组装关联关系
Map<Long, List<TagDescriptionDto>> descMap = descriptions.stream().collect(Collectors.groupingBy(TagDescriptionDto::getTadId));
tags.forEach(tag -> tag.setTagDescription(descMap.getOrDefault(tag.getId(), Collections.emptyList())));
Map<Long, List<TagDto>> tagMap = tags.stream().collect(Collectors.groupingBy(TagDto::getExperimentId));
experiments.forEach(exp -> exp.setTags(tagMap.getOrDefault(exp.getId(), Collections.emptyList())));

这种方案SQL简单易维护，三次查询都是单表查询，性能也足够应对大部分场景。 #

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