如何在JOOQ的公共表表达式(CTE)中实现类型转换？

核心解决方案 #

JOOQ实现递归CTE时，无需在fields()参数中处理类型转换，直接在锚点查询和递归查询中构造带类型转换、别名的字段即可，CTE会自动识别字段元数据；若需更严谨的类型控制，也可手动指定CTE字段的名称与类型。

完整代码实现 #

以下是对应目标SQL的JOOQ代码（以Java为例）：

// 1. 定义锚点查询（递归起始节点）
Select<?> anchorQuery = select(
        CATEGORIES.ID,
        CATEGORIES.PARENT_ID,
        CATEGORIES.TITLE,
        inline(0).as("employee_level"), // 初始层级设为0
        CATEGORIES.ID.cast(String.class).as("emp_id") // ID转字符串并别名emp_id
    )
    .from(CATEGORIES)
    .where(CATEGORIES.ID.eq(2));

// 2. 定义递归查询（迭代下级节点）
Select<?> recursiveQuery = select(
        CATEGORIES.ID,
        CATEGORIES.PARENT_ID,
        CATEGORIES.TITLE,
        // 引用CTE中的employee_level并加1
        field(name("subordinates", "employee_level"), Integer.class).plus(1).as("employee_level"),
        // 拼接emp_id字符串
        concat(
            field(name("subordinates", "emp_id"), String.class),
            inline(" --> "),
            CATEGORIES.ID.cast(String.class)
        ).as("emp_id")
    )
    .from(CATEGORIES)
    // 关联CTE表，匹配父ID
    .join(table(name("subordinates")))
    .on(CATEGORIES.PARENT_ID.eq(field(name("subordinates", "id"), Integer.class)));

// 3. 组合递归CTE并执行查询
Result<?> result = ctx.withRecursive("subordinates")
    .as(anchorQuery.unionAll(recursiveQuery))
    .selectFrom("subordinates")
    .fetch();

关键细节说明 #

1. 锚点查询的字段构造：
   直接通过cast(String.class)完成ID到字符串的转换，并用as("emp_id")指定别名，JOOQ会自动将这些字段的类型、别名信息同步到CTE的定义中，无需手动在fields()里重复定义。

2. 递归查询的字段引用：
   引用CTE中的字段时，必须用field(name("CTE名称", "字段名"), 数据类型)明确指定类型，避免类型不匹配导致的错误（比如employee_level是整数类型，emp_id是字符串类型）。

3. 手动指定CTE字段（可选）：
   如果需要更严谨的类型控制，可手动指定CTE的所有字段，确保锚点与递归部分的字段类型完全一致：

   ctx.withRecursive("subordinates",
           field(name("id"), Integer.class),
           field(name("parent_id"), Integer.class),
           field(name("title"), String.class),
           field(name("employee_level"), Integer.class),
           field(name("emp_id"), String.class)
       )
       .as(anchorQuery.unionAll(recursiveQuery))
       .selectFrom("subordinates")
       .fetch();
   

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