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 = 0;
ResultSet rs = magicallyAppearingStmt.executeQuery(query);
if (rs.next()) {
    iVal = rs.getInt("ID_PARENT");
    if (rs.wasNull()) {
        // handle NULL field value
    }
}

(Edited as @martin comments below; the OP code as written would not compile because iVal is not initialised)

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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 6

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

Answer 7

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

Answer 8

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

    // ...

}

Answer 9

Just in case someone comes here while programming in Kotlin (as I did), the answer suggested by BalusC works fine. Just be aware that Short and Float are instantiated as Integer and Double (respectively) inside ResultSet, and we should cast them to the correct type after calling getObject(). In my case the final code was:

when {
    propKClass.isSubclassOf(Int::class) -> rs.getObject(colName) as Int? 
    propKClass.isSubclassOf(Short::class) -> (rs.getObject(colName) as Int?)?.toShort()
    propKClass.isSubclassOf(Long::class) -> rs.getObject(colName) as Long?
    propKClass.isSubclassOf(Boolean::class) -> rs.getObject(colName) as Boolean?
    propKClass.isSubclassOf(Double::class) -> rs.getObject(colName) as Double?
    propKClass.isSubclassOf(Float::class) -> (rs.getObject(colName) as Double?)?.toFloat()
    else -> rs.getString(colName)
}

Answer 10

If you want an alternative to calling ResultSet.wasNull() you can use getObject() and cast to the correct type.

Long val = (Long)rs.getObject(pos++);

You can also set null values in a Statement with setObject().

pstmt.setObject(pos++, null);

Answer 11

In Kotlin I would just solve it once and be done with the issue forever with this:

fun <K : Any> ResultSet.getNullable(columnLabel: String, type: KClass<K>): K? = 
    this.getObject(columnLabel, type.java)

So later you can just do this:

rs.getNullable("ID_PARENT", Int::class)

I guess if you want you could also do this too

fun <K> ResultSet.getNullable(columnLabel: String, type: Class<K>): K? = 
    this.getObject(columnLabel, type)

So you can just do this:

rs.getNullable("ID_PARENT", Int::class.java)

Or better still make both methods available if you happen to be dealing with developers that can't agree on even the simplest of things.

fun <K : Any> ResultSet.getNullable(columnLabel: String, type: KClass<K>): K? =
        this.getNullable(columnLabel, type.java)

fun <K> ResultSet.getNullable(columnLabel: String, type: Class<K>): K? =
        this.getObject(columnLabel, type)

Edit: if the library is still being fussy you can finally do something like:

rs.getNullable("ID_PARENT", String::class)?.let {FOO.valueOf(it) }

Answer 12

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

Answer 13

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.