How to find the number of rows in the resultset

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?

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.

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.

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...

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:

For debugging purposes, you should take a look at http://www.p6spy.com/

Moving to our JDBC forum...

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:

//----------------------------------------------------------------------------------------------
/**
 * Determines the number of rows in a <code>ResultSet</code>. Upon exit, if the cursor was not
 * currently on a row, it is just before the first row in the result set (a call to
 * {@link ResultSet#next()} will go to the first row).
 * @param set The <code>ResultSet</code> to check (must be scrollable).
 * @return The number of rows.
 * @throws SQLException If the <code>ResultSet</code> is not scrollable.
 * @see #hasSingleRow(ResultSet)
 */
public static int getRowCount(ResultSet set) throws SQLException
{
   int rowCount;
   int currentRow = set.getRow();            // Get current row
   rowCount = set.last() ? set.getRow() : 0; // Determine number of rows
   if (currentRow == 0)                      // If there was no current row
      set.beforeFirst();                     // We want next() to go to first row
   else                                      // If there WAS a current row
      set.absolute(currentRow);              // Restore it
   return rowCount;
}

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?

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();

Welcome to the Ranch

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

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.