CODEWITHSUNDEEP
javajdbcwhile-loopresultset
kaboom
kaboom

Reputation: 833

java nested while loop in result set

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

Upvotes: 1

Views: 5612

Answers (2)

tonga
tonga

Reputation: 11961

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();
}

Upvotes: 0

Raghvendra Singh
Raghvendra Singh

Reputation: 1805

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.

Upvotes: 1

Related Questions