views:

237

answers:

3

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.

+1  A: 

You can execute

SELECT FOUND_ROWS()

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

Lalith
+1  A: 

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

Adeel Ansari
@Adeel Ansari: It still imposes a database read though.
Mr Morgan
@Mr Morgan: Thats perfactly fine for me. Actually, we do similar thing to achieve pagination. However, we ask specific number of rows in the 2nd query though. But here in your case `duffymo` is very much right. When you already queried the database, why not load that into a List or something.
Adeel Ansari
@Adeel Ansari: I'm currently looking at a 2 dimensional arraylist as per duffymo's idea. Before, I needed the resultset number of rows to work out the size of a 2 dimensional String array.
Mr Morgan
Or as I'm trying to access table metadata from the system schema, I can use a class with two fields, column name, column type, and then create an array list of this class. The main point here is to avoid iterating through a result set and this class in a collection should suffice.
Mr Morgan
+4  A: 

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.

duffymo
(+1) Of course, you are right. `count()` is perfectly fine for me, but not in this case. We normally do that to achieve pagination thingie.
Adeel Ansari