CODEWITHSUNDEEP
javaoracle-databasejakarta-ee
Gary
Gary

Reputation: 4725

how to join two ResultSets from oracle queries in java

Is it possible to join two ResultSets like join two tables in Java-oracle programming? I am writing a java program which querys two different oracle databases(the databases are physically in different locations). In the program, sql1 returns ResultSet ret1;sql2 returns ResultSet ret2. Suppose ret1 has the following data:

Id   item
------------
1   item1
2   item 2
3   item 3
.........

Ret2 has the following data

Id  info
---------
1  info1
2  info2
…

I need result like this

Id item info
----------------------
1   item1 info1
2   item2  info2

Can I do something like this in java:

Select ret1.id,ret1.item,ret2,info from ret1, ret2 where ret1.id=ret2.id

I know I can loop from ret1 and get the id then get info from ret2; I am wondering if there is a fast way like join two tables to join two ResultSets without looping? Actually these two results both have half million rows. Thank you!

Upvotes: 0

Views: 2571

Answers (6)

Gary
Gary

Reputation: 4725

dblink is not allowed to create due to permission issue. I finally find another solution. Here is what I am doing: I get resultset ret1 then using: 

while (ret1.next()) { 
  get id from ret1, 
  query  info from another database;
  bla,bla,...
}
....

The performance is much better than getting two resultsets then looping one inside another...

Upvotes: 0

Ajaykarthi
Ajaykarthi

Reputation: 1

You need to use the DB Link for your requirement.

Or you can try using an materialized view.

Upvotes: 0

heretolearn
heretolearn

Reputation: 6545

you could try something like this:

 SELECT ret1.id,ret1.item,ret2.info
FROM ret1
RIGHT JOIN ret2 
ON ret1.id=ret2.id;

Sql Fiddle example

EDIT:

If the two ResultSets are from the same database then why not combine them during the retrieval by using union; e.g.

select A, B
from C
union
select A, B
from D

Upvotes: 1

Sumit
Sumit

Reputation: 1669

Is your intended output to some type of bean or other java data structure?
Assuming it is and you are using commons dbutils to fill a javabean.
I would create a list of beans of type ret1 using BeanListHandler, and then use a BeanMapHandler on ret2 to get a map of beans with the map key as the id from ret2.
Then as you are looping throught ret1, you can access the values in ret2 by the id of ret1.
Hope that makes sense.

Upvotes: 0

Anjan Biswas
Anjan Biswas

Reputation: 7912

The best you can do is loop through both the result sets ret1 and ret2 in Java and compare id in both of them and then store the result in a multi dimensional array, something like-

  while (ret1.next()) {
        int id1 = rs.getInt("ID"); 
        while (ret2.next()){
               int id2 = rs.getInt("ID");
               if ( id1 == id2 )
               ...  
               ...
        }
  }

And as others say, a better approach will be to use dblinks. And as Tom Kyte says-

the less code you write, the less bugs you create
the more code you write, the more bugs you create
write less code, suffer less bugs
write more code, suffer more bugs

Upvotes: 0

Carlos Cambón
Carlos Cambón

Reputation: 112

The cleanest and more effective solution is to have a dblink from one database to the other and do the join in the query. Otherwise you have to loop through both resultsets.

Upvotes: 0

Related Questions