Getting the count of rows in a Java resultset

Question

Hello

Does anyone know a better way of getting the number of rows in a Java resultset returned from a MySQL database?

I'm currently using this:

public static int getResultSetRowCount(ResultSet resultSet) {
    int size = 0;
    try {
        resultSet.last();
        size = resultSet.getRow();
        resultSet.beforeFirst();
    }
    catch(Exception ex) {
        return 0;
    }
    return size;
}

But am open to alternatives.

The resultset returned is not going to be the total number of rows read from the database so I don't think I can use SQL COUNT.

Thanks

Mr Morgan.

Answer 1

You can execute

SELECT FOUND_ROWS()

immediately after executing your SELECT statement to find the row count.

Answer 2

You can always use SELECT COUNT() with the same exact conditions, before making the actual SELECT.

Answer 3

A better answer is to forget about the number of rows until you've successfully loaded the ResultSet into an object or collection. You can keep the count of the number of rows with either of those options.

It's important to close ResultSets (and all SQL resources like Connection and Statement) in the narrowest method scope possible. That means not passing ResultSet out of the persistence layer. Better to get the number of rows using a Collection size() call.

Stop thinking about databases and start thinking in terms of objects. Java's an object-oriented language.