Does anyone know a better way of getting the number of rows in a Java resultset returned from a MySQL database? 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's COUNT
aggregate function.
public static int getResultSetRowCount(ResultSet resultSet) { int size = 0; try { resultSet.last(); size = resultSet.getRow(); resultSet.beforeFirst(); } catch(Exception ex) { return 0; } return size; }
The SQL Count() function returns the number of rows in a table. Using this you can get the number of rows in a table.
To number rows in a result set, you have to use an SQL window function called ROW_NUMBER() . This function assigns a sequential integer number to each result row. However, it can also be used to number records in different ways, such as by subsets.
The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.
println("Total number of rows in ResultSet object = "+rowCount); Simply iterate on ResultSet object and increment rowCount to obtain size of ResultSet object in java. rowCount = rs. getRow();
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With