Binding a null variable in a PreparedStatement

Question

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

Answer 1

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

Answer 2

What database are you using?

But at least with Oracle, equality (and inequality) never matches NULL, you have to write IS NOT NULL.

Answer 3

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 = ? to column IS NULL. This isn't so much a limitation of JDBC as it the semantics of NULL in SQL. For SQL x = NULL is undefined as is 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 NULL this would be highly non-standard behavior (though it could be easily accomplished by writing some sort of query pre-processing method).