• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Record Count

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Bartender
Posts: 783
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
mister krabs
Posts: 13974
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
--------------------------------------------------------------------------------
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
 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic