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?
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);
}
