CODEWITHSUNDEEP
javaspringspring-bootjpa
Joy
Joy

Reputation: 4511

JPA query with both null and non-null value for a given query param

I have written one query in JPA repository class as follows:

@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);

The problem with the above query is, in case both the params: classId and courseId are null, all the records need to be fetched, which is not happening. As of now, no record is getting fetched.

Basically, I would like to write the query such that, it works for all the cases correctly i.e. both the params classId and courseId take null and non-null values.

In this regard, I have gone through the CriteriaBuilder concept, but could not grasp the concept properly in order to implement my case.

Could anyone please help here? Thanks.

EDIT

I have logged the query in console, it's coming as follows:

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

Upvotes: 1

Views: 2441

Answers (2)

Gaurav Jeswani
Gaurav Jeswani

Reputation: 4592

You can do it as below by adding an is null or condition:

@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);

Upvotes: 3

dassum
dassum

Reputation: 5112

The below Criteria Query can be used for conditional filter

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();

Reference: https://www.baeldung.com/hibernate-criteria-queries

Upvotes: 1

Related Questions