对于给定的查询参数，具有空值和非空值的JPA查询

Question

我在JPA repository类中编写了一个查询，如下所示：

@Query("select student from Student " +
      "student " +
      " where student.class.id = :classId " +
      " and student.course.id = :courseId " +
      " and student.isDeleted = false")
  List<Student> findDetails(
      @Param("classId") String classId,
      @Param("courseId") String courseId);

上述查询的问题是，如果参数：classId和courseId都为空，则需要获取所有记录，而这不会发生。到目前为止，没有获取任何记录。

基本上，我希望编写的查询能够正确地适用于所有情况，即参数classId和courseId都采用空值和非空值。

在这方面，我已经了解了CriteriaBuilder的概念，但无法正确掌握该概念以实现我的案例。

有人能帮忙吗？谢谢

编辑

我已经在控制台中记录了查询，如下所示：

select tcstudent_.id as id1_42_, tcstudent_.created_by as created_2_42_, tcstudent_.created_date as created_3_42_, tcstudent_.last_modified_by as last_mod4_42_, tcstudent_.last_modified_date as last_mod5_42_, tcstudent_.course_id as course_id7_42_, tcstudent_.class_id as class_id8_42_ from Student tcstudent_ where tcstudent_.class_id=? and tcstudent_.course_id=? and tcstudent_.is_deleted=0

Answer 1

以下条件查询可用于条件筛选

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Student> criteriaQuery = criteriaBuilder.createQuery(Student.class);
Root<Student> itemRoot = criteriaQuery.from(Student.class);

ParameterExpression<Boolean> param = criteriaBuilder.parameter(Boolean.class, "isDeleted");
Predicate paramFalse = criteriaBuilder.isFalse(param);

//Constructing list of parameters
List<Predicate> predicates = new ArrayList<Predicate>();

//Adding predicates in case of parameter not being null
if (classId != null) {
    predicates.add(
            criteriaBuilder.equal(itemRoot.get("classId"), classId));
}
if (courseId != null) {
    predicates.add(
            criteriaBuilder.equal(itemRoot.get("courseId"), courseId));
}

predicates.add(paramFalse)

//query itself
criteriaQuery.select(itemRoot)
        .where(predicates.toArray(new Predicate[]{}));

entityManager.createQuery(criteriaQuery).getResultList();

参考：https://www.baeldung.com/hibernate-criteria-queries

Answer 2

您可以通过添加为空或条件来执行以下操作：

@Query("select student from Student " +
      "student " +
      " where (:classId is null or student.class.id = :classId )" +
      " and (:courseId is null or student.course.id = :courseId )" +
      " and student.isDeleted = false")
  List<Student> findDetails(
      @Param("classId") String classId,
      @Param("courseId") String courseId);