JDBC what is the purpose of PreparedStatement # setNull

advertisements

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


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.