How do I get the row count in JDBC?

java jdbc resultset
Steve McLeod picture Steve McLeod · Oct 2, 2008 · Viewed 86.7k times · Source

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?

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

Answer

cletus picture cletus · Oct 2, 2008

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.

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).

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).

Related questions

How do I get the size of a java.sql.ResultSet?

Shouldn't this be a pretty straightforward operation? However, I see there's neither a size() nor length() method.

Read More
How to get row count using ResultSet in Java?

I'm trying to create a simple method that receives a ResultSet as a parameter and returns an int that contains the row count of the ResultSet. Is this a valid way of doing this or not so much? int size = 0; …

Read More
Iterating over ResultSet and adding its value in an ArrayList

I am iterating over an ResultSet and trying to copy its values in an ArrayList. The problem is that its traversing only once. But using resultset.getString("Col 1") to resultset.getString('Col n") is showing all entries of all columns. …

Read More