Getting the count of rows in a 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.

public stati开发者_运维问答c int getResultSetRowCount(ResultSet resultSet) {
    int size = 0;
    try {
        resultSet.last();
        size = resultSet.getRow();
        resultSet.beforeFirst();
    }
    catch(Exception ex) {
        return 0;
    }
    return size;
}

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.

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.

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

You can execute

SELECT FOUND_ROWS()

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

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.

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!

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

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.

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.

You can also use the following way to get the total records in the 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. ;)