Meaningless Drivel is fun!
The moose likes JDBC and Relational Databases and the fly likes Record Count Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Record Count" Watch "Record Count" New topic

Record Count

Anand Joshi

Joined: Dec 06, 2000
Posts: 12
Is there any way to count the number of records in Record Set?
Currently I am reading all the records one by one and counting them, which is very time consuming process.
I have an option of using Java Tool box for AS/400. Will it help me in this regard?

<A HREF="" rel="nofollow"></A>
Peter Tran

Joined: Jan 02, 2001
Posts: 783
If your table is index correctly, you can execute one SQL to get the record count, before executing the second SQL to fetch the records.
Assuming you want to execute something like SELECT City FROM TblCountry where City = "FOO" then you can do the following.
1st get record count using "SELECT COUNT(*) FROM TblCountry WHERE City = "FOO".
Once you know the record count, then you can execute the original SQL statement to get the city.
Caveat: Make sure you have an index on the column in your where clause, or you'll end up doing a table scan which is slow!!!
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
Since the ResultSet does not contain any rows but only contains the methods needed to get rows from he database, there is no way to query the ResultSet for a row count. If you need a row count, then follow Peter's advice.

Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
Kevin Mukhar
Ranch Hand

Joined: Nov 28, 2000
Posts: 83
If you are using a JDBC 2.0 compliant driver, there is one other way to get the row count:
int numRows = resultSet.getRow();
Note that depending on your database and driver, that the above may not be a good solution. For instance, the Oracle driver caches the rows in memory as you traverse the result set. If your result set is very large, then this could exhaust memory. If you don't care about making two accesses to the db, then the 'select count(*)...' technique is safer; but if you know your result set will be relatively small, and you only want to make on db access, then use getRow().
Anand Joshi

Joined: Dec 06, 2000
Posts: 12
Thanks a lot friends.
Currently I am using SELECT COUNT(*)... method to count the records.
Now I got the new tip i.e. checking the lat row of the resultset.
I will try that method and will check which onw is optimum.
Once again Thanks a lot for your reply.

Joined: Jun 25, 2001
Posts: 10
If you know that there will be only 1 or 0 rows then you can just check to see if the resultset returned any results: returns true if there are and records and false if none were found.
if (
//1 row returned ( means at least one row
//returned from the database)
//0 rows returned
hope that helps,
Tal, Rosenberg

Joined: Aug 02, 2001
Posts: 2
I'm strugling withe same issue. We are usiing Oracle as the DB server and the type of queries we use will not allow us to submit the query twice.
Does anyone know how Oracle stores the result count for the queries? (I know it does)
jenkinstechnology llc

Joined: Aug 18, 2001
Posts: 7
be careful using two database dips to get the row count. a row can be inserted between the two calls, making your count inaccurate. you must wrap the two calls in a transaction to prevent this from occuring..
Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Even setting up a transaction doesn't guarantee anything unless you pick the highest transaction isolation level - very expensive in terms of database resources.
If your driver supports JDBC 2.0 scrollable result sets, you could go to last(), get the record number, go back go first() and read your results. I don't have the faintest idea how expensive this is.
- Peter
subject: Record Count
It's not a secret anymore!