Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to find the number of rows in the resultset

 
Raghavan Chockalingam
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried to find from the documentation how many rows are in a ResultSet object but could not find it...can anyone tell me what is the method to get the number of rows in a ResultSet?
 
Corey McGlone
Ranch Hand
Posts: 3271
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Raghavan Chockalingam:
I tried to find from the documentation how many rows are in a ResultSet object but could not find it...can anyone tell me what is the method to get the number of rows in a ResultSet?


Hmmm...not sure there's a good method to simply grab this information. If I've ever needed that information, I'd simply increment a counter every time I grabbed a row of information. Is there some reason you need to know the number of rows in the ResultSet prior to processing the data in the ResultSet?

Another option that I suspect would work (although I've never tried) would be this:

1. Invoke ResultSet.last() on the ResultSet.
2. Invoke ResultSet.getRow() to get the current row number (which is also the number of rows in the ResultSet)
3. Invoke ResultSet.beforeFirst() on the ResultSet.
4. Process as normal.

It would seem that this method would work, although it's certainly not very elegant. Of course, you may run into issues if your ResultSet is a FORWARD_ONLY ResultSet.

Perhaps someone has a better suggestion.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4008
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In general, there is no way without reading all the results or performing a separate query that selects the count(*) of the first query (only works if data is not frequently changing).

I believe there are some solutions for specific DBMS's and drivers, but nothing in the general sense.
 
Raghavan Chockalingam
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just for the sake of debugging...I wanted to know how many rows are returned by a particular ResultSet. Using ResultSetObject.getRow() when there is no rows, gives an exception...
 
Corey McGlone
Ranch Hand
Posts: 3271
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Raghavan Chockalingam:
Just for the sake of debugging...I wanted to know how many rows are returned by a particular ResultSet. Using ResultSetObject.getRow() when there is no rows, gives an exception...


If you're using ResultSet.last() to move to the end of the ResultSet, it will return a boolean value. It'll return true if the cursor now points to a valid record and false, otherwise. You can use that to check to see if there are 0 rows in the ResultSet. If ResultSet.last() returns true, go ahead and call getRow(). Otherwise, you already know that the ResultSet contains 0 rows.

However, if it's not essential to know how many rows exist prior to processing the ResultSet, I'd simply use a counter to count them up as you go along:

 
Ilja Preuss
author
Sheriff
Posts: 14112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For debugging purposes, you should take a look at http://www.p6spy.com/

Moving to our JDBC forum...
 
Adam Richards
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The previous replies have pretty well covered the techniques used to count rows. Since this is such a common problem, I wrote a small routine for my own database class that is easily reusable:
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is not a "free" operation. Most (all?) JDBC drivers use lazy load most (all?) of the time, meaning they fetch a buffer full of rows from the db to your Java memory at a time. last() will force it to fetch them all. Right?
 
Virginia Pasek
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ResultSet rs = runQuery("Select something from the database");

// Go to the last row
rs.last();
int numRows = rs.getRow();

// Reset row before iterating to get data
rs.beforeFirst();
 
Campbell Ritchie
Sheriff
Posts: 48424
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch

I presume you have seen the earlier comments, that such a count will have a severe performance overhead?
 
Virginia Pasek
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Campbell Ritchie wrote:Welcome to the Ranch

I presume you have seen the earlier comments, that such a count will have a severe performance overhead?


Hello Sheriff Campbell,

Thank you for the welcome. Please excuse my previous brevity. You are correct to note that the solution I posted above
is inherently wasteful as it can pull the entire table and then throw the data away. A more efficient solution
would be to run a count(*) query to get the row count. There are shortcomings in this method, however.

For instance say id=5555 in MyTable contains 4096 records, where id would be a foreign key. The query "select count(*)
from MyTable where id=5555 limit 3000" returns 4096, not 3000.

I suspect the best method for "How should I get the row count" may depend on the type of query being run and the size
of the dataset being investigated.

 
Campbell Ritchie
Sheriff
Posts: 48424
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Bernardo Guerrero
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found the same problem. I tried a diferent and simple solution:

I first load all my rows in a Queue, once i have them all in the queue i can know the number of rows from the Queue. Now i can use that data to construct whatever i need and then i proceed to dequeue all items and use them properly.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic