CODEWITHSUNDEEP
javajdbcpaginationresultsetrowcount
Zeeshan
Zeeshan

Reputation: 1183

JDBC ResultSet total rows

I am implementing Paging in my application. For this I run a query and get a ResultSet.
Now I want to get total number of records in this ResultSet for my paging calculation.
How can I get this ? I don't want to execute extra SQL which gives me total rows.

Upvotes: 2

Views: 6492

Answers (7)

Lokesh
Lokesh

Reputation: 1323

There is no point in counting List size to get record count, as we are implementing pagination and SHOULD NOT load entire resultset at a time.

I use ROW_NUM at database level to implement pagination logic. We need to get the as many records as we need to show it on the screen.

Example: select * from Emp where rownum>=:beginRecord and rownum<=:endRecord

***** Logic will be the same but syntax may change depending up on the type of the database. Need to use nested query if we need to do order by of any column.*

I believe, count(*) is expensive operation, rather i prefer partition by.

Select Eno, Ename , (select count() from emp) as record_count from Emp -- Expensive Select Eno, Ename , count() over (partition by eno) as record_count from Emp -- Preferred one.

***** Partition by syntax may change depending up on the type of the database.*

I have considered Oracle database.

Upvotes: 0

Fareed Alnamrouti
Fareed Alnamrouti

Reputation: 32174

in addition to Fathah solution you can use this code, also note that because its a memory pointer this solution has no performance issues:

int totalRows = 0;
if(rowSet.last()) {
   totalRows = rowSet.getRow();
}
rowSet.beforeFirst();

Upvotes: -1

Fathah Rehman P
Fathah Rehman P

Reputation: 8751

int totalRows = 0;
    try    {
        resultSet.last();
        totalRows = resultSet.getRow();
        resultSet.beforeFirst();
    } catch(Exception ex)  {
               return 0;
          }
    return totalRows ;

Upvotes: 2

BalusC
BalusC

Reputation: 1109272

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!
} else if (entities.size() == 1) {
    // It has only one row!
} else {
    // It has more than one row!
}

Upvotes: 2

Andreas Dolk
Andreas Dolk

Reputation: 114807

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.

Upvotes: 1

kasperjj
kasperjj

Reputation: 3664

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;

Upvotes: 3

Uri
Uri

Reputation: 89809

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.

Upvotes: 1

Related Questions