How to Perform Cross-database Queries via JDBC
Problem description & analysis
We have a STATEINFO table in the Oracle database. Below is part of the data:
| STATEID |
POPULATION |
AREA |
| 1 |
4779736 |
52419 |
| 2 |
710231 |
663267 |
| 3 |
6392017 |
113998 |
| 4 |
2915918 |
52897 |
| 5 |
37253956 |
163700 |
| … |
… |
… |
We also have a STATENAME table in the MySQL database. Below is part of the data:
| STATEID |
NAME |
ABBR |
| 1 |
Alabama |
AL |
| 2 |
Alaska |
AK |
| 3 |
Arizona |
AZ |
| 4 |
Arkansas |
AR |
| 5 |
California |
CA |
| … |
… |
… |
The two tables are logically associated through STATEID. We are trying to perform a cross-database query via JDBC. Below is part of the desired result:
| STATEID |
POPULATION |
AREA |
NAME |
ABBR |
| 1 |
4779736 |
52419 |
Alabama |
AL |
| 2 |
710231 |
663267 |
Alaska |
AK |
| 3 |
6392017 |
113998 |
Arizona |
AZ |
| 4 |
2915918 |
52897 |
Arkansas |
AR |
| 5 |
37253956 |
163700 |
California |
CA |
| … |
… |
… |
… |
… |
Solution
Write the following script p1.dfx in esProc:
Method 1: SPL
| A |
|
| 1 |
=connect("oracle") |
| 2 |
=A1.query@x("SELECT * FROM STATEINFO") |
| 3 |
=connect("mysql") |
| 4 |
=A3.query@x("SELECT * FROM STATENAME") |
| 5 |
=join(A2:SI,STATEID;A4:SN,STATEID) |
| 6 |
=A5.new(SI.STATEID,SI.POPULATION,SI.AREA,SN.NAME,SN.ABBR) |
Explanation:
A1 Connect to the database named oracle.
A2 Perform SQL, return result as a table sequence, and then close database connection.
A3 Connect to the database named mysql.
A4 Perform SQL, return result as a table sequence, and then close database connection.
A5 Join A2’s table sequence and A4’s through associative field STATEID.
A6 Generate the desired result table sequence.
Method 2: Simple SQL
| A |
|
| 1 |
=connect("mysql").cursor@x("SELECT * FROM STATENAME") |
| 2 |
=connect("oracle").cursor@x("SELECT * FROM STATEINFO") |
| 3 |
$select n.STATEID as STATEID,n.POPULATION as POPULATION,n.AREA as AREA,m.NAME as NAME,m.ABBR as ABBR from {A1} m join {A2} n on m.STATEID=n.STATEID |
Explanation:
A1 Connect to the database named mysql, return a database cursor created from sql, and auto-close database connection when the cursor is closed.
A2 Connect to the database named oracle, return a database cursor created from sql, and auto-close database connection when the cursor is closed.
A3 Join A1’s table and A2’s table using simple SQL.
Read How to Call an SPL Script in Java to learn about the method of integrating the SPL script into Java.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
