如何使用Spring JPA Specifications实现带多层子查询的复杂动态JPQL查询

完整实现代码 #

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

public class JobSpecifications {

    // 入参分别对应原JPQL中的?5（organizationId）、?6（待匹配的gradeId）
    public static Specification<Job> complexGradeFilter(Long organizationId, Long inputGradeId) {
        return (Root<Job> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
            Predicate[] orConditions = new Predicate[3];

            // 对应原JPQL第一个条件：?6 = 0L
            orConditions[0] = cb.equal(cb.literal(inputGradeId), 0L);

            // 对应原JPQL第二个条件：0L IN (SELECT i12.filterFieldId FROM IjpPublishFilterFields i12 WHERE ...)
            Subquery<Long> sub12 = query.subquery(Long.class);
            Root<IjpPublishFilterFields> i12 = sub12.from(IjpPublishFilterFields.class);
            // 直接指定返回单个字段，不需要返回完整实体
            sub12.select(i12.get("filterFieldId"))
                    .where(
                            cb.equal(i12.get("jobId"), root.get("id")),
                            cb.like(i12.get("fieldType"), "GRADE")
                    );
            orConditions[1] = cb.in(0L).value(sub12);

            // 对应原JPQL第三个条件：?6 IN (SELECT node.id FROM Grade node ...)
            Subquery<Long> nodeSub = query.subquery(Long.class);
            Root<Grade> node = nodeSub.from(Grade.class);
            Join<Grade, Grade> parent = node.join("parent", JoinType.INNER);

            // 嵌套的parent.id IN 子查询
            Subquery<Long> i1Sub = query.subquery(Long.class);
            Root<IjpPublishFilterFields> i1 = i1Sub.from(IjpPublishFilterFields.class);
            i1Sub.select(i1.get("filterFieldId"))
                    .where(
                            cb.equal(i1.get("jobId"), root.get("id")),
                            cb.like(i1.get("fieldType"), "GRADE")
                    );

            // 组装node子查询条件
            nodeSub.select(node.get("id"))
                    .where(
                            cb.between(node.get("lft"), parent.get("lft"), parent.get("rgt")),
                            parent.get("id").in(i1Sub),
                            cb.equal(node.get("organizationId"), organizationId),
                            cb.isTrue(node.get("isActive")),
                            cb.isTrue(parent.get("isActive"))
                    );
            orConditions[2] = cb.in(cb.literal(inputGradeId)).value(nodeSub);

            return cb.or(orConditions);
        };
    }
}

核心要点说明 #

• 你之前无法返回单个字段的核心原因是未指定子查询的返回类型，将子查询泛型设置为字段对应类型，调用select(属性路径)即可直接返回单字段结果，不需要查询完整实体
• 如果Grade实体没有配置parent自关联映射，可以通过parent.on(cb.xxx(...))手动指定关联条件，不依赖实体注解配置
• 如果fieldType是精确匹配GRADE，可以把cb.like替换为cb.equal，查询性能更好

使用示例 #

可以和其他动态Specification任意拼接，满足多条件动态查询需求：

Specification<Job> finalSpec = JobSpecifications.complexGradeFilter(yourOrgId, yourInputGradeId)
        .and(其他动态查询Specification)
        .and(更多自定义条件Specification);
List<Job> result = jobRepository.findAll(finalSpec);

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