使用jOOQ的jsonArrayAgg查询MariaDB/MySQL时触发SQL语法错误异常

当SQL语句包含jsonArrayAgg命令时，jOOQ总是抛出异常：“You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near”。但开启MariaDB的general_log后，将jOOQ生成的SQL语句复制到数据库执行却一切正常，结果符合预期。

代码示例 #

list = ctx.select(
            MAIN.KEYID,
            field(
            select(jsonArrayAgg(
                jsonObject(
                    key("id").value(CONNECTOR.ID),
                    key("standard").value(CONNECTOR.STANDARD)
            ))).from(CONNECTOR)
            .where(CONNECTOR.MAINKEYID.eq(MAIN.KEYID))).as("connectors")                                
        ).from(MAIN)           
        .fetchInto(JSON_POJO.class);

jOOQ报错日志 #

at org.jooq_3.15.12.MARIADB.debug(Unknown Source) ~[?:?]
    at org.jooq.impl.Tools.translate(Tools.java:2997) ~[jooq-3.15.12.jar:?]
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:639) ~[jooq-3.15.12.jar:?]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:354) ~[jooq-3.15.12.jar:?]
    at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:295) ~[jooq-3.15.12.jar:?]
    at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:316) ~[jooq-3.15.12.jar:?]
    at org.jooq.impl.SelectImpl.fetchLazyNonAutoClosing(SelectImpl.java:2866) ~[jooq-3.15.12.jar:?]
    at org.jooq.impl.ResultQueryTrait.collect(ResultQueryTrait.java:357) ~[jooq-3.15.12.jar:?]
    at org.jooq.impl.ResultQueryTrait.fetchInto(ResultQueryTrait.java:1423) ~[jooq-3.15.12.jar:?]

jOOQ生成的SQL代码 #

set @t = @@group_concat_max_len; set @@group_concat_max_len = 4294967295; 
select `mydb`.`main`.`keyid`, 
(select json_merge_preserve('[]', concat('[', group_concat(json_merge_preserve('{}', json_object('id', `mydb`.`Connector`.`id`), json_object('standard', `mydb`.`Connector`.`standard`)) separator ','), ']')) 
from `mydb`.`Connector` where `mydb`.`Connector`.`mainKeyid` = `mydb`.`main`.`keyid`) as `connectors` from `mydb`.`main`; 
set @@group_concat_max_len = @t

已尝试的无效方案 #

• jOOQ版本从3.12升级到3.16
• MariaDB从10.3升级到10.11
• 使用Java 11.0.18版本
• 配置连接参数allowMultiQueries = true

解决思路与替代方案 #

1. 强制使用MariaDB原生JSON聚合函数 #

MariaDB 10.5+原生支持json_arrayagg，可以绕过jOOQ的模拟实现，直接调用原生函数：

// 在子查询中直接使用原生SQL函数
field("json_arrayagg(json_object('id', {0}, 'standard', {1}))", 
     JSON.class, CONNECTOR.ID, CONNECTOR.STANDARD)

也可以通过jOOQ配置强制启用原生JSON函数支持：

Settings settings = new Settings()
    .withDialect(SQLDialect.MARIADB)
    .withRenderNameStyle(RenderNameStyle.AS_IS);
DSLContext ctx = DSL.using(connection, SQLDialect.MARIADB, settings);

2. 改用Java端聚合 #

放弃SQL端聚合，通过JOIN查询所有数据后，在Java代码中手动分组聚合：

// 查询关联数据
List<Record> records = ctx.select(MAIN.KEYID, CONNECTOR.ID, CONNECTOR.STANDARD)
    .from(MAIN)
    .leftJoin(CONNECTOR).on(CONNECTOR.MAINKEYID.eq(MAIN.KEYID))
    .fetch();

// Java端分组转换
Map<Long, List<ConnectorDTO>> connectorMap = records.stream()
    .collect(Collectors.groupingBy(
        r -> r.get(MAIN.KEYID),
        Collectors.mapping(
            r -> new ConnectorDTO(r.get(CONNECTOR.ID), r.get(CONNECTOR.STANDARD)),
            Collectors.toList()
        )
    ));

List<JSON_POJO> list = connectorMap.entrySet().stream()
    .map(entry -> new JSON_POJO(entry.getKey(), entry.getValue()))
    .collect(Collectors.toList());

3. 匹配驱动与组件版本 #

确保MariaDB JDBC驱动版本与jOOQ、MariaDB服务器版本完全兼容，例如使用MariaDB Connector/J 3.0+配合jOOQ 3.16+，可能解决多语句执行的兼容性问题。

内容的提问来源于stack exchange，提问作者deniro.wang