How to perform SQL-like queries on MongoDB in Java?

To other questions:
How to easily handle text file calculations in Java?
How to easily consolidate data from different sources in Java?
What should a Java programmer do when it is too difficult to write complex SQL?
What is the lightweight Java library to read and write Excel dynamically?
What would be a dynamic and flexible way to transform Json to Java object?
What should I do when it is difficult to implement code with Java Stream?

Solution: esProc – the professional computational package for Java

imagepng

esProc is a class library dedicated to Java-based calculations and aims to simplify Java code. SPL is a scripting language based on the esProc computing package. It can be deployed together with Java programs and understood as a stored procedure outside the database. Its usage is the same as calling a stored procedure in a Java program. It is passed to the Java program for execution through the JDBC interface, realizing step-by-step structured computing, return the ResultSet object. It is light , has simple syntax, and supports MongoDB data computation.

For example, MongoDB has a collection named test1, which stores employee information, and the Orders field is an array type, which keeps multiple orders of the current employee. Thus, part of the data is as follows:

[{
      "_id": {"$oid":   "6074f6c7e85e8d46400dc4a7"},
      "EId": 7,"State":   "Illinois","Dept": "Sales","Name":   "Alexis","Gender": "F","Salary":   9000,"Birthday": "1972-08-16",
      "Orders": [
         {"OrderID":   70,"Client": "DSG","SellerId":   7,"Amount": 288,"OrderDate": "2009-09-30"},
         {"OrderID":   131,"Client": "FOL","SellerId":   7,"Amount": 103.2,"OrderDate": "2009-12-10"}
    ]
}
{
      "_id": {"$oid":   "6074f6c7e85e8d46400dc4a8"},
      "EId": 8,"State": "California", ...
}]

SPL handles the conditional query on multi-layer collections in the following way:


A

1

=mongo_open("mongodb://127.0.0.1:27017/mongo")

2

=mongo_shell(A1,"test1.find()")

3

=A2.conj(Orders)

4

=A3.select(Amount>1000 && Amount<=3000 && like@c(Client,"*s*")).fetch()

5

=mongo_close(A1)

According to A2, esProc supports MongoDB's JSON-style query expressions (find, count, distinct and aggregate). To do an interval-based query, the SPL is like this: =mongo_shell(A2,"test1.find({Orders.Amount:{gt:1000,gt:1000,lt:3000}})").

This block of code can be executed in esProc IDE, and stored as a script file (like select.dfx) for invocation from a Java program through the JDBC interface. Below is the code for invocation:

package Test;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.Statement;
  public class test1 {
      public static void main(String[]   args)throws Exception {
            Class.forName("com.esproc.jdbc.InternalDriver");
          Connection connection   =DriverManager.getConnection("jdbc:esproc:local://");
          Statement statement =   connection.createStatement();
          ResultSet result =   statement.executeQuery("call select()");

……
          if(connection != null)   connection.close();
      }
  }

For details on integration with Java programs, please refer to How to Call an SPL Script in Java

SPL achieves grouping and aggregation using the following code:


A

1

=mongo_open("mongodb://127.0.0.1:27017/mongo")

2

=mongo_shell(A1,"test1.find()")

3

=A2.conj(Orders).groups(year(OrderDate);sum(Amount))

4

=mongo_close(A1)

Or implements a join operation using code below:


A

1

=mongo_open("mongodb://127.0.0.1:27017/mongo")

2

=mongo_shell(A1,"test1.find()")

3

=A2.new(Orders.OrderID,Orders.Client,Name,Gender,Dept).fetch()

4

=mongo_close(A1)

The SPL data structure itself is multi-layered, which can directly correspond to multi-layer collections and can naturally express the master-child relationship, so there is no need for other associations. However, other computing libraries are single-layer data structures, which are difficult to correspond to a multi-layer collection.

SPL supports joining two single-level collections, of course:


A

1

=mongo_open("mongodb://127.0.0.1:27017/mongo")

2

=mongo_shell(A1,"Orders.find()").fetch()

3

=mongo_shell(A1,"Employees.find()").fetch()

4

=mongo_close(A1)

5

=join(A2,SellerId;A3,EId)

6

=A5.new(_1.OrderID,_1.Client,_2.Name,_2.Gender,_2.Dept)

SPL has rich expressions with procedural syntax and the support of SQL syntax. As SQL does not support multilevel data, SQL in SPL supports the join of two single-level collections, as shown by the code below:


A

1

=mongo_open("mongodb://127.0.0.1:27017/mongo")

2

=mongo_shell(A34,"Orders.find()").fetch()

3

=mongo_shell(A34,"Employees.find()").fetch()

4

=mongo_close(A34)

5

$select o.OrderId,o.Client,e.Name,e.Gender,e.Dept  from {A35}  o join {A36} e on  o.SellerId=e.EId

One of MongoDB’s feature is the multilevel data, yet it is difficult to handle multilevel data using JSON-style expressions. If we could use SPL as the computing class library, the computing process will be much simplified. In the following task, we need to sum the numbers under income field and those under output field respectively.

_id

income

output

1

"cpu":1000,   "mem":500,     "mouse":"100"

"cpu":1000, "mem":600     ,"mouse":"120"

2

"cpu":2000,"mem":1000,      "mouse":"50","mainboard":500

{"cpu":1500,   "mem":300}

Below is the code using JSON-style expressions. It is complicated.

var fields = [  "income",   "output"];
db.computer.aggregate([ 
   { 
      $project:{ 
           "values":{ 
              $filter:{ 
                 input:{ 
                      "$objectToArray":"$$ROOT"
                 },
                 cond:{ 
                    $in:[ 
                       "$$this.k",
                       fields
                    ]
                 }
              }
         }
      }
   },
   { 
      $unwind:"$values"
   },
   { 
      $project:{ 
           key:"$values.k",
           values:{ 
              "$sum":{ 
                 "$let":{ 
                    "vars":{ 
                       "item":{ 
                            "$objectToArray":"$values.v"
                       }
                    },
                      "in":"$$item.v"
                 }
              }
         }
      }
   },
   {$sort: {"_id":-1}},
   { "$group": {
    "_id": "$_id",
    'income':{"$first":     "$values"},
    "output":{"$last":     "$values"}
    }},
]);

SPL handles it in a simple and elegant way:


A

1

=mongo_open("mongodb://127.0.0.1:27017/raqdb")

2

=mongo_shell(A1,"computer.find()").fetch()

3

=A2.new(_id:ID,income.array().sum():INCOME,output.array().sum():OUTPUT)

4

>A1.close()

To use esProc IDE for MongoDB, just start MongoCLi in Extend library. The deployment supports the use of GUI interface.

Refer to the following to connect to MongoDB in your java application.

Obtain the required jar package from the esProc external library file directory, installation directory \esProc\extlib\MongoCli. The core jar package of the external library is MongoCli.jar. bson-3.6.3.jar and mongo-java-driver-3.6.3.jar are third-party jar packages. Please download them from the Internet and replace them with the correct jar packages according to the MongoDB version you are using.

For more examples, please refer to Assist MongoDB Calculation(pdf)

For more SPL applications, please refer to Use SPL in applications


esProc SPL Source Code and Package 👉

https://github.com/SPLWare/esProc

esProc SPL Official Website 👉

http://www.scudata.com

esProc SPL Forum 👉

http://c.raqsoft.com