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:
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:
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 write SELLERID.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();