Java’s java.sql
package provides a general API for accessing data stored in relational databases. It is part of JDBC (Java Database Connectivity). The API is relatively low-level, and is often used via higher-level abstractions based on JDBC, such as query builders like jOOQ, or object–relational mappers (ORMs) like Hibernate.
If you choose to use JDBC directly you have to be aware that the API relatively old. It was added as part of JDK 1.1 and predates later additions to the language such as generics and optionals. There are also some pitfalls to be avoided. One of these pitfalls is ResultSet’s wasNull method.
The wasNull method
The wasNull
method reports whether the database value of the last ‘get’ call for a nullable table column was NULL or not:
int height = resultSet.getInt("height");
if (resultSet.wasNull()) {
height = defaultHeight;
}
The wasNull
check is necessary, because the return type of getInt
is the primitive data type int
, not the nullable Integer
. This way you can find out whether the actual database value is 0 or NULL.
The problem with this API design is that the ResultSet type is very stateful. Its state does not only change with each row (by calling next
method), but also with each ‘get’ method call.
If any other ‘get’ method call is inserted between the original ‘get’ method call and its wasNull
check the code will be wrong. Here’s an example. The original code is:
var width = rs.getInt("width");
var height = rs.getInt("height");
var size = new Size(width, rs.wasNull() ? defaultHeight : height);
A developer now wants to add a third dimension to the size:
var width = rs.getInt("width");
var height = rs.getInt("height");
var depth = rs.getInt("depth");
var size = new Size(width, rs.wasNull() ? defaultHeight : height, depth);
It’s easy to overlook the wasNull
call, or to wrongly assume that adding another ‘get’ method call is a safe code change. But the wasNull
check now refers to “depth” instead of “height”, which breaks the original intention.
Advice
So my advice is to wrap the ‘get’ calls for nullable database values in their own methods that return an Optional
:
Optional<Integer> getOptionalInt(ResultSet rs, String columnName) {
final int value = rs.getInt(columnName);
if (rs.wasNull()) {
return Optional.empty();
}
return Optional.of(value);
}
Now the default value fallback can be safely applied with the orElse
method:
var width = rs.getInt("width");
var height = getOptionalInt(rs, "height").orElse(defaultHeight);
var depth = rs.getInt("depth");
var size = new Size(width, height, depth);
The wasNull() pitfall is indeed a painful one when using JDBC directly. Since you’re mentioning jOOQ, note that you can also use jOOQ as a JDBC proxy to check your application for any missing or superfluous wasNull() calls: https://www.jooq.org/doc/latest/manual/sql-execution/diagnostics/diagnostics-was-null-calls/