Get the Number of Rows in a ResultSet

1. Overview

Understanding how to determine the number of rows in a JDBC ResultSet is useful in scenarios where the query output size affects application logic or resource handling. JDBC doesn’t provide a direct API for this purpose because it fetches rows lazily as the ResultSet advances.

In this tutorial, we examine the ways to determine the size of a ResultSet, including approaches based on standard and scrollable cursors.

2. Counting the ResultSet Rows

Counting the rows of a ResultSet is often not straightforward because the JDBC API doesn’t expose a direct method for retrieving the row count. It loads the row results from the database every time we request them using the ResultSet.next() method.

When a query executes, the driver doesn’t tell us the number of rows we get in advance. Instead, the application must traverse the result set until the end is reached to determine how many rows are available.

There are two common ways to do this: using either a standard ResultSet or a scrollable ResultSet.

3. Standard ResultSet

A straightforward way to count the results of a query is to iterate through all of them and increment a counter variable for each row.

Let’s write a class with a definition of a simple counter that receives a database connection:

class StandardRowCounter {
    Connection conn;

    StandardRowCounter(Connection conn) {
        this.conn = conn;
    }
}

The class contains a single method that takes an SQL query as a String and returns the row count by iterating through the ResultSet and incrementing a counter for each row.

Now, we can write the method that demonstrates we can get the row count:

int getQueryRowCount(String query) throws SQLException {
    try (Statement statement = conn.createStatement();
        ResultSet standardRS = statement.executeQuery(query)) {
        int size = 0;
        while (standardRS.next()) {
            size++;
        }
        return size;
    }
}

A try-with-resources block ensures proper usage of that JDBC resources.

To verify the implementation, an in-memory database can be used to create a table with three entries. The following example shows a simple runner:

class RowCounterApp {

    public static void main(String[] args) throws SQLException {
        Connection conn = createDummyDB();

        String selectQuery = "SELECT * FROM STORAGE";

        StandardRowCounter standardCounter = new StandardRowCounter(conn);
        assert standardCounter.getQueryRowCount(selectQuery) == 3;
    }

    static Connection createDummyDB() throws SQLException {
        ...
    }

}

The above method works with any database. When the database driver supports scrollable result sets, additional APIs provide alternative ways to obtain the row count.

4. Scrollable ResultSet

Some JDBC drivers support scrollable result sets. Using the overloaded createStatement() method, a scrollable ResultSet can be requested. Scrollable cursors enable navigation in multiple directions, including moving directly to the last row. In this case, the row number of the last entry, retrieved through getRow(), represents the total row count.

Below is the ScrollableRowCounter class:

class ScrollableRowCounter {
    Connection conn;

    ScrollableRowCounter(Connection conn) {
        this.conn = conn;
    }
}

Similar to the StandardRowCounter, the class holds a single field for the database connection.

The method getQueryRowCount() retrieves the row count:

int getQueryRowCount(String query) throws SQLException {
    try (Statement statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet scrollableRS = statement.executeQuery(query)) {
        scrollableRS.last();
        return scrollableRS.getRow();
    }
}

To get a scrollable ResultSet, we provide the ResultSet.TYPE_SCROLL_INSENSITIVE constant to the createStatement method. A concurrency mode is also required, and CONCUR_READ_ONLY is appropriate in this case. If the JDBC driver doesn’t support scrollable result sets, it throws an exception.

Let’s test this approach with the RowCounterApp example:

ScrollableRowCounter scrollableCounter = new ScrollableRowCounter(conn);
assert scrollableCounter.getQueryRowCount(selectQuery) == 3;

A scrollable result set offers a convenient way to determine the number of rows without iterating over the entire result set, provided the database driver supports this capability.

5. Performance and Considerations

Although the previous implementations are simple, they don’t offer the best performance because they require traversing the entire ResultSet. For large datasets, this approach increases processing time and memory usage due to row-by-row iteration.

For most use cases, a COUNT query provides a more efficient way to obtain a row count:

SELECT COUNT(*) FROM STORAGE

This returns a single row with a single column that contains the number of rows in the STORAGE table. Database engines optimize COUNT(*) internally, and it’s functionally identical to COUNT(1) for this purpose. Both forms produce the same result, and modern relational databases treat them equivalently during query planning.

In addition, ResultSet.getFetchSize() doesn’t return the number of rows. Instead, it provides a hint to the JDBC driver about how many rows to fetch from the database at once. Its value depends on driver behavior and doesn’t reflect the row count of the result set.

When performance is important, a direct COUNT query is usually the preferred approach.

6. Conclusion

In this article, we explored ways to get the number of rows in a ResultSet and considerations when doing so.

Row counts can be determined through iteration, scrollable cursors, or direct COUNT queries. The first two methods work universally, while a COUNT query is generally more efficient and better suited for performance-sensitive workloads.