Java 如何处理 PreparedStatement 中的（可能）空值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4215135/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to deal with (maybe) null values in a PreparedStatement?
提问by Zeemee
The statement is
声明是
SELECT * FROM tableA WHERE x = ?
and the parameter is inserted via java.sql.PreparedStatement 'stmt'
并且参数是通过 java.sql.PreparedStatement 'stmt' 插入的
stmt.setString(1, y); // y may be null
If yis null, the statement returns no rows in every case because x = nullis always false (should be x IS NULL).
One solution would be
如果y为 null，则该语句在任何情况下都不返回任何行，因为x = null始终为 false（应该为x IS NULL）。一种解决方案是
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!
谢谢！
采纳答案by Paul Tomblin
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);
回答by dcp
would just use 2 different statements:
只会使用 2 个不同的语句：
Statement 1:
声明 1：
SELECT * FROM tableA WHERE x is NULL
Statement 2:
声明 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.
您可以检查变量并根据条件构建正确的语句。我认为这使代码更清晰，更容易理解。
EDITBy 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.
编辑顺便说一句，为什么不使用存储过程？然后，您可以在 SP 中处理所有这些 NULL 逻辑，并且可以简化前端调用中的事情。
回答by Knubo
If you use for instance mysql you could probably do something like:
例如，如果您使用 mysql，您可能会执行以下操作：
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.
你必须检查你的解释计划，看看它是否能提高你的表现。虽然这意味着空字符串等于 null，所以它不会满足您的需求。
回答by Zeemee
There is a quite unknown ANSI-SQL operator IS DISTINCT FROMthat handles NULL values. It can be used like that:
有一个非常不为人知的 ANSI-SQL 运算符IS DISTINCT FROM来处理 NULL 值。它可以这样使用：
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).
所以只需要设置一个参数。不幸的是，这不受 MS SQL Server (2008) 支持。
Another solution could be, if there is a value that is and will be never used ('XXX'):
另一个解决方案可能是，如果有一个永远不会被使用的值（'XXX'）：
SELECT * FROM tableA WHERE COALESCE(x, 'XXX') = COALESCE(?, 'XXX')
回答by Brian
In Oracle 11g, I do it this way because x = nulltechnically evaluates to UNKNOWN:
在 Oracle 11g 中，我这样做是因为x = null技术上评估为UNKNOWN：
WHERE (x IS NULL AND ? IS NULL)
OR NOT LNNVL(x = ?)
The expression before the ORtakes care of equating NULL with NULL, then the expression after takes care of all other possibilities. LNNVLchanges UNKNOWNto TRUE, TRUEto FALSEand FALSEto TRUE, which is the exact opposite of what we want, hence the NOT.
之前的表达式OR负责将 NULL 与 NULL 等同，然后表达式之后的表达式负责所有其他可能性。LNNVL变化UNKNOWN来TRUE，TRUE来FALSE和FALSE到TRUE，这是我们想要的正好相反，因此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.
在某些情况下，接受的解决方案在 Oracle 中对我不起作用，当它是更大表达式的一部分时，涉及NOT.
