How to Implement Cross-database Queries Conveniently
SQL cannot be directly applied to implement queries involving multiple subdatabases; SQL functions between different databases are not consistent; Java hardcoding is hard to write; it’s hard to optimize SQL containing a database link if we use the transparent internet gateway. Those are the existing problems in writing cross-subdatabase queries.
There are two usual methods for handling such a query:
Method 1: Java hardcoding
To perform count between databases, part of the Java code is written in this way:
… public Connection getConnection(String drivername,String url, String username, String password) { try { Class.forName(drivername); conn = DriverManager.getConnection(url, username, password); } catch (Exception e) { System.out.println("DBUtils.getConnection_FAILED!"); e.printStackTrace(); } return conn; } … … Connection conn1 = DBUtils.getConnection(URL1, USER1, PASSWORD1); Connection conn2 = DBUtils.getConnection(URL2, USER2, PASSWORD2); … String sql = "select count(*) c from sales";
PreparedStatement ptmt1 = conn1.prepareStatement(sql); PreparedStatement ptmt2 = conn1.prepareStatement(sql); … ResultSet rs1 = ptmt1.executeQuery(); ResultSet rs2 = ptmt2.executeQuery(); … return rs1.getInt("c") + rs2.getInt("c"); … |
Method 2: The use of transparent internet gateway
Take Oracle dblink as an example:
Optimizing a SQL query with the dblink is not easy. Usually we analyze a set of execution plans using collocated inline view and driving_site hint to do the optimization. But that’s a thorny issue even for an experienced DBA.
There would be no more headaches if we could use esProc to write the cross-subdatabase queries. Below is an example script:
The count operation involves multiple databases (n). The multi-subdatabase query requires a count in each subdatabase and then a sum of the count results. With esProc SPL, a 4-line is sufficient to get it done:
A |
B |
|
1 |
=n.(connect("mysql"+string(~))) |
//Connect to each of the subdatabases |
2 |
=SQL="select count(*) c from sales" |
//The SQL to be executed in each subdatabase |
3 |
=A1.(~.cursor(SQL)) |
//Execute SQL in the n subdatabases |
4 |
=A3.conjx().total(sum(c)) |
//Concatenate and sum the count results got by subdatabases |
esProc can translate a SQL query into appropriate syntax to execute by subdatabases, if they are of different structures.
esProc also enables high-performance code to achieve efficient algorithms according to the way data is arranged. No more hardcoding for sorting, getting top N, grouping, distinct and joins. Find more examples in Cross-database Queries.
An esProc SPL script can be easily embedded into a Java program. Read How to Call an SPL Script in Java to learn details.
Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.
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