在PreparedStatement中绑定空变量
我发誓这曾经有效，但在这种情况下不起作用。我试图匹配 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);
}
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?
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);
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这可能取决于 JDBC 驱动程序，但在大多数情况下，是的，您需要使用上面显示的更扩展的形式。
JDBC 准备好的语句通常是围绕参数化查询的本机实现的相对较薄的包装器，即带有 ? 的查询。 in place of 参数传递给查询编译器并编译，因此，稍后，当您调用 stmt.executeQuery() 时，该语句无法从
column = ?调整为column IS NULL。这与其说是 JDBC 的限制，不如说是 SQL 中 NULL 的语义 。对于 SQL，x = NULL未定义，x <> 也是如此。空。也就是说，某些 JDBC 驱动程序可能违反 SQL 中的
NULL-ity 概念，并允许 setNull() 将语句从= ?转换为IS NULL 这将是高度非标准的行为（尽管可以通过编写某种查询预处理方法轻松完成）。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 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 = ?tocolumn IS NULL. This isn't so much a limitation of JDBC as it the semantics of NULL in SQL. For SQLx = NULLis undefined as isx <> NULL.That said, some JDBC drivers may violate the notion of
NULL-ity in SQL and allow setNull() to transform the statement from= ?toIS NULLthis would be highly non-standard behavior (though it could be easily accomplished by writing some sort of query pre-processing method).你使用什么数据库？
但至少对于 Oracle，相等（和不等）永远不会匹配 NULL，你必须写 IS NOT NULL。
What database are you using?
But at least with Oracle, equality (and inequality) never matches NULL, you have to write IS NOT NULL.
一般来说，等于 NULL 的东西总是假的（甚至是 NULL，所以 SELECT * FROM tbl WHERE NULL=NULL; 将是一个空集），所以如果你想要接受这样的空相等
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我知道这也许为时已晚，但只是为了它，对 Oracle 的一个 hack 在我看来有效：
SQL：
在执行 jdbc 的代码中，调用如下所示：
I know that is too late perhaps, but just for the sake of it a hack for Oracle which seems to me working:
SQL:
And in the code where you do the jdbc call something like this: