Java 怎样对 MongoDB 执行 SQL

 

开启MongoDBConnector服务后,可以通过MySQL JDBC执行SQL。官方工具的优点是适配性极佳,几乎无须配置就能使用,缺点是计算能力较弱,比如不支持窗口函数,所以官方给的建议是只限于BI工具中使用。

使用Calcite类库也可以在MongoDB上执行通用SQL,优点是迁移性极佳,缺点是计算能力非常弱,连模糊查询和日期函数都不支持;所有的多层collection都要改造成单层,否则有些计算没法实现;另外就是配置很麻烦。

更好的方法是使用开源的集算器SPL,原理和Calcite有点像,也是在MongDB上执行通用SQL,但SPL的计算能力非常强大,且无须改造MongoDB,也没有麻烦的配置。

SPL基本用法简单。比如某collection是两层结构,上层存储多条员工记录,其中Orders字段为数组类型,用来存储当前员工的多个订单。对下层的订单进行条件查询,只需如下JAVA代码:


Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str="$select * from
{mongo_shell@x(A1,mongo_open(\"mongodb://127.0.0.1:27017/mongo\"),\"data.find()\").conj(Orders)}
where Client like '%S%' or (Amount>1000 and Amount<=2000)";
ResultSet result = statement.executeQuery(str);

SPL支持计算代码外置,可显著降低代码耦合性,尤其适合代码较长或频繁修改的情况。比如上面的条件查询,可先将计算代码存储为SPL脚本文件:


A

1

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

2

=mongo_shell@x(A1,"data.find()")

3

=A2.conj(Orders)

4

$select * from {A3} where Client like   '%S%' or (Amount>1000 and Amount<=2000)

         JAVA中调用时,只需以存储过程的形式引用脚本文件名:


Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str="call condition()";
ResultSet result = statement.executeQuery(str);

SPL 支持常见的SQL语法,可实现各类日常计算,下面举一些例子:

#sort
$select * from {A3} order by Client,Amont desc

#group by
$ select year(orderdate) y,sum(amount) s from {A3}

group by year(orderdate) having sum(amount)>=2000000

#distinct
$ select distinct(sellerid) from {A3}

SPL支持高级SQL语法,可实现难度较大的计算,比如集合计算、case  whenwith、嵌套子查询等,详见在文件上使用 SQL 查询的示例

上面SQL中的{…}SPL扩展函数,扩展函数具SQL等价的计算能力,而且代码普遍更简练。比如关联查询,不用SQL而直接用扩展函数实现:


A

1

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

2

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

3

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

SPL扩展函数可以简化复杂的计算逻辑。比如:计算某支股票最长的连续上涨天数,只需两行代码:


A

1

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

2

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

3

=a=0,A1.max(a=if(price>price[-1],a+1,0))

MongoDB经常用来存储Json,而SQL只适合计算结构化记录,不适合计算多层jsonSPL的数据对象本身就是多层的,特别擅长简化多层json的计算。比如,对于下面的collection统计每条记录中 incomeoutput 的数量之和:

_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}

这个题目有一定难度,SQL、存储过程,包括MongDB自己的查询语法都很难计算,用SPL计算就简单多了:


A

1

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

2

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

3

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

 

对于逻辑较复杂的脚本,SPL提供了专业的IDE,不仅有完整的调试功能,还能用表格的形式观察每一步的中间计算结果:

IDEpng

除了上面的特性,SPL在配置方面也特别简单,还支持SQL或扩展函数计算csv\xls\restful\各种NoSQL数据源,感兴趣可上官网。