How to Execute Multi-database Queries via JDBC
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:
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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL