如何在 Java 中使用 ResultSet 获取行数? [英] How to get row count using ResultSet in Java?

问题描述

我正在尝试创建一个简单的方法，该方法接收一个 ResultSet 作为参数并返回一个包含 ResultSet 行计数的 int.这是一种有效的方法吗?

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("--------------------------------------------------------");
    }

我试过了:

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

但我收到一条错误消息，提示 com.microsoft.sqlserver.jdbc.SQLServerException: 请求的操作在仅转发结果集上不受支持.

But I got an error saying com.microsoft.sqlserver.jdbc.SQLServerException: The requested operation is not supported on forward only result sets.

在此先感谢您的指点！

推荐答案

如果您有权访问导致此结果集的准备好的语句，您可以使用

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);

这会以一种您可以倒回光标的方式准备您的语句.这也记录在 ResultSet Javadoc

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

但是，一般来说，对于大型结果集，前向和后退游标可能效率很低.SQL Server 中的另一个选项是直接在 SQL 语句中计算总行数:

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 ...

这篇关于如何在 Java 中使用 ResultSet 获取行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！