How to Determine the Size of a java.sql.ResultSet in Java?

Question

How can I find out the size of a java.sql.ResultSet in Java?

// Example of working with ResultSet in Java
import java.sql.*;

public class ResultSetSizeExample {
  public static void main(String[] args) throws SQLException {
    Connection connection = DriverManager.getConnection("jdbc:your_database", "user", "password");
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT * FROM your_table");

    // Count the number of rows
    int rowCount = 0;
    while (resultSet.next()) {
      rowCount++;
    }
    resultSet.beforeFirst(); // Reset cursor to the beginning

    System.out.println("Row count: " + rowCount);
    
    resultSet.close();
    statement.close();
    connection.close();
  }
}

Answer

In Java, the `java.sql.ResultSet` does not provide a direct method to retrieve its size, such as `size()` or `length()`. Instead, measuring the size of a ResultSet typically involves iterating through its contents to count the number of rows it contains. Here’s how to do this effectively:

// Example of counting rows using scrolling ResultSet
import java.sql.*;

public class ResultSetRowCountExample {
  public static void main(String[] args) throws SQLException {
    Connection connection = DriverManager.getConnection("jdbc:your_database", "user", "password");
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet resultSet = statement.executeQuery("SELECT * FROM your_table");

    if (resultSet.last()) { // Move cursor to the last row
      int rowCount = resultSet.getRow(); // Get current row index
      System.out.println("Row count: " + rowCount);
    }

    resultSet.close();
    statement.close();
    connection.close();
  }
}

Causes

Lack of direct methods in ResultSet for size calculation.
ResultSet is designed to retrieve data incrementally, not all at once.

Solutions

Iterate through the ResultSet to count rows
Use `resultSet.last()` and `resultSet.getRow()` to get the row count (if the result is scrollable)
Store query results into a collection that can be sized easily

Common Mistakes

Mistake: Forgetting to set the ResultSet type to TYPE_SCROLL_INSENSITIVE.

Solution: Ensure to create the Statement with appropriate ResultSet type to allow scrolling.

Mistake: Assuming getRow() always works with non-scrollable ResultSets.

Solution: Note that getRow() only gives valid results for scrollable ResultSets.

Helpers

java.sql.ResultSet size
how to count ResultSet rows in Java
Java JDBC ResultSet row count