wood burning stoves 2.0*
The moose likes JDBC and the fly likes Looping through result set Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Looping through result set" Watch "Looping through result set" New topic
Author

Looping through result set

Jennifer Sohl
Ranch Hand

Joined: Feb 28, 2001
Posts: 455
Hello. I have a result set that has a city, state, and zip in it. If there is more than one row in the resultset, I need to compare the city and zip values in the second row, to the city, zip values in the first row. How do I accomplish this in a while(rs.next()) loop? I've tried setting a variable but the next time through, the values are changed for that variable. How do I save these values? Is there an easier, way of doing this?
Maybe I'm making it harder than it really is?
Thanks for any help in advance!!
Dave Vick
Ranch Hand

Joined: May 10, 2001
Posts: 3244
Jennifer
Just to make sure I understand: Youneed to process the resultset but ignore rows with the same address?
something like this should work for you (this isn't tested but you should get the general idea):

Is that close to what you need or did I misunderstand the whole thing


Dave
Jennifer Sohl
Ranch Hand

Joined: Feb 28, 2001
Posts: 455
Hi Dave. This is my method to retrive the zip code information:

Basically what it does is checks to see if there is more than one record in the result set(rs.last()). If not, then it returns the string for the city and state to the textfields on the screen. If there is more than one row in the result set, it brings up a JDialog box(SelectCity)with all the cities listed for that zip code.
After reading your code I can kind of see where you're going, however, I don't really understand it. Why do I need to compare the result set String to a variable containing a blank String? I think I want to compare the result set to itself. You know, like compare Row 1 to Row 2.
Does this make sense, or am I confusing you as much as I'm confusing myself??
Thanks for your help!!
Michael Mathewson
Greenhorn

Joined: Feb 22, 2002
Posts: 7
Hi. Here's something i wrote which is kind of similar, except mine needed a counter when it encountered same names. All irrelevant code has been taken out. Hope this helps.


[ Edited by Dave to format code ]
[ April 16, 2002: Message edited by: David O'Meara ]
Michael Mathewson
Greenhorn

Joined: Feb 22, 2002
Posts: 7
Hi again. Sorry bout the way that came out, I cut n pasted it so there's no tabbing. I'll know for next time.
Dave Vick
Ranch Hand

Joined: May 10, 2001
Posts: 3244
Jennifer
OK, I misunderstood what you were doing from your first post:
Two questions:
1 - are all of the citys and zips in the result set unique? Or do you have to only list each one once if there are multiple citys and zips the same?
2 - When you create your JDialogBox you ar passing it the result set, so I assume it creates itself and lists all of the citys and zips in it and that part is ok right?
Assuming I'm right so far then you need to just be able to tell if there is one or more than one record returned right?
In your test for isLast change it to this:
if(rs.isLast() && count == 1)
Because you increment count to 1 as soon as you enter the loop if it is pointing at the last record and count is only 1 (you are in the first run through the loop) then there must be only one record.

If you've have to exclude duplicate citys and zips then you'll need to do something like what I gavey ou in my first post. The reason you compare the first record to the empty string is becasue everytiome you get a new city or zip you want to do something so by setting the current city and zip to empty strings the first record is guaranteed to be considered a new city and/or zip.
I hope this is more what you were looking for. If not let me know what I'm missing.
Just thought of this: If you need to get only distinct citys and zips, then you can always use a group by clause in your SQL statement to group all of the result by city and zip. Something like this:
String s1 = ("SELECT ZIP,CITY,STATE FROM ZIP WHERE ZIP ='" + s + "' GROUP BY city, zip");
[ April 16, 2002: Message edited by: Dave Vick ]
Jennifer Sohl
Ranch Hand

Joined: Feb 28, 2001
Posts: 455
Thanks for the help guys!
Dave, after reading the last part of your post on doing a 'Group By' you sparked something in my head. I tried doing a 'SELECT Distinct' on city state and zip, and this is returning exactly what I want. (I received an error message doing a GROUP BY) This was a lot easier than trying to get around this in my code.
Thanks again for you help!!
You guys should all get a raise!
Dave Vick
Ranch Hand

Joined: May 10, 2001
Posts: 3244
I forgot all about DISTINCT - glad it is working for you now.
Originally posted by Jennifer Sohl:
You guys should all get a raise!

OK, put on your boots....
All the bartenders and sheriffs are all volunteers and we dont get paid. We do it for the love of helping our fellow humanity. And for the warm, fuzzy feeling we get in furthering the Java language.
All bull aside...
Actually the best part of this is that almost every question I answer I learn something from too. Even if it's just to reinforce something I already knew!! I originally started studying for the SCJP just by reading but found out answering makes you learn so much more!!!
I also heard this was a great way to pick up women - of course it hasn't worked yet
What are you doing Saturday? - just kidding
Dave
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Looping through result set