File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to find the number of rows in the resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to find the number of rows in the resultset" Watch "How to find the number of rows in the resultset" New topic
Author

How to find the number of rows in the resultset

Raghavan Chockalingam
Ranch Hand

Joined: Dec 20, 2005
Posts: 77
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?


Raghavan
SCJP 6
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
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.


SCJP Tipline, etc.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Raghavan Chockalingam
Ranch Hand

Joined: Dec 20, 2005
Posts: 77
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

Joined: Dec 20, 2001
Posts: 3271
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

Joined: Jul 11, 2001
Posts: 14112
For debugging purposes, you should take a look at http://www.p6spy.com/

Moving to our JDBC forum...


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Adam Richards
Ranch Hand

Joined: Nov 03, 2005
Posts: 135
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

Joined: Jan 29, 2003
Posts: 8791
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?


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Virginia Pasek
Greenhorn

Joined: Jun 07, 2012
Posts: 3
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

Joined: Oct 13, 2005
Posts: 40052
    
  28
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

Joined: Jun 07, 2012
Posts: 3
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

Joined: Oct 13, 2005
Posts: 40052
    
  28
Bernardo Guerrero
Greenhorn

Joined: Nov 27, 2013
Posts: 1
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to find the number of rows in the resultset