File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes # Rows in a resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "# Rows in a resultset" Watch "# Rows in a resultset" New topic

# Rows in a resultset

Joe Nichols

Joined: Jul 15, 2004
Posts: 1
Is there any way to find the number or rows in a resultset without looping through it? and if that is the only way is there a way to get back to the start? If I am creating an array of Strings based on the number of returned rows, I need to know how big to make the array, but if I have to llop through it then I am at the end of the rs... Or should I use something other than a resultset to get my data? Thanks
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33129

You have to loop through the result set to get the number of rows. (You could do a separate query to get the count, but that is inefficient.)

Some drivers support rs.first() to reset the resultset, but I'm not sure if this is universal.

You could store the data in a dynamic data structure, like an ArrayList, and then convert in to an array later.

[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
Andy Nimmo

Joined: Mar 30, 2003
Posts: 14
Alternatively Joe, and assuming your JDBC driver supports this you could use the following code snippet:

It's not a very nice way of doing it but it's one of the few ways you can do what you're asking. I've given up trying to find a nicer way of doing this.

David O'Meara

Joined: Mar 06, 2001
Posts: 13459

a third alternative (and possibly not the best, but you'd have to check) is to perform the query twice, the first time as a count(*) operation.
I agree that using a List implementation then converting to an Array (if required) may be your best bet.
Stefan Wagner
Ranch Hand

Joined: Jun 02, 2003
Posts: 1923

Using 'select count (*)' is a good solution if the number of results is too big to put it into an array/ list/ whatever.
The drawback is, that you could get a result, which is out of date, when you send the second query.
To display a progressbar, it should be sufficient.
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33129

I wouldn't use the select count (*) option in this case because Joe was planning on storing all the data anyway (in his array.) It would add a server roundtrip. By storing/converting the results, it is only an optimized java operation.
Jeff Walker
Ranch Hand

Joined: Apr 25, 2004
Posts: 116
I would try this:

rs.last(); // set the resultset to the last row
int lastRow = rs.getRow(); // get row count of last row
rs.first() // go back to the first row for proecessing

Won't lastRow (or lastRow - 1) above, give you the number of rows?
Again, as a previous poster said, this maybe only possible in JDBC3.0, and could throw an exception if rs.last() fails (because your result could be empty). You'll need to pretty it up a little with a try-catch block to pass muster.

Also, there are performance considerations, it will take time to run thru the entire resultset for the rs.last() to work, depending on how the resultset is buffered, but then again, looping thru all rows will take time too!

Jeff Walker
I agree. Here's the link:
subject: # Rows in a resultset
It's not a secret anymore!