esProc Helps Process Heterogeneous DataSources in Java – MongoDB

MongoDB does not support join directly.The unity JDBC recommended by the official website can perform the join operation after retrieving data out. But the advanced functions, like join, group, functions and expressions, are only provided by the paid version of unity JDBC. Even the paid version does not support the complicated SQL operations, such as subquery, window functions, etc. The other free JDBC drivers of MongoDB only support less SQL statements.

Using free esProc working with the basic query statement of unity JDBC (or other JDBC drivers) can realize a great many of complicated structured (semi-structured) computations. We’ll take join as an example to illustrate the method in detail.

As shown in the following, the file -orders- in MongoDB contains the sales orders, and employee contains the employee information:

MongoDB shell version: 2.6.4
connecting to: test
>db.orders.find();
{ “_id” : ObjectId(“5434f88dd00ab5276493e270″), “ORDERID” : 1, “CLIENT” : “UJRNP
“, “SELLERID” : 17, “AMOUNT” : 392, “ORDERDATE” : “2008/11/2 15:28″ }
{ “_id” : ObjectId(“5434f88dd00ab5276493e271″), “ORDERID” : 2, “CLIENT” : “SJCH”
, “SELLERID” : 6, “AMOUNT” : 4802, “ORDERDATE” : “2008/11/9 15:28″ }
{ “_id” : ObjectId(“5434f88dd00ab5276493e272″), “ORDERID” : 3, “CLIENT” : “UJRNP
“, “SELLERID” : 16, “AMOUNT” : 13500, “ORDERDATE” : “2008/11/5 15:28″ }
{ “_id” : ObjectId(“5434f88dd00ab5276493e273″), “ORDERID” : 4, “CLIENT” : “PWQ”,
“SELLERID” : 9, “AMOUNT” : 26100, “ORDERDATE” : “2008/11/8 15:28″ }

>db.employee.find();
{ “_id” : ObjectId(“5437413513bdf2a4048f3480″), “EID” : 1, “NAME” : “Rebecca”, ”
SURNAME” : “Moore”, “GENDER” : “F”, “STATE” : “California”, “BIRTHDAY” : “1974-1
1-20″, “HIREDATE” : “2005-03-11″, “DEPT” : “R&D”, “SALARY” : 7000 }
{ “_id” : ObjectId(“5437413513bdf2a4048f3481″), “EID” : 2, “NAME” : “Ashley”, “S
URNAME” : “Wilson”, “GENDER” : “F”, “STATE” : “New York”, “BIRTHDAY” : “1980-07-
19″, “HIREDATE” : “2008-03-16″, “DEPT” : “Finance”, “SALARY” : 11000 }
{ “_id” : ObjectId(“5437413513bdf2a4048f3482″), “EID” : 3, “NAME” : “Rachel”, “S
URNAME” : “Johnson”, “GENDER” : “F”, “STATE” : “New Mexico”, “BIRTHDAY” : “1970-
12-17″, “HIREDATE” : “2010-12-01″, “DEPT” : “Sales”, “SALARY” : 9000 }

SELLERID in orders corresponds to EID in employee. Query the information of sales orders that satisfies the criterion that the property of STATE of employee is California. The expression of querying condition can be passed to esProc program as a parameter, as shown below:

esProc_java_heterogeneous_mongodb_1

where is a string parameter. Its value is SELLERID.STATE=”California”. The jars of unity JDBC for MongoDB esProc needs for accessing MongoDB include:

MongoDB_unityjdbc.jar

mongo-java-driver-2.12.2.jar

The jars can be downloaded by the URL http://www.unityjdbc.com/mongojdbc/mongo_jdbc.php. The configuration for connecting to MongoDB is similar to that for connecting to other databases. Just configure its JDBC and detailed process is omitted here.

The code written in esProc is as follows:

esProc_java_heterogeneous_mongodb_2

 

A1: Connect to MongoDB database configured in advance.

A2: Import data from orders.

A3: Import data from employee.

A4: switch function is used to switch the values of field SELLERID in A2 (orders) to the references of the corresponding records in A3 (employee).

A5: Filter according to the criterion. Here macro is used to realize dynamically parsing the expression, in which where is the input parameter. esProc will compute the expression surrounded by ${…} first, take the result as a macro string value and replace ${…} with it, and then interpret and execute the code. The code ultimately executed is =A2.select(SELLERID.STATE=”California”). Since the values of SELLERID have been switched to the references of corresponding records in employee, you can writeSELLERID.STATEonly.

A6: Switch back the values of SELLERID in the filtering result to ordinary values.

A7: Return the eligible result set to Java program.

If the filtering condition is changed, you just need to modify the parameter where. For example, the condition is changed to “select the information of sales orders where STATE is California or that where CLIENT is PWQ”. The value of the parameter where can be written as CLIENT==”PWQ”|| SELLERID.STATE==”California”.

esProc JDBC is used to call this block of code in Java program and get the result (save the esProc block of code as test.dfx and configure the JDBC driver of unity JDBC for MongoDB). The code is as follows:

// create a connection using esProc JDBC

Class.forName(“com.esproc.jdbc.InternalDriver”);

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

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

com.esproc.jdbc.InternalCStatementst =(com.esproc.jdbc.InternalCStatement)con.prepareCall(“call test(?)”);

// set the parameters

st.setObject(1,”CLIENT==\”PWQ\”||SELLERID.STATE==e\”California\””);

// execute the esProc stored procedure

ResultSet set =st.executeQuery();

– See more at: http://blog.raqsoft.com/?p=1156#sthash.bYuO3ij5.dpuf

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in esProc/R/Python/Perl, Java Assistant, MongoDB. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s