使用jOOQ与SQLite实现WITH RECURSIVE查询时触发DataAccessException

问题根因 #

你遇到的报错是SQLite本身对递归CTE的语法限制，和jOOQ的实现逻辑叠加触发的问题：

• SQLite明确要求：递归CTE的递归引用（也就是你代码里的tree表）不能出现在子查询内部，必须直接放在UNION后第二个分支的顶层FROM子句中
• jOOQ 3.13版本中，你将SELECT语句作为table()方法的参数封装为派生表时，会自动给查询包一层SELECT * FROM (...) x的嵌套结构，你的SQL生成后递归引用的tree刚好被包裹在这层子查询里，触发了SQLite的校验报错。

解决方法 #

调整代码结构，避免jOOQ生成不必要的嵌套子查询即可：

// 第一步：先定义非递归的基础关联CTE，把group和map的关联逻辑抽离，避免重复生成嵌套子查询
CommonTableExpression<?> baseCte = name("base_group_map").fields("id", "parent_id", "path")
    .as(select(
            GROUP.ID.as("id"),
            GROUP.PARENTID.as("parent_id"),
            MAP.PATH.as("path")
        )
        .from(GROUP)
        .join(MAP).on(MAP.GROUPID.eq(GROUP.ID))
    );

// 第二步：定义递归CTE，直接引用上面的baseCte，不会触发额外嵌套
CommonTableExpression<?> recursiveCte = name("tree").fields("id", "path")
    .as(
        // 递归锚点：查询你指定的初始节点
        select(field(name("base_group_map", "id"), UUID.class), field(name("base_group_map", "path"), String.class))
            .from(baseCte)
            .where(field(name("base_group_map", "id"), UUID.class).eq({some_value}))
        .union(
            // 递归分支：直接关联递归表tree，没有多余嵌套，符合SQLite语法要求
            select(field(name("base_group_map", "id"), UUID.class), field(name("base_group_map", "path"), String.class))
                .from(baseCte)
                .join(name("tree"))
                .on(field(name("base_group_map", "parent_id"), UUID.class).eq(field(name("tree", "id"), UUID.class)))
        )
    );

// 执行查询获取path列表
return ctx.with(baseCte)
    .withRecursive(recursiveCte)
    .select(field(name("tree", "path"), String.class))
    .from(recursiveCte)
    .fetch(String.class);

jOOQ + SQLite 使用递归CTE的注意事项 #

• 不要将递归CTE的引用放在派生表、子查询中，必须直接放在FROM子句的顶层
• 尽量将通用的关联逻辑提前抽为非递归CTE，避免jOOQ自动生成嵌套子查询触发SQLite限制
• 你当前的表名group是SQL保留字，需要确保jOOQ配置了正确的标识符转义规则，避免后续出现表名识别错误

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