• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Looping through result set

 
Jennifer Sohl
Ranch Hand
Posts: 455
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Jennifer Sohl
Ranch Hand
Posts: 455
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
Eclipse IDE Firefox Browser Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
Eclipse IDE Firefox Browser Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 455
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3244
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic