CODEWITHSUNDEEP
javasqljdbcnullresultset
DeaIss
DeaIss

Reputation: 2585

Can a ResultSet be 'null' in Java?

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?

Upvotes: 3

Views: 30770

Answers (3)

sactiw
sactiw

Reputation: 22461

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

Upvotes: 6

padippist
padippist

Reputation: 1206

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.

Upvotes: 2

Rohit Jain
Rohit Jain

Reputation: 213281

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.

Upvotes: 11

Related Questions