File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Beginning Java and the fly likes Question on looping thru 2 recordsets Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Beginning Java
Bookmark "Question on looping thru 2 recordsets" Watch "Question on looping thru 2 recordsets" New topic

Question on looping thru 2 recordsets

Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 387

Sorry guys but I'm lost in the weeds here. Been at this one for a few hours and I keep getting deeper.

I have a recordset (rs) that has 41 records and another recordset (rs1) that has 3 records. I need to loop through the entire (rs) to see if rs1.itemNum in (rs1) matches any of the rs.itemNum in (rs) and if it does then I create a list and if it does not then I need to go on to the next (rs) record.

I just can't get out of the maze and any help or direction would be greatly appreciated. Thank you.

Here is my code:

Adam Nace
Ranch Hand

Joined: Jul 17, 2006
Posts: 117
Nothing's jumping out at me that looks obviously wrong. What behavior are you seeing?

- Adam
Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 387


Thanks for the reply. I think I just got my problem fixed.

Again I appreciate your time in replying.

Jim Yingst

Joined: Jan 30, 2000
Posts: 18671
Is a RecordSet like a ResultSet? Generally ResultSets are designed to be looped through exactly once, not multiple times. You can try calling rs.first() to reset the ResultSet to the first result; this will error if the ResultSet does not support this.Probably a better method would be to read the contents of rs1 just once, before doing anything with the other rs. Store all the data you need from rs1 in come sort of Collection, e.g. a HashSet. Then you can loop through the other rs and access what you need from rs1 using the Collection, not the original ResultSet.

"I'm not back." - Bill Harding, Twister
Krithika Srinath
Ranch Hand

Joined: Apr 11, 2006
Posts: 52
To achieve what you are trying to, you should first have two way scrollable ResultSet for the rs1 ResultSet. By default, forward only scrollable resultset is created. If you are using JDBC2.0 driver then you can create two way scrollable ResultSet by using the following createStatement API

Your code might be changed to use while instead of for(...) - this is just a suggestion since you dont need to introduce a new variable for just scrolling the resultset.

now at this point if the data match happens for last record in rs1, then you are at the end of the resultset and you need to once again set the pointer of rs1 to before 1st record.

Your code at [1] should be:

For more details on this please check beforeFirst() API

Sun's doc on ResultSet gives a good overview on how to create and manage different types of ResultSet.

Hope this helps.
Krithika Srinath
Ranch Hand

Joined: Apr 11, 2006
Posts: 52

I was typing so slowly that before i could complete you could fix your problem.

Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 387

Hey guys,

Thanks for all the replies. I did find that I still had an error but your suggestions are helping get through the recordset issue.

Unfortunately we're working with a squirrelly jdbc driver called J204 for the M204 database (legacy system). It is not a relational DB and consequently a lot of quirky stuff to workaround.

My lead is working on a fix on a sql tool we're using to be able to advance the recordset or reset it if the criteria is not found and then I can advance to the next record in the recordset without having to create a hashset.

Thanks again for all the help. Regards.
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
If you can sort the lists, matching is easy.

A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
I agree. Here's the link:
subject: Question on looping thru 2 recordsets
It's not a secret anymore!