A Handy Method of Performing Cross-database Relational Operations in Java

Program development in Java involves cross-database relational operations. The following example will illustrate Java’s method of handling these operations. sales table is in db2 database, employee table is in mysql database. The task is to join sales with employee through sellerid of sales table and eid of employee table, and filter out the data in sales and employee that satisfy state=“California”.

The data and structure of sales table are as follows:

The data and structure of employee table are as follows:

Since the two tables come from different databases, SQL cannot be used to join them. But Rowset, Java's class library for data computing, can do this, because it has JoinRowSet and FilteredRowSet classes that can perform cross-database computing.

Java's way of programming is as follows：

1. Retrieve the data of sales table and employee table respectively from databasesdb2 and mysql, and store them in the object of CachedRowSet.

2. Use JoinRowSet to complete the inner join of the two tables.

3.   Use FilteredRowSet to complete the conditioned filtering.

4.   Print out the resulting data.

In the following code, two functions are used to import the data of db2 and mysql:

    public static RowSet db2() throws Exception {

String drive = "com.ibm.db2.jcc.DB2Driver";

String url = "jdbc:db2://127.0.0.1:50000/demo";

String DBUSER="db2admin";

String password="db2admin"

Connection conn = null;

Statement stmt= null;

ResultSet result = null;   

Class.forName(drive);

conn =DriverManager.getConnection(url,DBUSER,password);

stmt = conn.createStatement();

result1 =stmt.executeQuery("SELECT * FROM sales");

CachedRowSetcachedRS = new CachedRowSetImpl();

cachedRS.populate(result);

result.close();

stmt.close();

conn.close();

returncachedRS;

         }

         public static RowSetmysql() throws Exception {

                   String drive = "com.mysql.jdbc.Driver";

             String url = "jdbc:mysql://127.0.0.1:3306/test";

             String DBUSER="root";

             String password="root";

        Connection conn = null;

        Statement stmt= null;

ResultSet result1 = null;   

Class.forName(drive);

conn =DriverManager.getConnection(url,DBUSER,password);

stmt = conn.createStatement();

        result1 =stmt.executeQuery("SELECT * FROM employee");

CachedRowSetcachedRS = new CachedRowSetImpl();

cachedRS.populate(result1);

result1.close();

stmt.close();

conn.close();

returncachedRS;

         }

Then another two functions are used to join the two tables and filter the data.

         public static void myJoin() throws Exception {

         //retrieve data from the two databases

RowSetmysqlRS= mysql();

                   RowSet db2RS= db2();

                   // join the two tables

                   JoinRowSetjoinRS = new JoinRowSetImpl();

                   joinRS.addRowSet(db2RS, "SELLERID");

                   joinRS.addRowSet(mysqlRS,"EID");

                   // perform the conditioned filtering

                   FilteredRowSetfilterRS = new FilteredRowSetImpl();

                   filterRS.populate(joinRS);

                   StateRange range = new StateRange();//filtering condition; for the detailed filtering process, please see below.

                   filterRS.setFilter(range);

                   while(filterRS.next()){//print out the result

                            int  ORDERID =filterRS.getInt("ORDERID");

                            int  SELLERID =filterRS.getInt("SELLERID");

                            String NAME = filterRS.getString("NAME");

                  String STATE = filterRS.getString("STATE");

                  System.out.print("ORDERID="+ORDERID+";");

                  System.out.print("SELLERID="+SELLERID+";");

                  System.out.print("NAME="+NAME+";");

                  System.out.print("STATE="+STATE+";");

     }

         }

During the process, the object of StateRange needs to be created by itself, like the following inner class:

public static class StateRange implements Predicate {

                   publicStateRange(){}

                   publicboolean evaluate(RowSetrs) {

         try {

                   if (rs.getString("STATE").equals("California"))

         return true;//if “state” equals “California”, return true

                 } catch (SQLException e) {

                     // do nothing

                 }

                            return false;

                   }

                   publicboolean evaluate(Object value, int column) throws SQLException {

                            return false;

                   }

                   publicboolean evaluate(Object value, String columnName)

                                     throwsSQLException {

                            return false;

                   }

         }

The above code completed the task of cross-database relation operation between db2 and mysql and the filtering in them, but it is far from ideal. First, JoinRowSet supports only the inner joinand doesn't support outer join. Second, db2, mysql and hsql can use JoinRowSet after test, but the result set of joining oracle11g with another database is empty though no error will be reported. However, if oracle11g's users of two databases perform cross-database joining using JoinRowSet, they will get correct result. So the jdbc provided by different database providers could affect the result obtained by using this method. Third, the code is a little complicated.

The task can be made easier by introducing esProc to the operation. As a programming language specializing in processing structured (semi-structured) data, esProc can perform cross-database relational operations easily, and integrate with Java seamlessly, enabling Java to perform cross-database data computing as flexibly as SQL does. esProc supports various databases, including oracle, db2, mysql, sqlserver, sybase, postgre, and so on, all of which can perform cross-database relational operations like inner join and outer join.

To fulfill the above task, 8 lines of code in esProc are enough, which you can see below:

A1: Connect to db2 data source configured in advance.

A2: Connect to mysql data source configured in advance. In fact other databases like oracle can also be used here.

A3, A4: Retrieve table sequences salesand employee from db2 and mysql respectively. esProc's IDE can display the retrieved data visually, as shown in the right part of the above figure.

A5: Join table sequences sales and employee through sellerid=eid using esProc's object reference mechanism.

A6: Filter the two table sequences according to state="California".

A7: Generate a new table sequence and get the required field.

A8: Return the field to the caller that called the esProc program.

Lastly, call this piece of esProc code in Java to get the result using jdbc provided by esProc. Java needs to call the following code to save the above esProc code as test.dfx file:

// create a connection between esProc and jdbc

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

// call esProc program (the stored procedure), test is the name of file dfx

com.esproc.jdbc.InternalCStatementst;

st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test()");

// execute the esProc stored procedure

st.execute();

// get the result set

ResultSet set = st.getResultSet();