Hibernate 6.0.2中使用Criteria API实现CTE的方法及示例问询

Hibernate 6.0+的Criteria API支持通过CommonTableExpression定义CTE，以下是对应你给出SQL的实现示例：

假设你有两个实体类：Tbl1对应数据库表tbl1，Tbl2对应tbl2，字段col1已正确映射到实体属性。

基础CTE查询示例 #

// 获取CriteriaBuilder和主查询对象
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<String> mainQuery = cb.createQuery(String.class);

// 定义CTE的子查询：查询tbl2的col1
Subquery<String> cteSubQuery = mainQuery.subquery(String.class);
Root<Tbl2> cteRoot = cteSubQuery.from(Tbl2.class);
cteSubQuery.select(cteRoot.get("col1"));

// 创建CTE，指定别名"tbl"
CommonTableExpression<String> cte = cb.with("tbl").as(cteSubQuery);

// 主查询从CTE中获取数据
Root<String> mainRoot = mainQuery.from(cte);
mainQuery.select(mainRoot.get("col1"));

// 执行查询并获取结果
List<String> result = entityManager.createQuery(mainQuery).getResultList();

带条件的CTE示例 #

如果需要给CTE添加过滤条件，直接在子查询中追加where即可：

Subquery<String> cteSubQuery = mainQuery.subquery(String.class);
Root<Tbl2> cteRoot = cteSubQuery.from(Tbl2.class);
// 添加过滤条件
cteSubQuery.select(cteRoot.get("col1"))
           .where(cb.like(cteRoot.get("col1"), "%test%"));

关联实体的CTE示例 #

如果需要在主查询中关联CTE和其他实体（比如关联tbl1和CTE），可以这样实现：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tbl1> mainQuery = cb.createQuery(Tbl1.class);

// 定义返回实体类型的CTE
Subquery<Tbl2> cteSubQuery = mainQuery.subquery(Tbl2.class);
Root<Tbl2> cteRoot = cteSubQuery.from(Tbl2.class);
cteSubQuery.select(cteRoot);

CommonTableExpression<Tbl2> cte = cb.with("tbl").as(cteSubQuery);

// 主查询关联tbl1和CTE
Root<Tbl1> mainRoot = mainQuery.from(Tbl1.class);
// 关联CTE，这里假设tbl1和tbl2通过col1关联
mainQuery.select(mainRoot)
         .where(cb.exists(
             cb.createQuery()
               .select(cb.literal(1))
               .from(cte)
               .where(cb.equal(cte.get("col1"), mainRoot.get("col1")))
         ));

List<Tbl1> result = entityManager.createQuery(mainQuery).getResultList();

需要注意的是，实体类的属性与数据库字段的映射必须正确，CTE的别名要和后续查询中引用的名称保持一致。

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