This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases 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

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "getting recordCount from ResultSet object ?" Watch "getting recordCount from ResultSet object ?" New topic

getting recordCount from ResultSet object ?

James Hetfield

Joined: Jul 10, 2003
Posts: 7
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..??

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

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.
    Jeanne Boyarsky
    author & internet detective

    Joined: May 26, 2003
    Posts: 33132

    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.

    [OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, 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

    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 per u'r i am LearningCurve...following..naming conventions... ...I am very happy to be here...with u all at ranch.
    I agree. Here's the link:
    subject: getting recordCount from ResultSet object ?
    It's not a secret anymore!