How to deal with (maybe) null values in a PreparedStatement?

The statement is

SELECT * FROM tableA WHERE x = ?

and the parameter is inserted via java.sql.PreparedStatement 'stmt'

stmt.setString(1, y); // y may be null

If y is null, the statement returns no rows in every case because x = null is always false (should be x IS NULL). One solution would be

SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL)

But then i have to set the same parameter twice. Is there a better solution?

Thanks!

Solution 1:

I've always done it the way you show in your question. Setting the same parameter twice is not such a huge hardship, is it?

SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL);

There is a quite unknown ANSI-SQL operator IS DISTINCT FROM that handles NULL values. It can be used like that:

SELECT * FROM tableA WHERE x NOT IS DISTINCT FROM ?

So only one parameter has to be set. Unfortunately, this is not supported by MS SQL Server (2008).

Another solution could be, if there is a value that is and will be never used ('XXX'):

SELECT * FROM tableA WHERE COALESCE(x, 'XXX') = COALESCE(?, 'XXX')