在JAVA for MySQL PreparedStatment中绑定NULL? [英] Binding NULL in JAVA for MySQL PreparedStatment?
问题描述
MySQL使用IS NULL而不是NULL,所以我如何绑定变量来实现相同?
MySQL uses "IS NULL" instead of "NULL" so how do i bind variable to achieve the same ?
现在我试过这个
selectStmt.setNull( 2, java.sql.Types.VARCHAR );
如果是这样,在预备状态下如何设置IS NULL?
when i printed "java.sql.Types.VARCHAR" shows value = 12 ? If so how does this set " IS NULL" in preparedstatment ?
更多详细信息:
即我有兴趣查询(MySQL)为null,如果我有输入中的字符串=。所以逻辑就是这样,
i.e I am interested in querying (MySQL) for null if i have string = " " in input. So logic goes like this,
Connection c = SQLCon.connect();
PreparedStatement selectStmt = c.prepareStatement( "select * from "
+ TableName
+ " where P = ? AND M = ? AND PR = ? AND DR = ? " );
if ( P.trim().equals( "" ) )
{
selectStmt.setNull( 1, java.sql.Types.VARCHAR );
} else
{
selectStmt.setString( 1, P );
}
if ( M.trim().equals( "" ) )
{
selectStmt.setNull( 2, java.sql.Types.VARCHAR );
} else
{
selectStmt.setString( 2, M );
}
请咨询
推荐答案
如果要匹配where子句中可能为null的列与可能为null的参数匹配,则实际上需要将其绑定两次,因为在SQL中为null不等于其他任何东西,甚至是另一个null。
If you want to match a column in a where clause that might be null with a parameter that might be null, you actually need to bind it twice, because in SQL a null doesn't equal anything else, even another null.
PreparedStatement stmt = conn.prepareStatement("select * from foo where (? is null and bar is null) or (bar = ?)");
col = 1;
setVarchar(stmt, col++, var);
setVarchar(stmt, col++, var);
void setVarchar(PreparedStatement stmt, int col, String var)
{
if (var == null)
stmt.setNull(col, java.sql.Types.VARCHAR);
else
stmt.setString(col, var);
}
这篇关于在JAVA for MySQL PreparedStatment中绑定NULL?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!