Make JPA Queries generic to support multiple optional fields.

Ever been in a situation when you have to modify a query and ignore any one of the parameters while fetching the result?

Lets take an example for an employee table :
A particular company has multiple offices and it identifies employees in a particular office by office_id and the floor on which the employee sits in that office by floor_id.

Scenario 1 : Get all the employees sitting in a particular office in a particular floor?

List<Employee> findByOfficeIdAndFloorId(Integer officeId, Integer floorId);

Scenario 2 : A use case comes where the result requested is to get the list of all the employees from a particular office and a floor. In case floorId is not provided then it should get the list of all the employees from that particular office. Which means if floorId is null then it should be ignored while fetching query result.

List<Employee> findByOfficeId(Integer officeId);

Doing this is also completely okay!!!

But is there any way we can make our query generic which would take floorId in consideration if present and ignore if not?

Yes it is similar to a null check in code, instead the check is made in the query making the code prettier and letting repository layer handling such logic.

@Query("SELECT * FROM Employee EMP WHERE EMP.officeId= :officeId AND (:floorId is null OR EMP.floorId = :floorId)")
List<Employee> findByOfficeIdAndFloorId(@Param("officeId") Integer officeId, @Param("floorId") Integer floorId);

This adds a check of floorId is null or not. If it is null then it is not considered in fetching the result and in case it is not null then the next condition in OR is calculated which is EMP.floorId = :floorId.


Software Engineer, IIITB