Alex Rivera | Logout

How to get row count using ResultSet in Java?

Asked 2011-10-25T08:16:10.733
95

I'm trying to create a simple method that receives a ResultSet as a parameter and returns an int that contains the row count of the ResultSet. Is this a valid way of doing this or not so much?

int size = 0;
    try {
        while(rs.next()){
            size++;
        }
    }
    catch(Exception ex) {
        System.out.println("------------------Tablerize.getRowCount-----------------");
        System.out.println("Cannot get resultSet row count: " + ex);
        System.out.println("--------------------------------------------------------");
    }

I tried this:

int size = 0;
try {
    resultSet.last();
    size = resultSet.getRow();
    resultSet.beforeFirst();
}
catch(Exception ex) {
    return 0;
}
return size;

But I got an error saying

com.microsoft.sqlserver.jdbc.SQLServerException: 
The requested operation is not supported on forward only result sets.
java jdbc resultset
Edit
Report

2 Answers

75

If you have access to the prepared statement that results in this resultset, you can use

connection.prepareStatement(sql, 
  ResultSet.TYPE_SCROLL_INSENSITIVE, 
  ResultSet.CONCUR_READ_ONLY);

This prepares your statement in a way that you can rewind the cursor. This is also documented in the ResultSet Javadoc

In general, however, forwarding and rewinding cursors may be quite inefficient for large result sets. Another option in SQL Server would be to calculate the total number of rows directly in your SQL statement:

SELECT my_table.*, count(*) over () total_rows
FROM my_table
WHERE ...
answered 2011-10-25T08:20:38.510
2

Your function will return the size of a ResultSet, but its cursor will be set after last record, so without rewinding it by calling beforeFirst(), first() or previous() you won't be able to read its rows, and rewinding methods won't work with forward only ResultSet (you'll get the same exception you're getting in your second code fragment).

answered 2011-10-25T08:20:47.653

Your Answer