Effective Methods for Retrieving Row Count Using ResultSet in Java

Core Challenges in ResultSet Row Count Retrieval

In Java database programming, ResultSet serves as the primary container for query results, where row count retrieval presents common technical challenges. Many developers initially attempt the combination of resultSet.last() and resultSet.getRow(), but this approach throws "The requested operation is not supported on forward only result sets" exception on default TYPE_FORWARD_ONLY result sets.

Cursor Type Configuration Solution

The most effective solution involves explicitly specifying cursor type when creating PreparedStatement:

Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(
    sql, 
    ResultSet.TYPE_SCROLL_INSENSITIVE, 
    ResultSet.CONCUR_READ_ONLY
);
ResultSet resultSet = statement.executeQuery();

By setting ResultSet.TYPE_SCROLL_INSENSITIVE, the result set supports bidirectional cursor movement, making last() and beforeFirst() methods available. The complete row count retrieval method implementation is as follows:

public int getRowCount(ResultSet resultSet) {
    int size = 0;
    try {
        resultSet.last();
        size = resultSet.getRow();
        resultSet.beforeFirst();
    } catch (SQLException ex) {
        System.err.println("Cannot retrieve result set row count: " + ex.getMessage());
        return 0;
    }
    return size;
}

Performance Optimization and Alternative Approaches

While the cursor scrolling solution offers complete functionality, it may present performance issues with large result sets. A more efficient alternative involves calculating row count directly in SQL queries:

String countSql = "SELECT COUNT(*) AS total_count FROM table_name WHERE conditions";
PreparedStatement countStatement = connection.prepareStatement(countSql);
ResultSet countResult = countStatement.executeQuery();
countResult.next();
int totalRows = countResult.getInt("total_count");

For databases like SQL Server, window functions can also be utilized:

SELECT column1, column2, COUNT(*) OVER () AS total_rows 
FROM table_name 
WHERE conditions

Exception Handling and Best Practices

In practical applications, various exception scenarios must be considered. When cursor scrolling is unavailable, iterative counting serves as a reliable fallback:

public int getRowCountByIteration(ResultSet resultSet) {
    int size = 0;
    try {
        while (resultSet.next()) {
            size++;
        }
        // Reset cursor to initial position
        if (resultSet instanceof ScrollableResultSet) {
            ((ScrollableResultSet) resultSet).beforeFirst();
        }
    } catch (SQLException ex) {
        System.err.println("Iterative counting failed: " + ex.getMessage());
    }
    return size;
}

Although this method is less efficient, it works reliably across all types of ResultSet implementations.

Technical Selection Recommendations

When choosing a row count retrieval approach, consider the following factors: data volume, performance requirements, database compatibility, and application context. For small to medium result sets, cursor scrolling provides optimal functional completeness; for large datasets or performance-sensitive scenarios, SQL-level count queries are more appropriate.