如何将含子查询的SQL Case语句转换为CaseBuilder实现？

我有个demo表，现在用这条SQL查询符合条件的id：

select t1.id
  from demo t1
 where substring(t1.id, -3) = case 
                                when (select count(*)
                                        from demo t2
                                       where t2.id like concat(substring(t1.id, 1, length(t1.id) - 3), '%')) = 2 then 'ENG'
                                else 'KOR'
                              end;

想把它改成JPA Criteria里的CaseBuilder实现，但卡壳了——不知道怎么在when语句里加子查询，求帮忙。

实现代码 #

直接用JPA的Subquery来构建统计逻辑，就能在CaseBuilder的when条件里用子查询结果了，完整代码如下：

// 获取CriteriaBuilder和创建查询
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<String> cq = cb.createQuery(String.class);
Root<Demo> t1 = cq.from(Demo.class);

// 构建子查询：统计同前缀的记录数
Subquery<Long> countSubquery = cq.subquery(Long.class);
Root<Demo> t2 = countSubquery.from(Demo.class);

// 生成t1的前缀（去掉id最后3位）
Expression<String> t1IdPrefix = cb.substring(
    t1.get("id"), 
    1, 
    cb.length(t1.get("id")).as(Integer.class) - 3
);
// 子查询的匹配条件：t2.id以t1的前缀开头
Predicate subQueryCondition = cb.like(t2.get("id"), cb.concat(t1IdPrefix, "%"));
countSubquery.select(cb.count(t2.get("id"))).where(subQueryCondition);

// 用CaseBuilder实现原SQL的case逻辑
Expression<String> caseExpression = cb.selectCase()
    .when(cb.equal(countSubquery, 2L), "ENG")
    .otherwise("KOR");

// 主查询的where条件：id最后3位等于case的结果
Predicate mainCondition = cb.equal(
    cb.substring(t1.get("id"), cb.length(t1.get("id")).as(Integer.class) - 2, 3),
    caseExpression
);

// 组装并执行查询
cq.select(t1.get("id")).where(mainCondition);
List<String> result = entityManager.createQuery(cq).getResultList();

要点说明 #

• 子查询得单独用cq.subquery()创建，把统计逻辑封装进去
• CaseBuilder的when方法能接收Predicate参数，用cb.equal(subquery, 2L)就能把子查询结果和2做比较，作为判断条件
• 原SQL里的substring(t1.id, -3)对应JPA里的cb.substring(..., length-2, 3)，因为JPA的substring起始位置从1开始算，取最后3位就是从长度-2的位置开始取3个字符

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