4

I'm using spring's NamedParameterJdbcTemplate because I have a SELECT ... IN () in my SQL query, as explained here.

In our specific case, the business logic should be: - If the list of id's to check is null or empty, omit the entire IN condition - If the list contains id's, use the IN condition like normal

So we programmed it like this:

SELECT * FROM table WHERE (:ids IS NULL or table.column IN (:ids))

This query works if the :ids is indeed a NULL or empty list, but it fails if it is not because the way spring fills in the parameters for a list of 3 values is like this:

SELECT * FROM table WHERE ((?,?,?) IS NULL or table.column IN (?,?,?))

and you cannot do "IS NULL" on the triple question mark statement. Is there any easy way to do solve this directly in the SQL query, thus not using Java code (we don't want to do string maniuptlation in the sql query in Java)?

user1884155
  • 3,385
  • 3
  • 48
  • 104

1 Answers1

0

You could try reversing the order like this:

SELECT * FROM table WHERE (table.column IN (:ids) or :ids IS NULL)

Since your 3 id case will satisfy the first condition, the 'or' may not be evaluated. This might depend on your DB though. This works with Hibernate + Oracle, but I don't see it working with Sybase IQ + NamedParameterJdbcTemplate so your mileage may vary.

If your DB supports Common Table Expressions (CTE's), you can try this:

with 
x as   (
       select column
       from   table
       where  column in (:ids)
       )
select *
from   table
where  (table.column in (:ids) or (select count(*) from x) = 0)
splashout
  • 499
  • 5
  • 10