Handling the Null Value from a Resultset

Checking for a null int value from a Java ResultSet

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)

JAVA - Checking for NULL value from a result set

Your probably could try using of ResultSet.getObject(int columnIndex) method. From its JavaDoc description:

This method will return the value of the given column as a Java
object. The type of the Java object will be the default Java object
type corresponding to the column's SQL type, following the mapping for
built-in types specified in the JDBC specification. If the value is an
SQL NULL, the driver returns a Java null.

Handling null value while dynamically converting resultset to JSON

This is a result of a limitation in JSONObject. You need to place a JSONObject.NULL in order to see an entry in your JSON object. I would try modifying your code to the following:

} else {
    Object object = rsNew.getObject(column_name);
    if (object != null) {
      obj.put(column_name, rsNew.getObject(column_name));
    } else {
      obj.put(column_name, JSONObject.NULL);
    }
}

Now this might be a little different depending on which JSON library and version you are using. Feel free to include those details in your question.

Handling null in java Resultset using wasnull()

The MySQL Connector/J driver should not throw a NullPointerException in this situation, but instead should have thrown a SQLException that the result set is not on a row. This is a bug.

However, the wasNull() method is for checking if the last primitive value read from the result set was null (as primitives don't support null, while a database column does):

Reports whether the last column read had a value of SQL NULL. Note
that you must first call one of the getter methods on a column to try
to read its value and then call the method wasNull to see if the
value read was SQL NULL.

There are three problems with your code:

1. You always need to use rs.next() to check if there is a row (and move to that row if it exists), before you can retrieve values.
2. You need to retrieve a value (eg getInt(1)), before you can call wasNull().
3. You use wasNull() to assign 0 to versionNo, which is entirely unnecessary because getInt will return 0 if the column value is NULL (see getInt: "Returns: the column value; if the value is SQL NULL, the value returned is 0")

To fix your code, you need to do something like:

try (ResultSet res = stmt.executeQuery(query)) {
    while (res.next()) {
        versionNo = res.getInt(1);
        if (res.wasNull()) {
            // whatever you want to, other than setting versionNo to 0
        }
    }
}

Be aware that if you have multiple rows, this will effectively return the value of the last row.

On the other hand if you want to check if there is no row, then you should do something like:

Option 1: equivalent logic

try (ResultSet res = stmt.executeQuery(query)) {
    if (res.next()) {
       do {
           versionNo = res.getInt(1);
       } while (res.next());
    } else {
        // no rows
    }
}

Option 2: initialize versionNo to a default value. If you are only interested in initializing versionNo to zero, you can also just do it before processing rows.

try (ResultSet res = stmt.executeQuery(query)) {
    versionNo = 0;
    while (res.next()) {
        versionNo = res.getInt(1);
    }
}

Reading null values from ResultSet

I believe the issue is that you need to configure the connection to convert a zero datetime to null per the following post:

handling DATETIME values 0000-00-00 00:00:00 in JDBC

Can a ResultSet be 'null' in Java?

You could have looked onto the API of Statement#executeQuery() method. It says:

Returns:

• a ResultSet object that contains the data produced by the given query; never null

Emphasis mine.

Should I be using while (rs.next()) to check instead?

Yes.

---

Related Topics