使用休眠中的条件 API 生成“where in”语句

Question

我制定了以下规范，表示根据学生的年龄和班级教师的姓名查询学生的谓词结构(一个学生可以有一个或多个教室(

public class StudentSpecification implements Specification<Student> {

  private final Integer age;
  public StudentSpecification(Integer age){
    this.age = age;
  }
  @Override
  public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
      List<Predicate> predicates = new ArrayList<>();
      predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.<Integert>get(age), Integer.valueOf(v)));
      SetJoin<Student, ClassRoom> classRooms = root.join(Student_.classRooms);
      predicates.add(criteriaBuilder.equal(classRooms.get(ClassRoom_.teacher), "Marta"));
      predicates.add(criteriaBuilder.equal(classRooms.get(ClassRoom_.teacher), "Fowler"));
      return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
  }
}

下面是一个数据示例：

Student
_____________________________________________
ID     CLASSROOM_ID     NAME         AGE
2      120              Pascal       22 
8      120              Bryan        21

ClassRoom
_____________________________________________
ID     CLASSROOM_ID     TEACHER
1      120              Marta
2      120              McAllister
2      120              Fowler

该规范不返回任何内容。

当我看到生成的语句时，我明白为什么它不起作用：

 where 
  classRooms.teacher=?
  and classRooms.teacher=? 

我期待类似的东西：

where  
  students0.classroom_id in (
    select classrooms0.classroom_id where
    classRooms.teacher=?
  )
  and students0.classroom_id in (
    select classrooms0.classroom_id where
    classRooms.teacher=?
  )

问题：如何在我的情况下使用条件 API 进行查询？

Answer 1

如果您需要坚持使用标准 API，则需要Subquery来实现您想要的目标。否则，与条件 API 的详细程度相比，为了提高可读性，HQL 可能是更好的选择。

这个想法是生成单个查询并通过谓词进行手动联接。所以不需要Join或SetJoin.

首先，请注意代码中存在一些错误。最明显的是您用于到达age字段的路径。应使用生成的元模型，而不是硬编码字符串。

predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get(Student_.age), age));

而不是：

predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.<Integert>get(age), Integer.valueOf(v)));

然后，这是完整的解决方案：

public static Specification<Student> withTeacherAndName(){
  return new Specification<Student>() {
    @Override
    public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> criteriaQuery,
        CriteriaBuilder criteriaBuilder) {
      List<Predicate> predicates = new ArrayList<>();
      predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get(Student_.age), 20));

      Subquery<String> sq1 = criteriaQuery.subquery(String.class);
      Root<Classroom> classroomRoot = sq1.from(Classroom.class);
      sq1.select(classroomRoot.get(Classroom_.classroomId));
      sq1.where(criteriaBuilder.equal(classroomRoot.get(Classroom_.teacher), "Marta"));
      Subquery<String> sq2 = criteriaQuery.subquery(String.class);
      Root<Classroom> classroomRoot2 = sq2.from(Classroom.class);
      sq2.select(classroomRoot2.get(Classroom_.classroomId));
      sq2.where(criteriaBuilder.equal(classroomRoot2.get(Classroom_.teacher), "Fowler"));
      criteriaQuery.where(criteriaBuilder.equal(root.get(Student_.classroomId), sq1));
      criteriaQuery.where(criteriaBuilder.equal(root.get(Student_.classroomId), sq2));
      return criteriaBuilder.and(predicates.toArray(new Predicate[]{}));
    }
  };
}

所以基本上你为每个条件创建一个子查询。代码需要重构(例如循环(。

Answer 2

如果你

想要一个in子句而不是一个等于子句，只需使用它：

predicates.add(criteriaBuilder.in(classRooms.get(ClassRoom_.teacher), "Marta"));
predicates.add(criteriaBuilder.in(classRooms.get(ClassRoom_.teacher), "Fowler"));

请参阅 https://docs.oracle.com/javaee/6/api/javax/persistence/criteria/CriteriaBuilder.html#in(javax.persistence.criteria.Expression(