Select Query not working in NULL parameters JPA-CodePudding

I am using spring-data-jpa here is my query


    @Query(value = "select ea.* from employee ea where ids in (?1) and (?2 is null or country=?2) ", nativeQuery = true)
    Page<Employee> findByds(List<UUID> ids,String country ,Pageable pageable );

i want to get list of employee by matching country only when parameter country is not null else i want to get all records in iDs

SQL Query is not working in case of null country parameter. i want my query to be like

When country is null select ea.* from employee ea where ids in (?1)
When country is not null select ea.* from employee ea where ids in (?1) and country =?2

CodePudding user response：

You can use Case when Condition 

select 
    case when 
        ea.country is null then 
            (select ea.* from employee ea where ids in (?1))
        when 
       ea.country = ?2 then 
            (select ea.* from employee ea where ids in (?1) and country =?2 )
      end
from employee ea

CodePudding user response：

Why you want use native query? This code works for me.

@Query("select p from Person p where p.id in (?1) and (?2 is null or p.country = ?2)")
List<Person> find(List<Long> ids, String country);