Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to determine the number of Result set records.

 
Scott Matthews
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using MySql if that matters in answering my question.
I have written my first program using MySql and can display information from an SQL query.
Question: What is the best way to determine the number of records returned by the result set.
.
.
ResultSet RS = Stmt.executeQuery(theSQLQuery);
while (RS.next()) {
System.out.println(RS.getString(1));
}
I know I can put my own "counter" in the while loop. Is there something like RS.size(). I looked in the online Java docs I have and did not see anything that was apparent.
Thanks.
 
sanjay yermalkar
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Although there is no direct method to find the no. of rows, it's possible by using Scrollable Resultset (supported in JDBC2).
I don't know if it works with MySql though. Here is some code I tried with oracle.


[This message has been edited by sanjay yermalkar (edited November 02, 2001).]
[This message has been edited by sanjay yermalkar (edited November 02, 2001).]
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Although sanjay's procedure will give you the count you need, it probably isn't as effecient as you may think:
1. It uses a scrollable resultset which by design are slower than non-scrollable
2. From what I've read, the getLast() method just loops through the recordset to the last record anyways...and then rs.first()/rs.beforeFirst() loops back to the beginning.
In my experience I have found that using the "select count(*) from emp where..." before I execute "select empno, name...from emp where..." is more efficient especially for larger resultsets.

I did some testing using both methods and my results were:
- Using "select count (*)" as described above - 200 to 300 milliseconds to execute in 10 trials
- Using the getRow() technique as described by sanjay - 500 to 600 milliseconds to execute in 10 trials
I just thought I would test this because I have never seen test results comparing the 2 techniques. Anyways, you can run your own tests if you like,

Jamie
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic