• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to find the number of rows in the resultset

 
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 3271
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:

 
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
For debugging purposes, you should take a look at http://www.p6spy.com/

Moving to our JDBC forum...
 
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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:
 
(instanceof Sidekick)
Posts: 8791
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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();
 
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic