How do I get the size of a java.sql.ResultSet?
Get the ResultSet size by using last() and getRow() for scrollable type of ResultSet. Here is the quick code for you:
Note: This approach works best with small datasets to avoid any performance impacts. For large datasets or TYPE_FORWARD_ONLY, consider dealing with the data without knowing the size initially, or run a count query first to estimate the size without fetching the actual data.
Understanding the cursor-like behaviour of ResultSet
ResultSet acts alike a cursor over the returned data from a SQL query, rather than an array or collection object. So, there are no direct methods such as size() or length() to fetch the size of a ResultSet. Size has to be calculated.
Savvy programming: smart practices for performance
Databases are crucial to applications, and doing a heavy operation like pushing the cursor to the end of a large ResultSet may slow things down. So, keep your database happy and only use the last() and getRow() methods on a ResultSet for small data sets.
When to avoid size detection: performance trade-off
Sometimes, knowing the size of ResultSet will not add much value, but instead will consume extra resources and time. In cases when processing of the fetched rows is the primary concern and size of the ResultSet is not affecting this operation, it's better to avoid the size detection.
Count query: a potent alternative
A powerful alternative to finding the size from ResultSet is to make another SQL query using SELECT COUNT(*). Running this query returns the count directly from your SQL database. It's a valuable tool when you need to know the count upfront, for instance for pagination or resource allocation purposes without the overhead of fetching all data.
Professional tips: navigating ResultSet
Beware if you're considering using resultSet.first() after resultSet.last(). The cursor might get mischievous and not point to the actual first entry following these operations due to cursor movements. Always prefer resultSet.beforeFirst() to prevent this as it reliably resets the cursor position.
Post size calculation: handling resultset post size calculation
Upon calculating the size, capture it in a variable, say rowcount, for recurrent usage. Having the count in hand aids in optimising memory use, simulating more effective data handling strategies, and developing intuitive UI components.
Was this article helpful?