How to determine the number of Result set records.

[Scott Matthews]

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]

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.

      
 String sqlQuery = "select * from emp";
 Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
 Resultset rs = s.executeQuery(sqlQuery);
 rs.last();
 int totalCount = rs.getRow();
 System.out.println("total rows :" + totalCount);
 // put it where it was
 rs.beforeFirst();

[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]

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.

String sqlQuery = "select count(*) from emp"; 
Statement s = conn.createStatement(); 
Resultset rs = s.executeQuery(sqlQuery);
if (rs.next()) 
{
  int totalCount = 0;
  totalCount=rs.getInt();
  System.out.println("total rows :" + totalCount);
}
String sqlQuery = "select * from emp";
rs = s.executeQuery(sqlQuery); 
//process the results

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