Shulou(Shulou.com)06/01 Report--

MongoDB does not support join. The unityjdbc recommended on its official website can take the data out for secondary calculation to achieve join operation, but this feature is only available in the paid version. Other free jdbc drive supports only the most basic SQL statements, not join. If you use Java and other programming languages to take out the data to achieve join calculation, it is also more complex.

Join computing can be realized by using free esProc with MongoDB. Here is an example to illustrate the specific practice.

The document orders in MongoDB holds the order data, and employee holds the employee data. As follows:

MongoDB shell version: 2.6.4

Connecting to: test

> db.orders.find ()

{"_ id": ObjectId ("5434f88dd00ab5276493e270"), "ORDERID": 1, "CLIENT": "UJRNP"

"," SELLERID ": 17," AMOUNT ": 392," ORDERDATE ":" 15:28 on 2008-11-2 "}

{"_ id": ObjectId ("5434f88dd00ab5276493e271"), "ORDERID": 2, "CLIENT": "SJCH"

, "SELLERID": 6, "AMOUNT": 4802, "ORDERDATE": "15:28 on 2008-11-9"}

{"_ id": ObjectId ("5434f88dd00ab5276493e272"), "ORDERID": 3, "CLIENT": "UJRNP"

"," SELLERID ": 16," AMOUNT ": 13500," ORDERDATE ":" 15:28 on 2008-11-5 "}

{"_ id": ObjectId ("5434f88dd00ab5276493e273"), "ORDERID": 4, "CLIENT": "PWQ"

"SELLERID": 9, "AMOUNT": 26100, "ORDERDATE": "15:28 on 2008-11-8"}

...

> 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 ":" rotated "," 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 employee. You need to query all order information that the state attribute of employee is equal to California. Among them, orders has a large amount of data and cannot be loaded into memory at one time. The amount of Employee data is small, and the amount of data filtered by Orders is also relatively small.

Query conditional expressions can be passed to esProc as parameters, as shown in the following figure:

The program code of esProc is as follows:

A1: connect to the MongoDB database, ip and port number is localhost:27017, database is test, user name and password are test.

A2: use the find function to fetch numbers from MongoDB to form cursors. The collection is orders, the filter condition is empty, and the specified key _ id is not taken out. EsProc uses the same parameter format as mongdb's find statement in the find function. EsProc cursors support reading and processing data in batches to avoid excessive amounts of data and memory overflows.

A3: get the data in employee. Because the amount of data is small, it is taken out at one time with the fetch function.

A4: use the switch function to convert the value of the SELLERID field in cursor A2 to a record reference in A3 (employee).

A5: filter by condition. Macros are used here to implement dynamic parsing expressions, where where is the incoming parameter. The aggregator will first calculate ${. } to replace ${as a macro string value with the result of the evaluation. Explain the execution later. The final execution in this example is: = A4.select (SELLERID.STATE== "California"). Since SELLERID has been converted to a reference to the corresponding record of employee, you can write SELLERID.STATE directly. After filtering, the amount of data is small, so it is taken out at one time. If the result still has a large amount of data, it can be taken out in batches, such as 10000 pieces at a time: fetch (10000).

A6: switch the SELLERID in the filter results back to normal values.

The calculation result of A6 is:

When the filter condition changes, there is no need to change the program, just change the where parameters. For example, the condition becomes: 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".

EsProc does not include MongoDB's java driver package. To access MongoDB with esProc, you must put the java driver package of MongoDB (driver version 2.12.2 or above required by esProc, mongo-java-driver-2.12.2.jar) in [esProc installation directory]\ common\ jdbc in advance.

The script that esProc assists MongoDB calculation can be easily integrated into java. As long as you add a line of A7 and write it as result A6, you can output the results in the form of resultset to java. For specific code, please refer to the esProc tutorial. Similarly, using java to call esProc to access MongoDB must also put the java driver package of mongdb into the classpath of the java program.

Download address of the aggregator esProc: http://www.raqsoft.cn/?p=2643.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.