How to return the row count of a query using JDBC ResultSet/RowSet

Date: 27-Jun-2003

Objective

After reading this document, you should be able to:

• Return the row count of a query using JDBC ResultSet or RowSet.
  
• Run the sample code to do the same using the instructions provided.

Software Requirements

• JDK1.3.x or above. This can be downloaded from here.
• Oracle9i Database Release 2 or later. This can be downloaded from here.
• Oracle9i JDBC driver 9.2.x.x downloadable from here.
  or
  Oracle Database Client Installation downloadable from here.
• Oracle CachedRowSet, downloadable from here. Download the ocrs12.zip found along with the downloads for JDBC driver class for JDK version used.
  
  Note: Oracle9i JDBC driver classes are available with Oracle Database client installation.

Description

Using JDBC(including Oracle JDBC extensions), there is no direct way i.e. no standard method available to find the number of rows returned by a query using ResultSet or RowSet. But this can be achieved with few lines of code using a Scrollable ResultSet or a Cached RowSet. Following lists the details of different ways that can be used.

• One approach is to execute a "SELECT COUNT(*)..." before the actual query.
  This means the database engine has to parse the same data twice (once for the count, once for the data itself).
  
  
• The second approach is to use the JDBC 2.0 way:
• one using Scrollable ResultSet and
• the other using Cached RowSet and plain(non-scrollable) ResultSet combination.

The JDBC methods will allow us to get the row count of a query without scanning all the rows or issue a separate SELECT COUNT(*). Moving to the end of the Scrollable ResultSet/Cached RowSet and getting its position(resultset.last()/cachedRowset.last() and resultset.getRow()/cachedRowset.getRow()) will do the job required. The RowSet extends the ResultSet interface and so we can use a plain ResultSet(not a scrollable one).

Caveats of using Scrollable ResultSet:

• If the ResultSet is very large, resultset.last() may be a very time-consuming operation, since it will use more resources on the server side. So, unless you really need a scrollable ResultSet, refrain from using this approach.
• Oracle JDBC driver will return the correct count using resultset.getRow(). But there could be other vendor implementations which can return zero for resultset.getRow().
  

Code Snippet:

Using SQL Query:

................ // Get a record count with the SQL Statement Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS rowcount FROM emp"); rs.next(); // Get the rowcount column value. int ResultCount = rs.getInt(rowcount) ; rs.close() ; ...............

Using JDBC Scrollable ResultSet:

.............. sqlString = "SELECT * FROM emp"; // Create a scrollable ResultSet. stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = stmt.executeQuery(sqlString); // Point to the last row in resultset. rs.last(); // Get the row position which is also the number of rows in the ResultSet. int rowcount = rs.getRow(); System.out.println("Total rows for the query: "+rowcount); // Reposition at the beginning of the ResultSet to take up rs.next() call. rs.beforeFirst(); ................

Using Oracle JDBC Cached RowSet

.........................
ResultSet rs = null;
........................

// Create and initialize Cached RowSet object.
OracleCachedRowSet ocrs = new OracleCachedRowSet();     
      
// Create a string that has the SQL statement that gets all the records.
String sqlString = "SELECT empno FROM emp";


// Create a statement, resultset objects.
stmt = conn.createStatement();
rs = stmt.executeQuery(sqlString);

// Populate the Cached RowSet using the above Resultset.
ocrs.populate(rs);
      
// Point to the last row in Cached RowSet.
ocrs.last();      

// Get the row position which is also the number of rows in the Cached
// RowSet.
int rowcount = ocrs.getRow();

System.out.println("Total rows for the query using Cached RowSet: "+
                   rowcount);          
    
// Close the Cached Rowset object.

if (ocrs != null) 
  ocrs.close();
.............