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

Question

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

@lucumt I coincide with your query. Any way logs SQL query will print. take the SQL and run it in the DB. Joy Please share code snips for a better understanding. — Lova Chittumuri
– Lova Chittumuri, Commented Jul 21, 2021 at 4:37

Gaurav Jeswani · Accepted Answer · 2021-07-22 06:26:38Z

3

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

edited Jul 22, 2021 at 6:26

answered Jul 21, 2021 at 5:14

Gaurav Jeswani

4,6186 gold badges30 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dassum · Accepted Answer · 2021-07-21 05:01:27Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related