CODEWITHSUNDEEP
javaspringhibernatejpanativequery
Pradeeshnarayan
Pradeeshnarayan

Reputation: 1235

Set an optional parameter in jpa query with "in" condition

I am using JPA to execute a native query as given below

@Query(value="SELECT pintable.pin from pintable s 
              where s.status=1 
              and (:pincodes is null or ifnull(s.pincode,'') in (:pincodes)) "
              , nativeQuery = true)

It is working fine for optional pincode (null) also working fine with a single pincode search. When I use multiple values for search, getting an error as given below.

Caused by: org.hibernate.exception.DataException: JDBC exception executing SQL [SELECT pintable.pin from pintable s where s.status=1 and ((?,?) is null or ifnull(s.pincode,'') in (?,?))    
Caused by: java.sql.SQLException: Operand should contain 1 column(s)

Please let me know how to handle with multiple value optional search.

Upvotes: 0

Views: 473

Answers (1)

thinhhja2001
thinhhja2001

Reputation: 11

Using COALESCE will solve the problem. The problem here is

:pincodes is null

Because in this case, SQL will see it as a Row Constructor instead of an Array.

To solve the problem, you should update your SQL script to

SELECT pintable.pin from pintable s 
          WHERE s.status=1 
          AND (COALESCE(:pincodes) IS null or ifnull(s.pincode,'') in (:pincodes))

Upvotes: 0

Related Questions