How to deal with (maybe) null values in a Prepared

问题:

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!

回答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);

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

回答3:

would just use 2 different statements:

Statement 1:

SELECT * FROM tableA WHERE x is NULL

Statement 2:

SELECT * FROM tableA WHERE x = ?

You can check your variable and build the proper statement depending on the condition. I think this makes the code much clearer and easier to understand.

EDIT By the way, why not use stored procedures? Then you can handle all this NULL logic in the SP and you can simplify things on the front end call.

回答4:

If you use for instance mysql you could probably do something like:

select * from mytable where ifnull(mycolumn,'') = ?;

Then yo could do:

stmt.setString(1, foo == null ? "" : foo);

You would have to check your explain plan to see if it improves your performance. It though would mean that the empty string is equal to null, so it is not granted it would fit your needs.

回答5:

In Oracle 11g, I do it this way because x = null technically evaluates to UNKNOWN:

WHERE (x IS NULL AND ? IS NULL)
    OR NOT LNNVL(x = ?)

The expression before the OR takes care of equating NULL with NULL, then the expression after takes care of all other possibilities. LNNVL changes UNKNOWN to TRUE, TRUE to FALSE and FALSE to TRUE, which is the exact opposite of what we want, hence the NOT.

The accepted solution didn't work for me in Oracle in some cases, when it was part of a larger expression, involving a NOT.