JDBC - select where column is NULL

Issue

I have a simple table in my Postgres 9.0 database:

create table test (id int not null, value int);

I have populated it with a few rows:

insert into test values (1, 1);
insert into test values (2, null);
insert into test values (3, null);
insert into test values (4, 1);

Now I'm trying to read it with JDBC. When I select by the non-null values in the value column, everything is fine:

PreparedStatement select = c.prepareStatement("select * from test where value=?");
select.setInt(1, 1);
return select.executeQuery();

But when I want to select rows where value is null, the Result Set contains no rows. I have tried both of these approaches:

select.setObject(1, null);

and

select.setNull(1, Types.INTEGER);

Neither work!

What's going on? I know that the correct SQL to check for NULLs would be where value is null instead of where value=null but surely JDBC is clever enough to sort that out for me?

Solution

Nothing is = NULL. If you typed select * from test where value=NULL into an interactive query evaluator, you'd get nothing back. JDBC doesn't rewrite your expression, it just substitutes in the values.

You have to use a query using the is operator instead:

PreparedStatement select = c.prepareStatement("select * from test where value is NULL");
return select.executeQuery();

You've said you expect JDBC to be "clever" enough to do that for you, but that would be a big violation of the separation of concerns. You may well want to have a parameter in your query using = which you set NULL knowing that that relation will never evaluate true (as part of a larger set of conditions, most likely).

Answered By - T.J. Crowder