• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

selecting cross - database servers in java


hi all

can anyone show me a way around my problem?

i have 2 database servers, postgresql and mysql

i need to do a select query on the postgresql database and use the recordset set returned to do another select on a mysql database

something like this

select id from postgresqltable inner join mysql table

is this possibel.  i able to connect using jdbc to both database but i don't know how to use the select result from the postgresql table and join them with the mysql table

any help greatly appreciated
0
dalecon
Asked:
dalecon
3 Solutions
 
CEHJCommented:
Just use two queries and join them 'manually'
0
 
daleconAuthor Commented:
if i use this


// go off to postgresql
conn = Connection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, rsPS.TYPE_SCROLL_INSENSITIVE, rsPS.CONCUR_READ_ONLY);
rsPS = ps.executeQuery();

// go off to mysql
conn = ConnectionMySQL.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, rsMS.TYPE_SCROLL_INSENSITIVE, rsMS.CONCUR_READ_ONLY);
rsMS = ps.executeQuery();


How do 'join' the 2 recordset?
0
 
CEHJCommented:
Get the key field from the first and use it to lookup in the 2nd recordset
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
objectsCommented:
loop thru the first result set, and for each record make a select on the other database by the the foreign key

conn = Connection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, rsPS.TYPE_SCROLL_INSENSITIVE, rsPS.CONCUR_READ_ONLY);
rsPS = ps.executeQuery();
while (rsPS.next()) {

   Object key = rsPS.getObject(...);
   String sql = ...

   // go off to mysql
   conn = ConnectionMySQL.getConnection();
   PreparedStatement ps = conn.prepareStatement(sql, rsMS.TYPE_SCROLL_INSENSITIVE, rsMS.CONCUR_READ_ONLY);
   rsMS = ps.executeQuery();

   // loop thru rsMS
}
0
 
daleconAuthor Commented:
no the query is too complex it's full of left outer, inner and union joins. with a massive where clause using an old version of mysql so i can do any sub queries

i also need the results as a recordset and send as xml using jdom

i'll have to copy the data in a sql job from one server to the other and do it that way

0
 
Tomas Helgi JohannssonCommented:
   Hi!

You should use this method as CEHJ says : http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/JoinRowSet.html
Example (taken from the above link) :

    JoinRowSet jrs = new JoinRowSetImpl();
 
     ResultSet rs1 = stmt.executeQuery("SELECT * FROM EMPLOYEES");
     CachedRowSet empl = new CachedRowSetImpl();
     empl.populate(rs1);
     empl.setMatchColumn(1);
     jrs.addRowSet(empl);
 
     ResultSet rs2 = stmt.executeQuery("SELECT * FROM ESSP_BONUS_PLAN");
     CachedRowSet bonus = new CachedRowSetImpl();
     bonus.populate(rs2);
     bonus.setMatchColumn(1); // EMP_ID is the first column
     jrs.addRowSet(bonus);

Regards,
  Tomas Helgi
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now