...

How to Implement Cross-database Queries Conveniently

 
multiple datasources(9)

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.


multiple datasources(9)
 
 •  410 View  •