Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

# Rows in a resultset

 
Joe Nichols
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Posts: 34837
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe,
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.
 
Andy Nimmo
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Andy.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Marshal
Posts: 34837
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 116
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic