The collector assists Java in processing the MongoDB of a diverse data source

MongoDB does not support joins, its official web-recommended Unity JDBC can take data out for two calculations to implement join operations, but these join, group, function, expression, and other advanced features are paid version only, and even if the charge version, sub-query, Complex SQL operations such as window functions are still not supported. Other free JDBC Drive can only support the most basic SQL statements.

With the free Esproc with MongoDB, the above structured (semi-structured) complex calculations can be achieved. Here is an example of join to illustrate the specific approach.

The document orders in MongoDB Save the order data, and the employee saves employees ' data. As follows:

MongoDB Shell version:2.6.4
Connecting To:test
> Db.orders.find ();
{"_id": ObjectId ("5434f88dd00ab5276493e270″"), "ORDERID": 1, "CLIENT": "Ujrnp
"," Sellerid ": +," 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 ": +," 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/R "," 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}
...

The Sellerid in orders corresponds to the Eid in the employee. The State property of the employee needs to be queried for all order information equal to California. A query condition expression can be passed as a parameter to a esproc, such as:

Esproc does not contain MongoDB's Java driver package. To access MongoDB with Esproc, the MongoDB Java driver package (Esproc requires 2.12.2 or more versions of driver, Mongo-java-driver-2.12.2.jar) must be placed in advance to the [Esproc installation directory]\ The COMMON\JDBC. The Java driver package for MongoDB is: https://github.com/mongodb/mongo-java-driver/releases.

The program code for ESPROC is as follows:

A1: Connect mongodb database, IP and port number is localhost:27017, database is test, username and password are test. If you need other parameters, you can follow mongo://ip:port/db?arg=value&. format to continue writing parameters.

A2: Use the Find function to take a number from MongoDB to form a cursor. The collection is orders, the filter condition is empty, and the specified key _id not taken out. You can see that the Find function is similar to MongoDB's find function. Esproc cursors are read and processed in batches to avoid excessive amounts of data and memory overflow situations. There is a small amount of data here, so fetch the function once.

A3: Obtain data from employee.

A4: Use the switch function to convert the value of the Sellerid field in A2 (orders) to a record reference in A3 (employee).

A5: Filter by condition. In this case, a macro is used to implement a dynamic parse expression, where it is the passed-in parameter. The collector calculates the ${first ...} Expression in the ${, substituting the result of the calculation as a macro string value ...} Then explain the execution. In this example, the final execution is: =a2.select (Sellerid. State= "California"). Since Sellerid has been converted into a reference to the employee's corresponding record, it is possible to write sellerid.state directly.

A6: Toggles the Sellerid in the filter results back to normal values.

A7: Returns a qualifying result set to Java.

Change the filter condition without changing the program, just change the where parameter. For example, the condition becomes either an order with state equal to California, or an order with client equal to PWQ. The parameter value of where can be written as: client== "Pwq" | | Sellerid. state== "California".

The code for using Esproc JDBC to invoke this program in a Java program is as follows: (Save the Esproc program as TEST.DFX, and place the MongoDB Java driver package in the classpath of the Java program):

Establishing a Esproc JDBC connection
Class.forName ("Com.esproc.jdbc.InternalDriver");
con= drivermanager.getconnection ("jdbc:esproc:local://");
Call the ESPROC program (stored procedure), where test is the file name of DFX
Com.esproc.jdbc.InternalCStatement st = (com.esproc.jdbc.InternalCStatement) con.preparecall ("Call Test (?)");
Setting parameters
St.setobject (1, "client==\" pwq\ "| | Sellerid. State==e\ "California\" ");
Executing Esproc stored Procedures
ResultSet set =st.executequery ();

The collector assists Java in processing the MongoDB of a diverse data source