preparedStatement.setNull(int parameterIndex, int sqlType)

Question

Question #1

Could you anyone please tell me what is the benefit I will receive using the following code, when sPhoneExt is null ?

if (sPhoneExt == null || sPhoneExt.trim().equals("")) {
    stmt.setNull(9, java.sql.Types.INTEGER);
} else {
    stmt.setString(9, sPhoneExt);
}

Instead of stmt.setString(9, sPhoneExt);

Because iPhoneType = rset.getInt("phone_type"); will return 0 if the value is SQL NULL; which I don't want.

---

Question #2

And just curious stmt.setString(9, null) is performed what will be the return of rset.getInt("phone_type")?

Answer #2

getInt() will return zero when it is null in DB. You have to use the below code to know about DB NULL.

if (rs.wasNull()) {
    // handle NULL field value
}

Answer 1

I don't see any benefit using setNull in this String case. It is only used to check empty string "" and insert null in DB. But for that also we can do it like stmt.setString(9, null);

But when sPhoneExt is Integer and holding null, then We cannot perform stmt.setInt(9, sPhoneExt); since setInt(int, int) API performs; converting (Unboxing) sPhoneExt (Integer) to primitive (int), so you will get NullPointerException. So you are in need of stmt.setNull(9, java.sql.Types.INTEGER);

Finally if you have inserted null in DB for NUMBER (sql type) column; getInt() will return 0 only.

This is irrespective of the below null set mechanism.

stmt.setString(9, null);
stmt.setNull(9, java.sql.Types.INTEGER)

Also Somebody told when the DB NUMBER column has default Value; that default value will be consider differently by the above two lines. But that is not true. Even that case also both the above line performs same way. It is setting NULL value; not the default value.

create table t1 (id number default 1 );
insert into t1 (id) values (2);

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestDB {
    public static void main(String args[]) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection("jdbc:oracle:thin:@10.201.32.92:1521:psprd1", "username", "password");
            String query = null;
            String l = null;
            ResultSet rset = null;
            int paramIndex = 1;
            query = "UPDATE t1 " + " SET id = ?";

            stmt = con.prepareStatement(query);
            stmt.setInt(paramIndex++, null);
            // stmt.setNull(1, java.sql.Types.INTEGER);
            stmt.executeUpdate();
            stmt.close();

            query = "select id from t1 ";
            stmt = con.prepareStatement(query);
            rset = stmt.executeQuery();
            rset.next();
            System.out.println(rset.getString("id"));
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
                stmt.close();
                con.close();
            } catch (Exception e) {
                e.printStackTrace();

            }
        }
    }
}