6

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,616
  • 4
  • 55
  • 108

2 Answers2

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
  • 537
  • 5
  • 11
0

After spending the whole day i managed to finally find workaround:

jdbc.queryForList("""
        SELECT * FROM daily_section_value_dict WHERE COALESCE(:types) IS NULL OR type_id IN (:types)
        """, new MapSqlParameterSource(params));

The idea here is instead of directly using is null we wrap it in coalesce. And in case if :types will be null coalesce will return null, and after that jdbcTemplate doesn't throw error on null check. It was the only option that worked for me

ArtemAgaev
  • 78
  • 7