JDBC ResultSet total rows

Question

I am implementing Paging in my application. For this i run a query and get a resultset. Now i want to get total no of records in this resultset for my paging calculation. How can i get ? i dont want to execute a extra sql which gives me total rows

Answer 1

If I'm not mistaken, the default behavior for a ResultSet is not to obtain all the rows at once, so there is no way to know from the object itself how many rows would be returned from the query without first iterating (and thus retrieving) all of them. You might get different behavior with specific JDBC drivers for specific databases.

May I ask why it is too costly for you to run a COUNT() query first ? Compared to the cost of retrieving the actual values, it shouldn't be too expensive.

Answer 2

The normal practice is to map the ResultSet to a List<Entity> where Entity is a javabean representing the actual data, e.g. User, Product, Order, etc.

Then, you can just use List methods like List#size() to obtain the rowcount.

List<Entity> entities = entityDAO.list();
int rows = entities.size();

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

Answer 3

Another option is to add the count aggregation as a sub queried column in your query. If your database is just a little bit smart, it will only execute that once. You should be able to check this easily using the query analyzer in your favorite database.

SELECT id,username,(SELECT COUNT(id) FROM users) FROM users;

Answer 4

From a comment to BalusC's answer:

[...] Actually i dont want to get all rows in a collection because i want to display only 10 rows on page thus my paging calculation helps me to get only 10 rows from resultset. For this i need total no of rows in resultset

You want nothing but asking the database for about 10 rows and the size of the table. so you actually have two (2) questions to your data store which is equal to two (2) select queries. Do it as Uri suggested and don't care about 'best practice'. If one day someone comes around with a better practice you still can decide whether to adapt your code or not.