javanullprepared-statement

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

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