如何获取JDBC中的行数？

I've executed a JDBC query to obtain a resultset. Before iterating over it, I'd like to quickly find out how many rows were returned. How can I do this with high performance?

我已经执行了JDBC查询以获取结果集。在迭代之前，我想快速找出返回的行数。我怎样才能以高性能做到这一点？

I'm using Java 6, Oracle 11g, and the latest Oracle JDBC drivers.

我正在使用Java 6，Oracle 11g和最新的Oracle JDBC驱动程序。

7 个解决方案

#1

You're going to have to do this as a separate query, for example:

您将不得不将此作为单独的查询执行，例如：

SELECT COUNT(1) FROM table_name

Some JDBC drivers might tell you but this is optional behaviour and, more to the point, the driver may not know yet. This can be due to how the query is optimised eg two example execution strategies in Oracle are to get all rows as quickly as possible or to get the first row as quickly as possible.

一些JDBC驱动程序可能会告诉您，但这是可选行为，更重要的是，驱动程序可能还不知道。这可能是由于查询的优化方式，例如Oracle中的两个示例执行策略是尽可能快地获取所有行或尽快获得第一行。

If you do two separate queries (one a count and the other the query) then you'll need to do them within the same transaction. This will work well on Oracle but can be problematic on other databases (eg SQL Server will either show you uncommitted data or block on an external uncommitted update depending on your isolation level whereas Oracle supports an isolation level that gives you a transactionally consistent view of the data without blocking on external updates).

如果您执行两个单独的查询（一个是计数而另一个是查询），那么您需要在同一个事务中执行它们。这在Oracle上运行良好但在其他数据库上可能会出现问题（例如，SQL Server将显示未提交的数据或阻止外部未提交的更新，具体取决于您的隔离级别，而Oracle支持隔离级别，为您提供事务一致的视图。数据没有阻止外部更新）。

Normally though it doesn't really matter how many rows there are. Typically this sort of query is either batch processed or paged and either way you have progress information in the form of rows loaded/processed and you can detect the end of the result set (obviously).

通常情况下，有多少行并不重要。通常，这种查询是批处理或分页的，无论哪种方式，您都可以以加载/处理的行的形式获得进度信息，并且可以检测结果集的结尾（显然）。

#2

Short answer: you can't.

简答：你不能。

Long answer: you can't, partly because the database may be lazily evaluating the query, only returning rows as you ask for them.

答案很长：你不能，部分是因为数据库可能懒惰地评估查询，只是在你要求时返回行。

EDIT: Using a scrollable ResultSet you can :)

编辑：使用可滚动的ResultSet你可以:)

Indeed, I asked this very question in the Java databases newsgroup a long time ago (back in 2001!) and had some helpful responses.

实际上，我很早以前就已经在Java数据库新闻组中提出了这个问题（早在2001年！）并且有一些有用的回答。

#3

ResultSet rs = stmt.executeQuery(sql);
int rowCount = rs.last() ? rs.getRow() : 0; // Number of rows in result set. Don't forget to set cyrsor to beforeFirst() row! :)

#4

To get the number of rows from JDBC:

要从JDBC获取行数：

ResultSet rs = st.executeQuery("select count(*) from TABLE_NAME");
rs.next();
int count = rs.getInt(1);

#5

If your driver supports it(!), you can call ResultSet.afterLast() ResultSet.getRow() ResultSet.beforeFirst(). Performance may or may not be good.

如果您的驱动程序支持它（！），则可以调用ResultSet.afterLast（）ResultSet.getRow（）ResultSet.beforeFirst（）。表现可能好也可能不好。

A better solution would be to rewrite your algorithm not to require the size up front.

更好的解决方案是重写算法，而不是预先要求大小。

#6

Without ternary operator

没有三元运算符

rs.last();  // Moves the cursor to the last row in this ResultSet object.
int rowCount = rs.getRow();  //Retrieves the current row number.
rs.beforeFirst(); //Moves the cursor to the front of this ResultSet object,just before the first row.

With ternary operator one line

使用三元运算符一行

int rowCount = rs.last() ? rs.getRow() : 0; 
rs.beforeFirst();

#7

-1

Code:

码：

//Create a Statement class to execute the SQL statement
Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS COUNT FROM
TABLENAME");

 while(rs.next()) {
    System.out.println("The count is " + rs.getInt("COUNT"));
 }

 //Closing the connection
 con.close();

#1