How to find out if a Java ResultSet obtained is empty?

Question

Class.forName("org.sqlite.JDBC");
Connection conn =
    DriverManager.getConnection("jdbc:sqlite:userdata.db");
Statement stat = conn.createStatement();

ResultSet rs = stat.executeQuery("SELECT * from table WHERE is_query_processed = 0;");

int rowcount = rs.getRow(); 
System.out.println("Row count = "+rowcount); // output 1

rs.first(); // This statement generates an exception

Why is it so?

Answer 1

Did you try Google first?

http://www.google.com/search?hl=en&source=hp&q=jdbc+resultset+number+of+rows&aq=1&aqi=g10&aql=&oq=jdbc+resul&gs_rfai=CnX4s4mgCTOO3K4TIhgSyhKzICAAAAKoEBU_QWw9D

Answer 2

You can do this too:

rs.last();
int numberOfRows = rs.getRow();
if(numberOfRows) {
    rs.beforeFirst();
    while(rs.next()) {
        ...
    }
}

Answer 3

The pattern I normally use is as follows:

boolean empty = true;
while( rs.next() ) {
    // ResultSet processing here
    empty = false;
}

if( empty ) {
    // Empty result set
}

Answer 4

 while (results.next())

is used to iterate over a result set.so results.next() will return false if its empty.

Answer 5

Here's a simple method to do it:

public static void isResultSetEmpty(ResultSet resultSet) {
    return !resultSet.first();
}

Caveats

This moves the cursor to the beginning. But if you just want to test whether it's empty, you probably haven't done anything with it yet anyways.

Alternatively

Use the first() method immediately, before doing any processing. ResultSet rs = stat.executeQuery("SELECT * from table WHERE is_query_processed = 0;");

if(rs.first()) {
    // there's stuff to do
} else {
    // rs was empty
}

References

ResultSet (Java Platform SE 6)

Answer 6

Why is execution not entering the while loop?

If your ResultSet is empty the rs.next() method returns false and the body of the while loop isn't entered regardless to the rownumber (not count) rs.getRow() returns. Colins example works.

Answer 7

Shifting the cursor forth and back to determine the amount of rows is not the normal JDBC practice. The normal JDBC practice is to map the ResultSet to a List of value objects each representing a table row entity and then just use the List methods to determine if there are any rows.

For example:

List<User> users = userDAO.list();

if (users.isEmpty()) {
    // It is empty!
if (users.size() == 1) {
    // It has only one row!
} else {
    // It has more than one row!
}

where the list() method look like as follows:

public List<User> list() throws SQLException {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    List<User> users = new ArrayList<User>();

    try {
        connection = database.getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(SQL_LIST);
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getLong("id"));
            user.setName(resultSet.getString("name"));
            // ...
            users.add(user);
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return users;
}

Also see this answer for other JDBC examples.