In Java DB2 JDBC: how can you use null parameters in a WHERE clause for a SELECT statement, where the value can be either null or non-null?

Question

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

"CHOOSE FOO FROM BAR WHERE FOOBAR =?"

The parameter for FOOBAR may have a value, and it may also be null.

Will 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

This will not work, null has no equivalence. If you are using PreparedStatement, try the following:

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

It will send a null value as an IN parameter.

Answer 2

According to the SQL standard, if the column is NULL, a search condition is to check if the NULL value is "column IS NULL". If the column is not null, you can have a condition such as "column = 'blabla". To make parameter markers work in both cases, modify the statement as follows:

 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"); 

No matter what @Mark suggested, I did not work for me in Oracle.

Answer 3

There is a difference between a null value and null values ​​in a column.

Answer 4

 if(requiredValue == null){ sql = "SELECT * FROM Table Where COLUMN is NULL" } 

You do not need to set a value in this case.