How to Execute Multi-database Queries via JDBC #eg76

Problem description & analysis

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

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

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:

Solution:

We write the following script p1.dfx in esProc:

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.

Open source SPL source address

Download