JDBC what's the purpose of PreparedStatement#setNull

Question

I did an experiment with a table having a VARCHAR column with null values trying to get the number of rows that have a specific column NULL. I used three forms:

form A

SELECT COUNT(*) FROM buyers WHERE buye_resp IS NULL

form B

 SELECT COUNT(*) FROM buyers WHERE buye_resp = ?

... where the parameter is provided with setString(1, null)

form C

... like form B but the parameter is set with setNull(1, java.sql.Types.VARCHAR)

Of the three forms, only form A produced the correct result, forms B and C both returned 0 (code of the three forms at the end of the post). Which begs the question: what's the purpose of setNull?

The tests where run against a PostgreSQL 9.2 database.

code

private static int numOfRows_formA(Connection conn) throws SQLException {
    PreparedStatement pstm = null;
    ResultSet         rs   = null;
    try {
        String pstmStr = "SELECT COUNT(*) FROM buyers WHERE buye_resp IS NULL";
        pstm = conn.prepareStatement(pstmStr);
        rs =  pstm.executeQuery();
        rs.next();
        return rs.getInt(1);
    } finally {
        DbUtils.closeQuietly(null, pstm, rs);
    }
}

private static int numOfRows_formB(Connection conn) throws SQLException {
    PreparedStatement pstm = null;
    ResultSet         rs   = null;
    try {
        String pstmStr = "SELECT COUNT(*) FROM buyers WHERE buye_resp = ?";
        pstm = conn.prepareStatement(pstmStr);
        pstm.setString(1, null);
        rs = pstm.executeQuery();
        rs.next();
        return rs.getInt(1);
    } finally {
        DbUtils.closeQuietly(null, pstm, rs);
    }
}

private static int numOfRows_formC(Connection conn) throws SQLException {
    PreparedStatement pstm = null;
    ResultSet         rs   = null;
    try {
        String pstmStr = "SELECT COUNT(*) FROM buyers WHERE buye_resp = ?";
        pstm = conn.prepareStatement(pstmStr);
        pstm.setNull(1, java.sql.Types.VARCHAR);
        rs = pstm.executeQuery();
        rs.next();
        return rs.getInt(1);
    } finally {
        DbUtils.closeQuietly(null, pstm, rs);
    }
}

Answer 1

SQL uses ternary logic, therefore buye_responsible = ? always returns unknown (and never true) when buye_responsible is null. That's why you need IS NULL to check for null.

setNull() can be used, for example, when you need to pass nulls to INSERT and UPDATE statements. Since methods such as setInt() and setLong() take primitive types (int, long) you need a special method to pass null in this case.

Answer 2

In data base system a null is not equal to another null so the line SELECT COUNT(*) FROM vat_refund.er_buyers WHERE buye_responsible = null won't return any record. The setNull() method simply set a null at the index position.Sets the designated parameter to SQL NULL. This from the JAVA API. That is it will set a SQL null for that index, but it don't use isNull() function as desired by you. So that is why for form C also you are not getting any result.