能否用JPA Specifications编写WITH查询？现有原生查询如何转换？

原SQL的核心逻辑是：筛选出满足以下任一条件的table1记录：

• 关联的table2中存在administrator_id为11的条目
• 关联的table3中存在administrator_id为11的条目

由于JPA Specifications本身不直接支持CTE（WITH子句），我们可以将CTE转换为子查询的形式，通过Specification构建对应的查询条件。

前提假设 #

假设你已经定义了对应的JPA实体类：

• Table1：对应数据库表table1，主键为id，包含createDate字段
• Table2：对应数据库表table2，包含contractId（关联Table1的id）、administratorId字段
• Table3：对应数据库表table3，包含id（关联Table1的id）、administratorId字段

具体实现代码 #

import jakarta.persistence.criteria.*;
import org.springframework.data.jpa.domain.Specification;

public class Table1Specifications {

    public static Specification<Table1> filterByAdministratorId(Long adminId) {
        return (root, query, criteriaBuilder) -> {
            // 构建第一个子查询：table1关联table2且table2.administrator_id=adminId
            Subquery<Long> subQuery1 = query.subquery(Long.class);
            Root<Table1> subRoot1 = subQuery1.from(Table1.class);
            Join<Table1, Table2> joinTable2 = subRoot1.join("table2List", JoinType.INNER); // 假设Table1有table2List属性关联Table2
            subQuery1.select(subRoot1.get("id"))
                    .where(criteriaBuilder.equal(joinTable2.get("administratorId"), adminId));

            // 构建第二个子查询：table1关联table3且table3.administrator_id=adminId
            Subquery<Long> subQuery2 = query.subquery(Long.class);
            Root<Table1> subRoot2 = subQuery2.from(Table1.class);
            Join<Table1, Table3> joinTable3 = subRoot2.join("table3", JoinType.INNER); // 假设Table1有table3属性关联Table3（一对一/多对一）
            subQuery2.select(subRoot2.get("id"))
                    .where(criteriaBuilder.equal(joinTable3.get("administratorId"), adminId));

            // 组合两个子查询的条件：id在subQuery1结果 或者 id在subQuery2结果
            return criteriaBuilder.or(
                    criteriaBuilder.in(root.get("id")).value(subQuery1),
                    criteriaBuilder.in(root.get("id")).value(subQuery2)
            );
        };
    }
}

关键说明 #

• 子查询替代CTE：原SQL的两个WITH子查询被转换为JPA的Subquery对象，直接在Specification中构建，效果和原CTE一致。
• 关联关系处理：代码中假设了Table1与Table2、Table3的关联属性（比如table2List、table3），你需要根据实际实体类的关联注解（@OneToMany、@ManyToOne等）调整对应的join名称。
• 排序的处理：原SQL子查询中的order by table1.create_date desc在IN子查询中不影响最终结果（因为IN只关心存在性，不关心顺序），如果不需要排序可以直接忽略；如果需要对最终查询结果排序，可以在调用Specification时通过Sort参数实现：

  // 调用示例
  table1Repository.findAll(Table1Specifications.filterByAdministratorId(11L), Sort.by(Sort.Direction.DESC, "createDate"));
  

• 性能优化：如果数据量较大，建议确保table2.contract_id、table3.id、table2.administrator_id、table3.administrator_id这些字段有合适的索引，避免全表扫描。

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