CODEWITHSUNDEEP
javajdbcresultset
Ravikiran
Ravikiran

Reputation: 31

Handling null in java Resultset using wasnull()

I am trying to handle null in resultset getting below mentioned error

Code: here query returns null, in case of null versionNo should be 0 

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

Error: java.lang.NullPointerException at com.mysql.cj.jdbc.result.ResultSetImpl.wasNull(ResultSetImpl.java:2496)

Upvotes: 2

Views: 6173

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109045

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

Upvotes: 8

Related Questions