How to retrieve the ResultSet size

Question

I am looking to be able to tell how many records, i.e., db-table rows, a jdbc query returns without iterating through the resultset.

I tried ResultSet.getFetchSize()-- however didn't work the way I thought it would:

        if (rSet.getFetchSize()==0) 
        System.out.println("no results returned");
        while (rSet.next())
            System.out.println(rSet.getString("transactionID"));
        rSet.next();

The above code snippet prints the first console message, then gets into the loop and iterates to execute the second console message on 1+ results.

How to tell the number of records fetched? what's ResultSet.getFetchSize() for?

//-----------------------------------

EDIT: looking to tell this count without any iterations on the ResultSet. convinced that this isn't possible though. the answers to the referred Q are saying so.

`fetchSize` controls how many rows the driver requests from the server while iterating over the result. It has **nothing** to do with the actual number of rows that the query returns. — , Jul 11 '16 at 14:59
You should really try to move away from having the need to know the total row count of the result set, it is very expensive to get that; just process your rows as you move along. If you want to check if there is at least one row, call `rs.next()` and process the rest using a `do..while` loop instead of a `while` loop. — Mark Rotteveel, Jul 11 '16 at 15:08

score 2 · Answer 1 · edited May 23 '17 at 10:28

ResultSet.getFetchSize() suggests to the underlying database driver how many records should be fetched over the network at one time, but it doesn't have anything to do with the size of your SQL result set.

If you want to find out how large a result would be, you can try doing a COUNT query:

SELECT COUNT(*) FROM yourTable WHERE <conditions> ...

Further reading:

What does Statement.setFetchSize(nSize) method really do in SQL Server JDBC driver?

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

How to retrieve the ResultSet size

1 Answers1