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

Question

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!

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

Answer 2

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')