java 在 PreparedStatement 中绑定空变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2649997/
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
Binding a null variable in a PreparedStatement
提问by Paul Tomblin
I swear this used to work, but it's not in this case. I'm trying to match col1, col2 and col3, even if one or more of them is null. I know that in some languages I've had to resort to circumlocutions like ((? is null AND col1 is null) OR col1 = ?). Is that required here?
我发誓这曾经有效，但在这种情况下不是。我正在尝试匹配 col1、col2 和 col3，即使其中一个或多个为空。我知道在某些语言中，我不得不求助于像((? is null AND col1 is null) OR col1 = ?). 这里需要吗？
PreparedStatement selStmt = getConn().prepareStatement(
"SELECT * " +
"FROM tbl1 " +
"WHERE col1 = ? AND col2 = ? and col3 = ?");
try
{
int col = 1;
setInt(selStmt, col++, col1);
setInt(selStmt, col++, col2);
setInt(selStmt, col++, col3);
ResultSet rs = selStmt.executeQuery();
try
{
while (rs.next())
{
// process row
}
}
finally
{
rs.close();
}
}
finally
{
selStmt.close();
}
// Does the equivalient of stmt.setInt(col, i) but preserves nullness.
protected static void setInt(PreparedStatement stmt, int col, Integer i)
throws SQLException
{
if (i == null)
stmt.setNull(col, java.sql.Types.INTEGER);
else
stmt.setInt(col, i);
}
采纳答案by ig0774
This may depend on the JDBC driver, but for the most part, yes, you would need to use the more extended form you show above.
这可能取决于 JDBC 驱动程序，但在大多数情况下，是的，您需要使用上面显示的更扩展的形式。
JDBC prepared statements are usually relatively thin wrappers around a native implementation of a parameterized query, i.e., the query with ? in place of parameters are passed to the query compiler and compiled, so, later, when you call stmt.executeQuery(), the statement cannot be adjust from a column = ?to column IS NULL. This isn't so much a limitation of JDBC as it the semantics of NULL in SQL. For SQL x = NULLis undefined as is x <> NULL.
JDBC 准备好的语句通常是参数化查询的本机实现的相对较薄的包装器，即带有 ? 代替参数传递给查询编译器并进行编译，因此，稍后，当您调用 stmt.executeQuery() 时，语句无法从 a 调整 column = ?为column IS NULL。这与其说是 JDBC 的限制，不如说是 SQL 中 NULL 的语义。因为 SQLx = NULL是未定义的x <> NULL。
That said, some JDBC drivers may violate the notion of NULL-ity in SQL and allow setNull() to transform the statement from = ?to IS NULLthis would be highly non-standard behavior (though it could be easily accomplished by writing some sort of query pre-processing method).
也就是说，一些 JDBC 驱动程序可能违反NULLSQL 中的-ity概念，并允许 setNull() 将语句从这里转换= ?为IS NULLthis 将是高度非标准的行为（尽管它可以通过编写某种查询预处理轻松实现方法）。
回答by Thilo
What database are you using?
你用的是什么数据库？
But at least with Oracle, equality (and inequality) never matches NULL, you have to write IS NOT NULL.
但至少在 Oracle 中，相等（和不等）永远不会匹配 NULL，您必须编写 IS NOT NULL。
回答by Michael Mrozek
Generally something being equal to NULL is always false (even NULL, so SELECT * FROM tbl WHERE NULL=NULL;will be an empty set), so you probably do need to do it the long way if you want to accept null equality like that
通常等于 NULL 的东西总是假的（即使是 NULL，所以SELECT * FROM tbl WHERE NULL=NULL;将是一个空集），所以如果你想接受这样的 null 相等，你可能需要很长的路要走
回答by hephestos
I know that is too late perhaps, but just for the sake of it a hack for Oracle which seems to me working:
我知道这也许为时已晚，但只是为了它对 Oracle 进行了一次黑客攻击，在我看来它正在工作：
SQL:
查询语句：
SELECT.....FROM....WHERE NVL(CUST_ID,0)=? AND NVL(vendor_id,0)=?
And in the code where you do the jdbc call something like this:
在执行 jdbc 调用的代码中，如下所示：
pstmt.setString(1, ( xxo.getCustId().equals("")) ? "0":xxo.getCustId());
pstmt.setString(2, ( xxo.getVendId().equals("")) ? "0":xxo.getVendId());
rs = pstmt.executeQuery();
