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

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

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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related