Question 90659 in PostgreSQL Question posted in
The official documentation can be found here.

Select Query not working in NULL parameters JPA – Postgresql

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

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

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

Tags:

3

Answers


  1. 0 
    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
    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
  3. 0

    this is spring-data use @query and spel

    The latest Spring Data JPA M1 release of the Evans release train eases this pain by adding support

    or

    you can use EntityManager

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search