aspose file tools*
The moose likes JDBC and the fly likes Record Count Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Record Count" Watch "Record Count" New topic
Author

Record Count

Anand Joshi
Greenhorn

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="mailto:anandjoshi@india.com" rel="nofollow">anandjoshi@india.com</A>
Peter Tran
Bartender

Joined: Jan 02, 2001
Posts: 783
Anand,
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!!!
-Peter
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:
resultSet.last();
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
Greenhorn

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.
jlrober
Greenhorn

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:
resultset.next() returns true if there are and records and false if none were found.
if (resultset.next())
{
//1 row returned (really...it means at least one row
//returned from the database)
}
else
{
//0 rows returned
}
hope that helps,
Jamie
Tal, Rosenberg
Greenhorn

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)
Thanks
jenkinstechnology llc
Greenhorn

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..
paul
Peter den Haan
author
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Record Count