使用jOOQ嵌套查询获取父子记录时遭遇PSQLException语法错误

问题重现 #

使用jOOQ查询包含嵌套子记录的父记录时，触发PSQLException语法错误及org.jooq.exception.DataAccessException，相关代码与错误信息如下：

代码实现 #

record ChildRecord(Integer parentId) {}
record ParentRecord(Integer parentId, List<ChildRecords> childs) {};

List<ParentRecord> parents = dsl.select(ParentTable.ID,
                multiset(
                        select(ChildTable.ID)
                                .from(ChildTable)
                                .where(ParentTable.ID
                                        .eq(ChildTable.PARENT_ID)))

                .as("childs")
                .convertFrom(records -> records.map(Records.mapping(ChildRecord::new))))
        .from(ParentTable)
        .fetchInto(ParentRecord.class);
return parents;

错误堆栈 #

org.jooq.exception.DataAccessException: SQL [...]ERROR: syntax error at or near "select"
  Position: 56
    at org.jooq_3.18.0.DEFAULT.debug(Unknown Source) ~[na:na]
    ...（省略中间堆栈）
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "select"

使用技术栈：jOOQ 3.18.0、PostgreSQL 14.5、Spring Boot v2.7.8、Spring v5.3.25、Java 17.0.5

错误原因与修复方案 #

1. 泛型类型拼写错误 #

ParentRecord定义中使用了List<ChildRecords>，但实际定义的子记录类是ChildRecord，类型不匹配会导致jOOQ生成SQL时出现异常，需修正为List<ChildRecord>。

2. 子查询字段与子记录构造参数不匹配 #

ChildRecord的构造参数是Integer parentId，但子查询中选取的是ChildTable.ID，字段含义与类型不匹配。根据业务需求调整：

• 若ChildRecord需要存储子记录ID，修改构造参数为Integer childId
• 若需要存储父ID，将子查询字段改为ChildTable.PARENT_ID

3. Multiset子查询语法优化 #

jOOQ 3.18中multiset子查询的关联条件写法可调整，同时确保SQL方言正确配置为PostgreSQL 14，避免语法生成错误。

修复后的代码示例 #

// 修正泛型拼写，匹配子查询字段与构造参数
record ChildRecord(Integer childId) {}
record ParentRecord(Integer parentId, List<ChildRecord> childs) {}

List<ParentRecord> parents = dsl.select(
                ParentTable.ID,
                multiset(
                        select(ChildTable.ID)
                                .from(ChildTable)
                                .where(ChildTable.PARENT_ID.eq(ParentTable.ID))
                ).as("childs")
                 .convertFrom(records -> records.map(Records.mapping(ChildRecord::new)))
        )
        .from(ParentTable)
        .fetchInto(ParentRecord.class);
return parents;

替代方案：使用multisetAgg聚合 #

若业务场景适合聚合查询，可改用multisetAgg简化代码：

List<ParentRecord> parents = dsl.select(
                ParentTable.ID,
                multisetAgg(ChildTable.ID)
                        .as("childs")
                        .convertFrom(ids -> ids.map(ChildRecord::new))
        )
        .from(ParentTable)
        .leftJoin(ChildTable).on(ChildTable.PARENT_ID.eq(ParentTable.ID))
        .groupBy(ParentTable.ID)
        .fetchInto(ParentRecord.class);

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