This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes total number of rows in ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "total number of rows in ResultSet" Watch "total number of rows in ResultSet" New topic
Author

total number of rows in ResultSet

Nehul NN
Ranch Hand

Joined: Nov 05, 2003
Posts: 45
How to find total number of rows in ResultSet? Do we have any method which says you retrieve 100 rows after executing xyz sql statement!! or you do resultset.next() in while loop to count row number in that resultset...
Mathias Nilsson
Ranch Hand

Joined: Oct 13, 2003
Posts: 107
I'm sorry! There is no method to retrive the rows in a resultset. Only if
you have deleted,updated or inserted rows.
You can do something like this
Using SQL Query:

Using JDBC Scrollable ResultSet: Can be very time counsuming if the resultset is big

You can altso use a cached rowset.

// Hope this helps
[ November 08, 2003: Message edited by: Mathias Nilsson ]

SCJP2 , MCP( 70-229 ) , Preparing For SCWDC
Nehul NN
Ranch Hand

Joined: Nov 05, 2003
Posts: 45
Yeah I did those trick.. I am looking for this method since 1997 in JDBC API.. wonder what will be big problem...only reason I can think of they have to scroll to entire resultset before it is available to us, when you fire select * from table;
SCJP 1.4, SCWCD & Preparing for SCJD, SCBCD
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
I think that ResultSet is designed so that it's possible for an implementation to start returning rows as soon as it finds them, even though the query is still running on the server. So at the time you first start processing a ResultSet, the information about how many rows there will be total is not necessarily available - and if you asked for it, you would force the system to block until all the rows had been returned, which could be rather inefficient. Instead JDBC is set up so that if you really want the row count right away, you need to write a query that returns the row count, specifically.


"I'm not back." - Bill Harding, Twister
Nehul NN
Ranch Hand

Joined: Nov 05, 2003
Posts: 45
but don't you think by firing one more query i.e. select count(*) from table you are introducing more overhead or additional time for processing data from database. Instead of that resultset can setup variable which will set rowcount of resultset after retrieving entire resultset.
Hope I am not asking something new... :roll:
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30076
    
149

Nehul,
It definitely adds more overhead because you would basically be doing the query twice. I think the point people are trying to make is that the count(*) query is more efficient if you only need the number of rows. If you need any other data, you should stick with one query.


[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
Ram Kumar Subramaniam
Ranch Hand

Joined: Jan 17, 2003
Posts: 68
Originally posted by Jim Yingst:
I think that ResultSet is designed so that it's possible for an implementation to start returning rows as soon as it finds them, even though the query is still running on the server. So at the time you first start processing a ResultSet, the information about how many rows there will be total is not necessarily available - and if you asked for it, you would force the system to block until all the rows had been returned, which could be rather inefficient. Instead JDBC is set up so that if you really want the row count right away, you need to write a query that returns the row count, specifically.

But Jim it would depend on the vendor implementation of the same right ? Or is it that the latest spec enforces this implementation ?
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: total number of rows in ResultSet
 
Similar Threads
Fetchsize
ResultSet
Is it possible to get rowCount from ResultSet Object?
How do you know number of rows fetched by ResultSet?.
sum of rows