Checking for a null int value from a Java ResultSet

java

null-handling

resultset

ternary-operator

byAlex Kataev·Nov 27, 2024

To manage possible null values when getting an int from a ResultSet, employ the wasNull() method right after fetching the data:

int value = rs.getInt("column_name");
if (rs.wasNull()) {
    // Oops! value was SQL NULL
} else {
    // Bravo! value is valid
}

Crucial: Fetch the value using getInt() and immediately invoke wasNull() to verify if it was null.

Detailed breakdown of null handling

One of the common hurdles when working with a ResultSet and retrieving primitive types is null handling. Alarmingly, the getInt() method returns 0 when the column value is null, leading to possible confusion especially when 0 is a valid piece of data in your dataset. Using rs.wasNull() immediately after calling getInt() empowers you to differentiate an authentic 0 from a null.

In certain scenarios, you might prefer a specified alternate value or behavior when you encounter a null. The ternary operator in combination with getObject() comes in handy:

Integer valueObj = (Integer) rs.getObject("column_name");
int value = valueObj != null ? valueObj : defaultValue; // defaultValue can be any int value

Remember, while using getObject(), ensure compatibility with the expected return type to avoid a cold ClassCastException.

Simplify your code with utility methods

To make your code more compact and reusable, craft a utility method that wraps up the null check logic:

public Integer retrieveNullableInt(ResultSet rs, String columnName) throws SQLException {
    Integer value = (Integer) rs.getObject(columnName);
    return value != null ? value : null; // switch the 'null' with a default value, if necessary
}

Such utility methods can be reused wherever you need to fetch integer data, ensuring a uniform null handling approach across your software.

Workaround for various data types and null substitution

In some rare but possible circumstances, the column could house data of various types. The getObject() method can thus provide greater adaptability:

Object columnValue = rs.getObject("column_name");
if (columnValue == null) {
    // Null, now what?
} else if (columnValue instanceof Integer) {
    int intValue = (Integer) columnValue; // Process intValue if you dare
} else {
    // Surprise, surprise! Handle non-int data types
}

This strategy provides a type-safe method to process various data types and nulls with grace.

Embrace the NULLs

What if your application logic requires specific action when it bumps into a null value? You can then customize the behavior to suit your application:

int value = rs.getInt("column_name");
if (rs.wasNull()) {
    value = computeDefaultValue(); // A method that fetches your default value
    nullAlert(); // A method that waves a flag when null is detected
}