1. Overview
1.概述
In this article, we’ll look at the different ways we can count the number of rows of a JDBC ResultSet.
在这篇文章中，我们将探讨计算JDBCResultSet的行数的不同方法。
2. Counting the ResultSet Rows
2.计算ResultSet的行数
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.
计算ResultSet的行数并不简单，因为没有API方法提供这一信息。其原因是，JDBC查询不会立即获取所有的结果。每次我们使用ResultSet.next方法请求行结果时，都会从数据库中加载这些结果。
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.
当我们执行一个JDBC查询时，我们不能事先知道会有多少结果。相反，我们需要浏览所有的结果，只有当我们到达终点时，我们才能确定可用的行数。
There’re two ways we can do this, either using a standard or a scrollable ResultSet.
我们有两种方法可以做到这一点，可以使用标准的或可滚动的ResultSet。
3. Standard ResultSet
3.标准的结果集
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:
让我们创建一个StandardRowCounter类，为数据库连接提供一个单一参数。
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.
我们的类将包含一个方法，该方法将把SQL查询作为String，并通过迭代ResultSet返回行数，为每个结果递增一个计数器变量。
Let’s name our counter method getQueryRowCount:
让我们给我们的计数器方法命名为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.
注意，我们使用一个try-with-resources块来自动关闭JDBC资源。
To test our implementation we’ll take advantage of an in-memory database to quickly generate a table with 3 entries.
为了测试我们的实现，我们将利用内存数据库来快速生成一个有3个条目的表。
With that in hand, let’s create a RowCounterApp with a simple main method:
有了这些，让我们创建一个RowCounterApp，其中有一个简单的main方法。
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.
上述方法可以在任何数据库中工作。然而，如果数据库驱动程序支持，有一些更高级的API，我们可以用来实现同样的结果。
4. Scrollable ResultSet
4.可滚动的结果集
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.
通过使用重载的Statement方法createStatement，我们可以要求在查询执行后创建一个可滚动的ResultSet。有了可滚动的版本，我们可以使用更高级的遍历方法，如previous来向后移动。在我们的例子中，我们将使用last方法移动到ResultSet的末尾，并获得最后一条的行号，该行号由getRow方法给出。
Let’s create a ScrollableRowCounter class:
让我们创建一个ScrollableRowCounter类。
class ScrollableRowCounter {
Connection conn;
ScrollableRowCounter(Connection conn) {
this.conn = conn;
}
}Like our StandardRowCounter, the only field we’ll use is for the Database Connection.
像我们的StandardRowCounter一样，我们唯一要使用的字段是数据库Connection。
Again, we’ll use a getQueryRowCount method:
同样，我们将使用一个getQueryRowCount方法。
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.
为了得到一个可滚动的ResultSet，我们必须向createStatement方法提供ResultSet.TYPE_SCROLL_INSENSITIVE常量。此外，我们必须为并发模式提供一个值，但由于它与我们的案例无关，我们使用默认的ResultSet.CONCUR_READ_ONLY常量。如果JDBC驱动程序不支持这种操作模式，它将抛出一个异常。
Let’s test our new implementation with the RowCountApp:
让我们用RowCountApp来测试我们的新实现。
ScrollableRowCounter scrollableCounter = new ScrollableRowCounter(conn);
assert scrollableCounter.getQueryRowCount(selectQuery) == 3;5. Performance Considerations
5.性能方面的考虑
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.
虽然上面的实现很简单，但由于必须对ResultSet进行遍历，所以它们的性能并不是最好的。出于这个原因，通常建议使用COUNT类型的查询来进行行计数操作。
A simple example is:
一个简单的例子是。
SELECT COUNT(*) FROM STORAGE
SELECT COUNT（*） FROM STORAGE
This returns a single row with a single column that contains the number of rows in the STORAGE table.
这将返回一条单列的记录，其中包含STORAGE表中的行数。
6. Conclusion
6.结语
In this article, we had a look at the different ways we can get the number of rows in a ResultSet.
在这篇文章中，我们看了获得ResultSet中的行数的不同方法。
As always, the source code for this article is available over on GitHub.
一如既往，本文的源代码可在GitHub上获得over。