CreatedStatement.setNull (int parameterIndex, int sqlType)

Question

Question # 1

Could you tell me what advantage I will get 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 SQL value is null ; which I do not want.

---

Question number 2

And just curiously, stmt.setString(9, null) does what return rset.getInt("phone_type") do?

Answer # 2

getInt() will return zero when it is zero in the database. You should use the code below to learn about DB null .

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

Answer 1

I see no benefit using setNull in this case String. It is used only to check the empty string "" and insert zero into the database. But for this we can do it like stmt.setString(9, null);

But when sPhoneExt is Integer and null, we cannot execute stmt.setInt(9, sPhoneExt); since the setInt(int, int) API is executed; converting (Unboxing) sPhoneExt (Integer) to a primitive (int), so you get a NullPointerException. So you need stmt.setNull(9, java.sql.Types.INTEGER);

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

This is independent of the zero dial mechanism.

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

Someone also said when the DB column NUMBER has a default value; this default value will be treated differently by the two above lines. But this is not true. Even so, both of the above lines work the same. It sets to NULL ; not by default.

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