How to find out if a Java ResultSet obtained is em

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

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 2

Here's a simple method to do it:

public static boolean 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 3

This checks if it's empty or not while not skipping the first record

if (rs.first()) {
    do {
        // ResultSet is not empty, Iterate over it
    } while (rs.next());
} else {
    // ResultSet is empty
}

Answer 4

Try with this:

ResultSet MyResult = null;
MyResult = Conexion.createStatement().executeQuery("Your Query  Here!!!");
MyResult.last();
int NumResut = MyResult.getRow();MyResult.beforeFirst();
//Follow with your other operations....

This manner you'll be able work normally.

Answer 5

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 6

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.