Spring Data JPA: How to use optional filter parameter?-CodePudding

I have the following service method that calls my repository:

List<EmployeeDTO> employees = employeeRepository.findByDepartmentUuid(depUuid);

In the repository, I can fill the DTO without any problem if depUuid parameter is not null.

@Query(value = "SELECT e.uuid as empUuid, e.name as empName FROM Employee e "  
        "WHERE e.depUuid = :depUuid")
List<EmployeeDTO> findByDepartmentUuid(@Param(value = "depUuid") UUID depUuid);

However, I want to retrieve all the records if the depUuid parameter is null. So, how can I manage this?

Note: I am using JPQL and need a solution with that, not with native query.

CodePudding user response：

The following query will do the job.

@Query(value = "SELECT e.uuid as empUuid, e.name as empName FROM Employee e "  
        "WHERE (?1 is NOT NULL AND e.depUuid = ?1 ) OR ?1 is NULL )"
List<EmployeeDTO> findByDepartmentUuid(@Param(value = "depUuid") UUID depUuid);

CodePudding user response：

You can change your query to not use a relational selection clause and so you just select from the entire table. Your Java method will have no params and you can call it findAllEmployees().

If you really want to use the same method that you have already defined, then test for null input and delegate to your new findAllEmployees() method.