[java] Checking for a null int value from a Java ResultSet

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

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

e.g for an 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.

For convenience, you can create a wrapper class around ResultSet that returns null values when ResultSet ordinarily would not.

public final class ResultSetWrapper {

    private final ResultSet rs;

    public ResultSetWrapper(ResultSet rs) {
        this.rs = rs;
    }

    public ResultSet getResultSet() {
        return rs;
    }

    public Boolean getBoolean(String label) throws SQLException {
        final boolean b = rs.getBoolean(label);
        if (rs.wasNull()) {
            return null;
        }
        return b;
    }

    public Byte getByte(String label) throws SQLException {
        final byte b = rs.getByte(label);
        if (rs.wasNull()) {
            return null;
        }
        return b;
    }

    // ...

}

You can call this method using the resultSet and the column name having Number type. It will either return the Integer value, or null. There will be no zeros returned for empty value in the database

private Integer getIntWithNullCheck(ResultSet rset, String columnName) {
    try {
        Integer value = rset.getInt(columnName);
        return rset.wasNull() ? null : value;
    } catch (Exception e) {
        return null;
    }
}

Just an update with Java Generics.

You could create an utility method to retrieve an optional value of any Java type from a given ResultSet, previously casted.

Unfortunately, getObject(columnName, Class) does not return null, but the default value for given Java type, so 2 calls are required

public <T> T getOptionalValue(final ResultSet rs, final String columnName, final Class<T> clazz) throws SQLException {
    final T value = rs.getObject(columnName, clazz);
    return rs.wasNull() ? null : value;
}

In this example, your code could look like below:

final Integer columnValue = getOptionalValue(rs, Integer.class);
if (columnValue == null) {
    //null handling
} else {
    //use int value of columnValue with autoboxing
}

Happy to get feedback

I think, it is redundant. 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
  }      
}

Another solution:

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

With java 8 you can do this:

Long nVal = Optional.ofNullable(resultSet.getBigDecimal("col_name"))
                    .map(BigDecimal::longValue).orElse(null));

In that case you ensure that the nVal will be null (and not zero) if the SQL value is NULL

Just check if the field is null or not using ResultSet#getObject(). Substitute -1 with whatever null-case value you want.

int foo = resultSet.getObject("foo") != null ? resultSet.getInt("foo") : -1;

Or, if you can guarantee that you use the right DB column type so that ResultSet#getObject() really returns an Integer (and thus not Long, Short or Byte), then you can also just typecast it to an Integer.

Integer foo = (Integer) resultSet.getObject("foo");

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.