获取Java resultset中的行数

Does anyone know a better way of getting the number of rows in a Java resultset returned from a MySQL database? The resultset returned is not going to be the total number of rows read from the database so I don't think I can use SQL's COUNT aggregate function.

有没有人知道更好的方法来获取从MySQL数据库返回的Java resultset中的行数?返回的resultset不会是从数据库读取的行总数，因此我不认为我可以使用SQL的COUNT聚合函数。

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

7 个解决方案

#1

A better answer is to forget about the number of rows until you've successfully loaded the ResultSet into an object or collection. You can keep the count of the number of rows with either of those options.

更好的答案是，在成功地将ResultSet加载到对象或集合之前，先忘掉行数。您可以使用其中任何一个选项来保持行数的计数。

It's important to close ResultSets (and all SQL resources like Connection and Statement) in the narrowest method scope possible. That means not passing ResultSet out of the persistence layer. Better to get the number of rows using a Collection size() call.

在尽可能窄的方法范围内关闭结果集(以及所有SQL资源，如连接和语句)是很重要的。这意味着不从持久性层中传递ResultSet。最好使用集合size()调用获取行数。

Stop thinking about databases and start thinking in terms of objects. Java's an object-oriented language.

停止思考数据库，开始从对象的角度思考。Java是一个面向对象的语言。

#2

You can execute

你可以执行

SELECT FOUND_ROWS()

immediately after executing your SELECT statement to find the row count.

在执行SELECT语句之后立即查找行计数。

#3

You can always use SELECT COUNT() with the same exact conditions, before making the actual SELECT.

在进行实际的选择之前，您总是可以使用具有相同条件的SELECT COUNT()。

#4

Here is my solution to this question (since I mostly want the number of records returned to build an array or something similar): Use a collection such as Vector<T> instead.

这里是我对这个问题的解决方案(因为我主要想要返回的记录的数量来构建一个数组或类似的东西):使用一个集合，比如Vector 。

public Vector<T> getRecords(){
   Vector<T> records = new Vector<T>();
   init_conn_and_stmt_and_rs();

   try {
      rs = stmt.executeQuery("SELECT * FROM `table` WHERE `something` = 0");
      while(rs.next()){
         // Load the Vector here.
      }
   } catch (SQLException e) {
      e.printStackTrace();
   }finally{
      close_rs_and_stmt_and_conn();
   }
   return records;
}

Clean and simple, no? Works for any size record set returned (no need to know the size before hand) and makes all the List methods available.

干净,简单,不是吗?适用于返回的任何大小记录集(不需要事先知道大小)，并使所有列表方法可用。

This has served me well for a time now, but if someone sees a flaw in this, please let me know. Always want to make my practices better, ya know.

这对我来说已经有一段时间了，但是如果有人发现其中的缺陷，请告诉我。你知道，我总是想把我的做法做得更好。

#5

If you are using Java 6 you can use the JDBC4ResultSet class which has the getUpdateCount method that returns the number of the lines affected by a SQL Statement even for a Select Statement.

如果您正在使用Java 6，您可以使用JDBC4ResultSet类，该类具有getUpdateCount方法，该方法返回受SQL语句影响的行数，甚至对于Select语句也是如此。

See below the example code:

参见下面的示例代码:

PreparedStatement ps = con.prepareStatement("select * from any_table ...");
JDBC4ResultSet rs = (JDBC4ResultSet)ps.executeQuery();
int rowNumber = rs.getUpdateCount();

I hope that this help!

我希望这能有所帮助!

#6

You can also use the following way to get the total records in the resultSet:

您还可以使用以下方法获取resultSet中的总记录:

statement = connect.createStatement();
resultSet = statement.executeQuery(sqlStatement);
resultSet.last();
int count = resultSet.getRow();
System.out.println(count);

count is the total returned rows for your result set. ;)

count是您的结果集返回的总数。

#7

See below the example code with the solution of Lalith :

参见下面的示例代码，使用Lalith的解决方案:

public static int getResultSetRowCount(connection) {
    int size = 0; 
    statement = connection.createStatement(); 
    try {
        resultSet = statement.executeQuery("SELECT FOUND_ROWS()")
        resultSet.next()
        size = resultSet.getInt(1)
    }
    catch(Exception ex) {
        return 0;
    }
    statement.close();
    return size;
}

#1