Can a ResultSet be 'null' in Java?

Question

I have a very basic bit of code which executes a select query and returns a boolean depending if the result set is empty or not.

public boolean checkIfUserHasPreferences(String username){
        ResultSet rs = null;
        boolean checkBoolean = false;

        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            con = DriverManager.getConnection(Messages.getString("OracleUserManagement.0"), Messages.getString("OracleUserManagement.1"), Messages.getString("OracleUserManagement.2")); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

            PreparedStatement statement = con.prepareStatement("SELECT USERNAME FROM USER_PREFERENCES WHERE USERNAME = ?"); 
            statement.setString(1, username);
            rs = statement.executeQuery();
            if (rs == null){
                System.out.println("I checked it was true!");
                checkBoolean = true;
            } else {
                System.out.println("I checked it was false!");
                checkBoolean = false;
            }

            con.commit();

            con.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return checkBoolean;
    }

What confuses is me that even though the table/database is empty, it always prints out "I checked it was false!".

Is this because even if a result set returns 0 rows, it does not = null? Should I be using while (rs.next()) to check instead?

Solution 1

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.

Solution 2

No, ResultSet returned by executeQuery(java.lang.String) method can never be null.

Moreover, the standard way to check whether a ResultSet is empty or not is to try setting its cursor to first row by using its first() and if it returns false it indicates that ResultSet is empty.

So, in your case you don't even need to check rather just return rs.first();

For example:

if (!rs.first()) {
    // handle empty set: throw error or return
}

// continue processing the ResultSet further
do {
    // ...
} while (rs.next());

Solution 3

The result set will not be null until and unless:

1.It is initialized to null and done nothing else,

2.If the statement on which execute query is written is not correct(sql exception will occur and initialization will not happen).

This is irrelevant for this question, but will serve as a tip for people like me.

This happen when database operation is done in a separate class where statement.executeQuery() is given in try-catch which has no e.printStackTrace() or any other logging mechanics.

I have confronted with this error and that is the reason why I am writing. This can be a great problem when we do a batch process where one out of 50000 execution causes an exception(in my case:

com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002

this error was caused around 20000 iteration ) which causes unwanted result.

Comments

[Rohit Jain]

@oOTesterOo. Of course. You should first visit the API, when in problem. That's what they are there for.

[kidnan1991]

It is too many steps, just checking rs.next() and then process the cursor instead

[sactiw]

@kidnan1991 the solution lets you handle scenario where you want to take some action on empty resultset