...

How to Execute Multi-database Queries via JDBC

 
jdbc(3) cross-database query(2)

Problem description & analysis

We have two MySQL databases. There is an employee table in MySQL_A, whose data is as follows:

undefined

There is a family table in MySQL_B, whose data is as follows:

undefined

We need to use JDBC to do a cross-database join query, which is to get the relationship (RELATION) between each employee (ENAME) and their corresponding family member (FNAME) by joining the two tables through EID. Below is the expected result table:

undefined

Solution:

We write the following script p1.dfx in esProc:

A

1

=connect("MySQL_A")

2

=connect("MySQL_B")

3

=A1.query@x("select *   from employee")

4

=A2.query@x("select     * from family")

5

=join(A3:Employee,EID;A4:Familymembers,EID)

6

=A5.new(Employee.NAME:ENAME,Familymembers.NAME:FNAME,Familymembers.RELATION)

 

Code description:

A1   Connect to database MySQL_A.

A2  Connect to database MySQL_B.

A3  Read employee table from MySQL_A.

A4  Read family table from MySQL_B.

A5  Join the two tables up through EID.

A6  Get the relationship (RELATION) between each employee (ENAME) and their corresponding family member (FNAME).

See How to Call an SPL Script in Java to learn about the way of integrating the SPL code with a Java program.

Q & A Collection

https://stackoverflow.com/questions/21968834/jdbcis-it-possible-to-execute-multi-database-querys-in-java

 


jdbc(3) cross-database query(2)
 
 •  234 View  •