如何在Querydsl 5.0.0中为Oracle查询添加Parallel提示？

使用Oracle数据库与Querydsl 5.0.0版本，目标是生成带Parallel提示的Oracle SQL，目标SQL如下：

SELECT /*+ parallel(2)*/ *
FROM (
WITH temp AS (
SELECT name1, name2 FROM names
-- 此处添加客户端传来的动态筛选条件
)
SELECT name1 AS name FROM temp
UNION ALL (SELECT name2 AS name FROM temp)
);

示例筛选条件（如name1为John或Adam）：

[…]
WITH temp AS (
SELECT name1, name2
FROM names
WHERE name1 IN ('John', 'Adam'))
[…]

现有Java代码（this.getConditions(Request request)返回包含客户端筛选条件的BooleanBuilder）：

@Autowired
private JdbcTemplate jdbcTemplate;
private final QNames namesTable = QNames.names;
private final QNames withTable = new QNames("with");

public List<String> getNames(Request request) throws Exception {
    Connection conn = this.jdbcTemplate.getDataSource().getConnection();

    OracleQuery<Names> withQuery = new OracleQuery<Names>(conn)
        .select(Projections.constructor(
            Names.class,
            this.namesTable.name1.as(this.withTable.name1),
            this.namesTable.name2.as(this.withTable.name2))
        )
        .from(this.namesTable)
        .where(this.getConditions(request));

    OracleQuery<String> name1Query = new OracleQuery<String>(conn)
        .select(this.withTable.name1.as("name"))
        .from(this.withTable);

    OracleQuery<String> name2Query = new OracleQuery<String>(conn)
        .select(this.withTable.name2.as("name"))
        .from(this.withTable);

    OracleQuery<String> fullQuery = new OracleQuery<String>(conn)
        .with(this.withTable, withQuery);

    fullQuery.unionAll(name1Query, name2Query);

    return fullQuery.fetch();
}

尝试过以下方式添加Parallel提示，但会覆盖WITH子句：

OracleQuery<String> parallelFullQuery = new OracleQuery<String>(conn)
.select(fullQuery)
.addFlag(QueryFlag.Position.START, "select /*+ parallel(2)*/ * from (")
.addFlag(QueryFlag.Position.END, ")")
.from(fullQuery);

return parallelFullQuery.fetch();

求解决思路。

方法1：直接给最终联合查询添加查询提示 #

Querydsl的OracleQuery支持直接在查询起始位置添加提示，无需额外嵌套。直接对fullQuery操作即可：

fullQuery.addFlag(QueryFlag.Position.START, "/*+ parallel(2)*/");
return fullQuery.fetch();

生成的SQL会是：

/*+ parallel(2)*/
WITH temp AS (
SELECT name1, name2 FROM names WHERE ...
)
SELECT name1 AS name FROM temp
UNION ALL
SELECT name2 AS name FROM temp

Oracle会自动识别该提示并应用到整个查询的执行计划，效果与目标SQL的嵌套写法一致。

方法2：严格保留嵌套结构的写法 #

如果必须遵循目标SQL的SELECT /*+ ... */ * FROM (...)嵌套格式，可将原fullQuery转为子查询，在外层添加提示：

// 将原查询转为子查询
SubQueryExpression<String> subQuery = fullQuery.asSubQuery();

// 外层查询添加Parallel提示
OracleQuery<String> parallelQuery = new OracleQuery<>(conn)
    .select(Expressions.stringPath("name"))
    .from(subQuery)
    .addFlag(QueryFlag.Position.START, "SELECT /*+ parallel(2)*/ *");

return parallelQuery.fetch();

这种写法会生成你需要的嵌套SQL结构，同时保留WITH子句。

注意事项 #

• 确保Querydsl的Oracle方言配置正确，避免提示被错误解析
• Parallel提示的并行度（示例中为2）可根据实际需求调整，也支持动态传入参数

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