CODEWITHSUNDEEP
javaspringjpaspring-data-jpahql
yongguangl
yongguangl

Reputation: 87

JPA - query parameter is null

@Query("select users from User users  " +
        "where users.id like concat('%',?1,'%') and " +
        "users.name like concat('%',?2,'%') and " +
        "users.telNo like concat('%',?3,'%') ")
List<User> fuzzyQueryUser(Integer id, String name, String telNo);

When the first parameter id is null, the fuzzy query has no results. I want to have results when the parameter is null if there are entries like that in the database. What should I do? Thank you. 

@Query("select users from User users  " +
    "where (users.id like concat('%',?1,'%') or ?1 is null or ?1 ='')and " +
    "(users.name like concat('%',?2,'%') or ?2 is null or ?2 ='') and " +
    "(users.telNo like concat('%',?3,'%') or ?3 is null or ?3 ='')")
List<User> fuzzyQueryUser(String id, String name, String telNo);

like this

@Query("select users from User users  " +
        "where (users.id like concat('%',?1,'%') or ?1 is null  )and " +
        "(users.name like concat('%',?2,'%') or ?2 is null or ?2 ='') and " +
        "(users.telNo like concat('%',?3,'%') or ?3 is null or ?3 ='')")
List<User> fuzzyQueryUser(Integer id, String name, String telNo);

Upvotes: 1

Views: 1722

Answers (1)

Maciej Kowalski
Maciej Kowalski

Reputation: 26492

Simply add another condition to ignore null and optionally empty strings:

@Query("select users from User users  " +
        "where (users.id like concat('%',?1,'%') or ?1 is null) and " 
       +"users.name like concat('%',?2,'%') and " +
        "users.telNo like concat('%',?3,'%') ")

Upvotes: 4

Related Questions