aspose file tools*
The moose likes JDBC and the fly likes getting recordCount from ResultSet object ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "getting recordCount from ResultSet object ?" Watch "getting recordCount from ResultSet object ?" New topic
Author

getting recordCount from ResultSet object ?

James Hetfield
Greenhorn

Joined: Jul 10, 2003
Posts: 7
hi,
Am wondering if there is a better way to get the recordCount from the ResultSet object instead of using the "select count(*) from <tablename>" query or by using a while loop...??
Is there any method available to get the record count..??
Thanx,
adios,
LearningCurve


Knowledge is power.<br />James.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

As far as I am aware, there are 3 common solutions to counting the number of resiults from a query
  • make the query. as you iterate through the ResultSet, increament a counter.
  • make the query. if your driver supprts this functionality, jump to the last row and use getRow to find the number of the last row.
  • execute a count() query against the same query.


  • (From the Java site: 18. There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set?
    I'm happy for someone to correct me as long as they aren't too rude about it, but I tend to favour the last option. It requires 2 database operations, but isn't too bad as long as the query isn't inefficient.
    and now that we've covered your question:
    "learning curve",Welcome to the Ranch!
    We're light on rules, but one rule that we take seriously is the naming convention. You should take the time to see it here.
    We require that names aren't obviously fictitious, and strongly encourage people to use their real names. Pleaseedit your profile and update your display name to be a valid one. Accounts with invalid display names get deleted.
    thanks,
    Dave.
    Jeanne Boyarsky
    author & internet detective
    Marshal

    Joined: May 26, 2003
    Posts: 31069
        
    232

    While this is mostly a matter of personal preference, I prefer using #1 or #3 depending on the scenario. If I would be making the query anyway, I would use #1. If I'm not making the query anyway, I would use #3. I try to avoid doing the same query twice. I do agree with David that the overhead isn't so bad (because the data should be in memory from the previous query), but in some cases the extra overhead could add up.


    [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
    Nagendra Prasad
    Ranch Hand

    Joined: Jul 11, 2002
    Posts: 219
    You could also argue with regards to where the processing takes place.
    #1 and #2 tend to use a lot on the app server. #3 delegates this to the db server. One could also prefer going this route to decide which option is best suited.
    I vote for #3. Its clean and most rdbms's have some optimized method already to obtain a count of a table. Why not utilize that!


    Best Regards,<br />Nagendra Prasad.
    James Hetfield
    Greenhorn

    Joined: Jul 10, 2003
    Posts: 7
    thanx all...for u'r views...and info...
    Even I feel based on the context...either #1 or #3 will be of appropriate use.
    ...and dave...as per u'r advice...here i am LearningCurve...following..naming conventions... ...I am very happy to be here...with u all at ranch.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: getting recordCount from ResultSet object ?