将SQL 'IN'子查询转换为JPA Criteria Query的问题

你当前的代码核心问题在于，当子查询可能返回多条结果时，你用了等值判断（=）而不是IN条件，这就是为什么生成的SQL里是teacher0_.school_id = (select ...)而不是IN。下面是修正后的代码，重点修改子查询中的条件部分：

修正后的完整代码 #

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<String> searchQuery = criteriaBuilder.createQuery(String.class);
Root<Student> root = searchQuery.from(Student.class);
List<Predicate> restrictions = new ArrayList<>();

// 外层子查询：查询符合条件的Teacher
Subquery<Teacher> teacherSubQuery = searchQuery.subquery(Teacher.class);
Root<Teacher> teacherRoot = teacherSubQuery.from(Teacher.class);
List<Predicate> teacherPredicates = new ArrayList<>();

// 内层子查询：查询当前Student关联的所有School ID
Subquery<Long> schoolSubQuery = searchQuery.subquery(Long.class); // 假设School ID是Long类型，可根据实际类型调整
Root<School> schoolRoot = schoolSubQuery.from(School.class);
schoolSubQuery.select(schoolRoot.get("id")) // 对应原SQL的school0_.id
              .where(criteriaBuilder.equal(schoolRoot.get("studentId"), root.get("id"))); // 对应school0_.student_id = student0_.id

// 关键修改：用IN替代EQUAL条件
teacherPredicates.add(criteriaBuilder.equal(teacherRoot.get("socialNumber"), userInput)); // 对应teacher0.social_number = ?
teacherPredicates.add(criteriaBuilder.in(teacherRoot.get("schoolId")).value(schoolSubQuery)); // 对应teacher0_.school_id IN (select ...)

// 构建EXISTS条件
restrictions.add(criteriaBuilder.exists(
    teacherSubQuery.select(teacherRoot.get("socialNumber"))
                   .where(teacherPredicates.toArray(new Predicate[0]))
));

// 最终查询构建
searchQuery.distinct(true)
           .select(root.get("name"))
           .where(restrictions.toArray(new Predicate[0]));

TypedQuery<String> query = em.createQuery(searchQuery);
List<String> nameList = query.getResultList();

关键修改点解释 #

1. 明确子查询返回类型：把内层school子查询的返回类型指定为School ID的实际类型（这里用Long示例），让代码逻辑更清晰，避免隐式类型转换问题。
2. 替换EQUAL为IN条件：将原来的等值判断criteriaBuilder.equal(...)替换为criteriaBuilder.in(teacherRoot.get("schoolId")).value(schoolSubQuery)。这个写法会告诉JPA生成IN关键字，适配子查询可能返回多条School ID的场景，完全匹配你期望的原生SQL逻辑。

修改后生成的SQL会和你需求的一致：

select distinct student0_.name from vnic03.student student0_ where (exists(select teacher0_.social_number from vnic03.teacher teacher0_ where teacher0.social_number = ? and teacher0_.school_id in (select school0_.id from vnic03.school school0_ where school0_.student_id = student0_.id)))

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