In Java DB2 JDBC: How can one use null parameters in a WHERE clause for a SELECT statement where the value can be either null or not null?

Question

For example I have the following select query for a PreparedStatement:

"SELECT FOO FROM BAR WHERE FOOBAR=?"

The parameter for FOOBAR can have a value and it could also be null.

Would the following code work?

if(<condition>) preparedStatement.setString(1, "<string value>");
else preparedStatement.setString(1, null);

If not, how should this be handled?

Answer 1

It won't work, null has no equivalence. If you're using a PreparedStatement, try this:

if(<condition>) preparedStatement.setObject(1, "<string value>");
else preparedStatement.setNull(1, Types.VARCHAR);

It will send the null as an IN parameter.

Answer 2

As per SQL standard, if a column has a NULL value, the search condition to check if it has NULL value is "column IS NULL". If column does not have null value you can have condition such as "column = 'blabla'. To make parameter markers work in both cases, change the statement as following:

SELECT FOO FROM BAR WHERE COALESCE(FOOBAR, 'a') = ? "

and have the following code:

if(<condition>) preparedStatement.setString(1, "<string value>");
else preparedStatement.setString(1, "a");

Whatever @Mark suggested did not work for me on Oracle.