如何将含左连接、子查询、GROUP BY的SQL转换为JPA CriteriaBuilder查询

前提说明 #

• 先完成实体类映射：Candidate实体对应candy.candidate表，JCandidateJobProfile实体对应candy.j_candidate_job_profile表，所有字段映射规则与数据库一致
• 以下实现基于JPA 2.1及以上版本，核心逻辑完全对齐提供的原生SQL

完整实现代码 #

import javax.persistence.criteria.*;
import javax.persistence.Tuple;
import java.util.List;

// 此处entityManager为Spring注入/自行初始化的JPA EntityManager实例
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
// 定义返回结果为Object[]，下标0为Candidate对象，下标1为最大得分
CriteriaQuery<Object[]> mainQuery = cb.createQuery(Object[].class);
Root<Candidate> candiRoot = mainQuery.from(Candidate.class);

// 1. 构造子查询，对应原生SQL中的ponderation临时表
Subquery<Tuple> ponderationSub = mainQuery.subquery(Tuple.class);
Root<JCandidateJobProfile> jobProfileRoot = ponderationSub.from(JCandidateJobProfile.class);

// 实现CASE WHEN得分计算逻辑
Expression<Integer> scoreCalculation = cb.selectCase()
        .when(
            cb.and(
                cb.equal(jobProfileRoot.get("profileId"), 941),
                cb.equal(jobProfileRoot.get("skillTypeId"), 970)
            ), 10
        )
        .when(
            cb.and(
                cb.equal(jobProfileRoot.get("profileId"), 941),
                cb.equal(jobProfileRoot.get("skillTypeId"), 972)
            ), 3
        )
        .otherwise(cb.nullLiteral(Integer.class));

// 子查询返回字段：候选人ID、计算后的得分
ponderationSub.multiselect(
        jobProfileRoot.get("candidateId").alias("candidate_id"),
        scoreCalculation.alias("score")
);

// 2. 实现LEFT JOIN子查询，关联条件为候选人ID相等
Join<Candidate, Tuple> ponderationJoin = candiRoot.join(
        ponderationSub,
        JoinType.LEFT,
        cb.equal(candiRoot.get("id"), ponderationSub.get("candidate_id"))
);

// 3. 设置WHERE条件：过滤得分不为空的记录
mainQuery.where(cb.isNotNull(ponderationJoin.get("score")));

// 4. 实现GROUP BY与MAX聚合
Expression<Integer> maxScoreExp = cb.max(ponderationJoin.get("score").as(Integer.class));
mainQuery.multiselect(candiRoot, maxScoreExp.alias("score"));
mainQuery.groupBy(candiRoot.get("id"));

// 5. 按候选人ID升序排序
mainQuery.orderBy(cb.asc(candiRoot.get("id")));

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

低版本JPA兼容说明 #

如果使用的JPA版本低于2.1，不支持直接JOIN子查询，可以调整逻辑为：先查询所有符合得分规则的JCandidateJobProfile记录的候选人ID和最高得分，再关联Candidate表查询匹配的候选人信息即可。

内容的提问来源于stack exchange，提问作者Mohamed El-Harrougui