如何用jOOQ在MySQL中获取一对多嵌套数据并修复语法错误？

错误原因 #

你原代码的问题在于：

• MySQL不支持用array()直接包裹子查询生成数组，这是PostgreSQL等数据库的语法，MySQL没有对应的原生数组构造方式。
• 多余的row(TABLE_B.B_VALUE)会生成单行记录而非单个字段值，进一步导致SQL语法错误。

解决方案 #

针对MySQL，推荐用JSON聚合函数或jOOQ的类型安全聚合API来实现需求，以下是几种可行方案：

方案1：使用MySQL原生JSON_ARRAYAGG（最适配）

通过左关联+分组聚合生成JSON数组，再映射到POJO的List<String>：

dslContext.select(
    TABLE_A.USER_ID,
    // 用COALESCE处理无关联数据时的空值，转为空数组
    field("COALESCE(JSON_ARRAYAGG(TABLE_B.B_VALUE), JSON_ARRAY())", SQLDataType.JSON).`as`("listOfBValue"),
    field("COALESCE(JSON_ARRAYAGG(TABLE_C.C_VALUE), JSON_ARRAY())", SQLDataType.JSON).`as`("listOfCValue")
)
.from(TABLE_A)
.leftJoin(TABLE_B).on(TABLE_A.USER_ID.eq(TABLE_B.USER_ID))
.leftJoin(TABLE_C).on(TABLE_A.USER_ID.eq(TABLE_C.USER_ID))
.where(TABLE_A.USER_ID.eq(userId))
.groupBy(TABLE_A.USER_ID)
.fetchOneInto(APojo::class.java)

方案2：使用jOOQ类型安全的collect()聚合（jOOQ 3.15+）

如果使用较新版本的jOOQ，可以用collect()函数直接生成集合，无需手写SQL函数：

dslContext.select(
    TABLE_A.USER_ID,
    collect(TABLE_B.B_VALUE).`as`("listOfBValue"),
    collect(TABLE_C.C_VALUE).`as`("listOfCValue")
)
.from(TABLE_A)
.leftJoin(TABLE_B).on(TABLE_A.USER_ID.eq(TABLE_B.USER_ID))
.leftJoin(TABLE_C).on(TABLE_A.USER_ID.eq(TABLE_C.USER_ID))
.where(TABLE_A.USER_ID.eq(userId))
.groupBy(TABLE_A.USER_ID)
.fetchOneInto(APojo::class.java)

方案3：关联子查询+JSON_ARRAYAGG

如果不想用表关联，也可以在子查询中聚合生成数组：

dslContext.select(
    TABLE_A.USER_ID,
    field(
        select(jsonArrayAgg(TABLE_B.B_VALUE))
            .from(TABLE_B)
            .where(TABLE_B.USER_ID.eq(TABLE_A.USER_ID))
    ).`as`("listOfBValue"),
    field(
        select(jsonArrayAgg(TABLE_C.C_VALUE))
            .from(TABLE_C)
            .where(TABLE_C.USER_ID.eq(TABLE_A.USER_ID))
    ).`as`("listOfCValue")
)
.from(TABLE_A)
.where(TABLE_A.USER_ID.eq(userId))
.fetchOneInto(APojo::class.java)

映射说明 #

jOOQ默认会将MySQL的JSON数组自动映射到List<String>类型，无需额外配置。如果遇到映射问题，可以自定义Converter处理JSON数组与集合的转换。

内容的提问来源于stack exchange，提问作者MD. AL-HASAN MRIDHA