Java 怎样对 MongoDB 执行 SQL
开启MongoDB的Connector服务后,可以通过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 when、with、嵌套子查询等,详见《在文件上使用 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只适合计算结构化记录,不适合计算多层json。SPL的数据对象本身就是多层的,特别擅长简化多层json的计算。比如,对于下面的collection,统计每条记录中 income,output 的数量之和:
_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,不仅有完整的调试功能,还能用表格的形式观察每一步的中间计算结果:
除了上面的特性,SPL在配置方面也特别简单,还支持SQL或扩展函数计算csv\xls\restful\各种NoSQL数据源,感兴趣可上官网。