java nested while loop in result set

Question

I want to merge results from 2 resultSet which are results returned from sql queries. What is the best way to accomplish this?

My plan is lopping over 2 result sets to print out the final result from both result sets. Both results set have the same id column which are their first column. But for the inner while loop, I only get the first value printed out.

I thought when there is a match, it prints the value from the second result set, and break out of the loop. Then when there is another match, it prints out the value again.

while (set1.next())
{
    System.out.print("Id: "+set1.getInt(1)+", Name: "+set1.getString(2));

    while (set2.next())
    {
        if (set1.getInt(1)==(set2.getInt(1)))
        {  
            System.out.print(", Alias: "+set2.getString(2);
            break;
        }
    }

    System.out.println();
}

Edit:

ResultSet set1: 
id | name
1  | A
2  | B
3  | C
...

ResultSet set2:
id | alias
1  | F
2  | G
2  | H

I want to print out:

Id: 1, Name: A, Alias: F
Id: 2, Name: B, Alias: G, H

FYI, the id is in ascending orders in both sets

would you mind posting a little sample of the data and the queries? — Leo
– Leo, Commented Mar 7, 2014 at 1:05
set1 and set2 are resultset from sql queries. 'id' is int. 'name' and 'alias' are string — kaboom
– kaboom, Commented Mar 7, 2014 at 1:33

Raghvendra Singh · Accepted Answer · 2014-03-07 01:05:31Z

1

The below loop will only executed once for the outer loop

        while (set2.next()){
            if (set1.getInt(1)==(set2.getInt(1))){  
                System.out.print(", Alias: "+set2.getString(2);
                break;
            }
        }

You need to reinitialize your iterator each time the outer loop executes.

answered Mar 7, 2014 at 1:05

Raghvendra Singh

1,8054 gold badges27 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kaboom Over a year ago

sorry, can you elaborate on reinitialize the iterator? I am very new to java

Raghvendra Singh Over a year ago

You must have initialized set2 in your code right? something like Iterator<Integer> set2 = yourSet.iterator(). You have to do this before the inner while loop.

kaboom Over a year ago

i added Iterator<Integer> set2_itr = set2.iterator(). It said there is no iterator() method. I did import java.util.* though

tonga · Accepted Answer · 2014-03-07 02:31:31Z

0

Since the two result sets have the same first column (id), you can start from the first row for both result sets. In the inner loop, iterate over the rows in set2 until its id is great than the id in set1. So you can do:

while (set1.next())
{
    System.out.print("Id: "+set1.getInt(1)+", Name: "+set1.getString(2));

    while (set2.next())
    {
        System.out.print(", Alias:");
        if (set1.getInt(1)==(set2.getInt(1)))
        {  
            System.out.print(set2.getString(2) + ", ");
            continue;
        } else {
            set2.previous();
            break;
        }
    }
    System.out.println();
}

edited Mar 7, 2014 at 2:31

answered Mar 7, 2014 at 1:58

tonga

12k25 gold badges79 silver badges96 bronze badges

3 Comments

kaboom Over a year ago

that's a good idea. but i cannot do that becoz the id in set2 is not unique. so one 'Name' could have multiple 'Alias'

kaboom Over a year ago

thank you. it actually says the operation is not supported on forward only result set. Is there a better way to merge results from the 2 result sets? I think ppl often use UNION in sql query. But I cannot use UNION becoz the returned rows have different fields ('Name' and 'Alias')

tonga Over a year ago

How did you obtain the two ResultSet objects? ResultSet class allows you to move cursor back to one row using previous() method. What DB are you using? But if you want to use SQL directly, I think you can use the query: SELECT table1.id, table1.name, table2.alias FROM table1 INNER JOIN ON table1.id=table2.id ORDER BY table1.id. Not sure if this works but you can try.

Collectives™ on Stack Overflow

java nested while loop in result set

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related