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();
