1. Overview
In this article, we’ll look at the different ways we can count the number of rows of a JDBC ResultSet.
2. Counting the ResultSet Rows
Counting the rows of a ResultSet is not straightforward since no API method provides this information. The reason for this is that a JDBC query does not fetch all of the results immediately. The row results are loaded from the database every time we request them using the ResultSet.next method.
When we execute a JDBC query, we cannot know how many results we’ll have beforehand. Instead, we need to go through all of them, and only when we reach the end can we be sure of the number of available rows.
There’re two ways we can do this, either using a standard or a scrollable ResultSet.
3. Standard ResultSet
The most straightforward way of counting our query results is to iterate through all of them and increment a counter variable for each result.
Let’s create a StandardRowCounter class with a single parameter for a database connection:
class StandardRowCounter {
Connection conn;
StandardRowCounter(Connection conn) {
this.conn = conn;
}
}
Our class will contain a single method that’ll take an SQL query as a String and will return the row count by iterating through the ResultSet, incrementing a counter variable for each result.
Let’s name our counter method getQueryRowCount:
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;
}
}
Note that we use a try-with-resources block to automatically close JDBC resources.
To test our implementation we’ll take advantage of an in-memory database to quickly generate a table with 3 entries.
With that in hand, let’s create a RowCounterApp with a simple main method:
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 will work in any database. However, if the database driver supports it, there are some more advanced APIs that we can use to achieve the same result.
By using the overloaded Statement method createStatement, we can ask for a scrollable ResultSet to be created after query execution. With the scrollable version, we can use more advanced traversal methods like previous to move backward. In our case, we’ll move to the end of the ResultSet using the last method and get the row number of the last entry, which is given by the getRow method.
Let’s create a ScrollableRowCounter class:
class ScrollableRowCounter {
Connection conn;
ScrollableRowCounter(Connection conn) {
this.conn = conn;
}
}
Like our StandardRowCounter, the only field we’ll use is for the Database Connection.
Again, we’ll use a getQueryRowCount method:
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 must provide the ResultSet.TYPE_SCROLL_INSENSITIVE constant to the createStatement method. Additionally, we must provide a value for the concurrency mode but since it’s irrelevant to our case we use the default ResultSet.CONCUR_READ_ONLY constant. In the case that the JDBC driver doesn’t support this mode of operation, it’ll throw an exception.
Let’s test our new implementation with the RowCountApp:
ScrollableRowCounter scrollableCounter = new ScrollableRowCounter(conn);
assert scrollableCounter.getQueryRowCount(selectQuery) == 3;
Although the above implementations are simple, they don’t have the best performance due to the mandatory traversal of the ResultSet. For this reason, it’s usually recommended to use a COUNT type query for row count operations.
A simple example is:
SELECT COUNT(*) FROM STORAGE
This returns a single row with a single column that contains the number of rows in the STORAGE table.
6. Conclusion
In this article, we had a look at the different ways we can get the number of rows in a ResultSet.
As always, the source code for this article is available over on GitHub.
