Checking for a null int value from a Java ResultSet

Question

In Java I'm trying to test for a null value, from a ResultSet, where the column is being cast to a primitive int type.

int iVal;
ResultSet rs = magicallyAppearingStmt.executeQuery(query);
if (rs.next()) {
  if (rs.getObject("ID_PARENT") != null && !rs.wasNull()) {
    iVal = rs.getInt("ID_PARENT");
  }
}

From the code fragment above, is there a better way to do this, and I assume that the second wasNull() test is redundant?

Educate us, and Thanks

Answer 1

The default for ResultSet.getInt when the field value is NULL is to return 0, which is also the default value for your iVal declaration. In which case your test is completely redundant.

If you actually want to do something different if the field value is NULL, I suggest:

int iVal;
ResultSet rs = magicallyAppearingStmt.executeQuery(query);
if (rs.next()) {
    iVal = rs.getInt("ID_PARENT");
    if (rs.wasNull()) {
        // handle NULL field value
    }
}

Answer 2

I think, it is reduntant. rs.getObject("ID_PARENT") should return an Integer object or null, if the column value actually was NULL. So it should even be possible to do something like:

if (rs.next()) {
  Integer idParent = (Integer) rs.getObject("ID_PARENT");
  if (idParent != null) {
    iVal = idParent; // works for Java 1.5+
  } else {
    // handle this case
  }      
}

Answer 3

AFAIK you can simply use

iVal = rs.getInt("ID_PARENT");
if (rs.wasNull()) {
  // do somthing interesting to handle this situation
}

even if it is NULL.

Answer 4

Another nice way of checking, if you have control the SQL, is to add a default value in the query itseld for your int column. Then just check for that value.

e.g for ORACLE database, use NVL

SELECT NVL(ID_PARENT, -999) FROM TABLE_NAME;

then check

if (rs.getInt('ID_PARENT') != -999) { }

Of course this also is under the assumption that there is a value that wouldn't normally be found in the column

Answer 5

an other solution:

public class DaoTools
{
    static public Integer getInteger( ResultSet rs, String strColName ) throws SQLException
    {
        int nValue = rs.getInt( strColName );
        if ( rs.wasNull() ) return null;
        return new Integer( nValue );
    }
}

Answer 6

I myself use the following idiom (substitute -1 with whatever null-case value you want):

int i = rs.getObject("col") != null ? rs.getInt("col") : -1;

I find it personally prettier than:

int i = rs.getInt("col");
if (rs.wasNull()) {
    i = -1;
}

A bit decent JDBC driver won't hit the DB two times on getObject() and getInt().