如何在Spring Data Relational中用SelectBuilder/Query构建CTE/WITH子句？

我希望使用Spring Data Relational项目中的SelectBuilder或Query构建器来编写CTE和/或WITH子句。我的查询示例如下：

WITH p_params
(
       SELECT :param1 AS param_1,
              :param2 AS param_2
       FROM   DUAL)
SELECT *
FROM   my_table mt
WHERE  mt.field1 = param_1;

请问是否可以在Spring Data Relational中实现CTE子句？

说明：虽然本示例可以改写查询以规避WITH子句，但这是填补SQL宏（动态替换WITH子句）与Java代码之间空白的必要前提。

可以实现，Spring Data Relational本身没有为CTE提供专门的构建器API，但有两种实用方案：

方案1：直接用Query接口拼接CTE片段 #

Spring Data Relational的Query类提供了with(String cte)方法，支持直接传入CTE的SQL片段，同时结合参数绑定保证安全性：

// 定义CTE部分的SQL
String cteSql = "p_params AS (SELECT :param1 AS param_1, :param2 AS param_2 FROM DUAL)";

// 构建查询并绑定参数
Query query = Query.query(Criteria.where("field1").is(SpelExpression.of("param_1")))
        .with(cteSql)
        .bind("param1", "实际参数1")
        .bind("param2", "实际参数2");

// 通过JdbcAggregateTemplate执行查询
List<MyTable> result = jdbcAggregateTemplate.query(query, MyTable.class);

这种方式灵活度高，适合需要动态生成CTE逻辑的场景，能直接对接SQL宏的需求。

方案2：自定义扩展SelectBuilder实现构建器风格 #

如果希望全程用构建器语法编写，可以封装一个CTE构建工具，将SelectBuilder生成的子查询转换为CTE片段：

// 自定义CTE构建工具类
public class CteBuilder {
    private final String cteName;
    private final SelectBuilder selectBuilder;

    private CteBuilder(String cteName, SelectBuilder selectBuilder) {
        this.cteName = cteName;
        this.selectBuilder = selectBuilder;
    }

    public static CteBuilder named(String name, SelectBuilder select) {
        return new CteBuilder(name, select);
    }

    public String toSql() {
        return String.format("%s AS (%s)", cteName, selectBuilder.build().getSql());
    }
}

// 使用示例
SelectBuilder paramSelect = Select.builder()
        .column(":param1 AS param_1")
        .column(":param2 AS param_2")
        .from("DUAL");

String cte = CteBuilder.named("p_params", paramSelect).toSql();

Query query = Query.query(Criteria.where("field1").is(SpelExpression.of("param_1")))
        .with(cte)
        .bind("param1", "实际参数1")
        .bind("param2", "实际参数2");

这种方式能保持代码的一致性和可读性，适合有统一SQL构建规范要求的场景。

注意：不同数据库的CTE语法细节略有差异（比如MySQL不需要DUAL表），实际使用时需根据数据库类型调整CTE片段。

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