Spring Data MongoDB实现$lookup,$match聚合操作,以及低版本Spring引起的Cursor问题

在mysql里的一些多表连接操作,在mongodb里可以通过一些聚合操作来达到要求,同时通过spring-data-mongodb来实现也是十分简单的

看一下具体本地测试mongodb的版本,这个对后面遇到的问题有影响

> db.version()
4.2.8

先创建collection,user作为主表,mongodb直接insert数据会自动创建collection

db.user.insert([
	{"_id":NumberInt(1), "name":"lihui", "age":NumberInt(7), "classId":NumberInt(1)},
	{"_id":NumberInt(2), "name":"lilei", "age":NumberInt(8), "classId":NumberInt(2)},
	{"_id":NumberInt(3), "name":"liwei", "age":NumberInt(9), "classId":NumberInt(3)},
	{"_id":NumberInt(4), "name":"lucy", "age":NumberInt(10), "classId":NumberInt(1)},
	{"_id":NumberInt(5), "name":"lily", "age":NumberInt(11), "classId":NumberInt(5)}
])

再创建collection class,可见classId作为外键和_id关联

db.class.insert([
	{"_id":NumberInt(1), "name":"One", "total":NumberInt(10)},
	{"_id":NumberInt(2), "name":"Two", "total":NumberInt(20)},
	{"_id":NumberInt(3), "name":"Three", "total":NumberInt(30)},
	{"_id":NumberInt(4), "name":"Four", "total":NumberInt(40)}
])

这时候,假如想获取LEFT OUTER JOIN的SQL查询命令

select u.*,c.* from user u LEFT OUTER JOIN class c ON u.classId=c._id WHERE u.age>9;

在mongo shell里可以通过$lookup和$match聚合操作来实现

db.user.aggregate([
	{
		$lookup:
		{
			from: "class",
			localField: "classId",
			foreignField: "_id",
			as: "classInfo"
		}
	},
	{
		$match:
		{
			age: { $gt: 9}
		}
	}
])

理解起来比较简答,user为主集合,from为连接的集合,localField是主集合里的字段,也就是外键,foreignField是from集合里的字段,和外键衔接,as是最后输出新增在主集合后面的内容

如果说得不够清楚,可以看下官方文档的说明

Field Description
from 右集合,指定在同一数据库中执行连接的集合。此集合不能shared分片。
localField 指定左集合(db.collectionname)匹配的字段。如果左集合不包含localField,$lookup 视为null值来匹配。
foreignField 指定from集合(右集合)用来匹配的字段。如果集合不包含该字段,$lookup 视为null值来匹配。
as 指定要添加到输入文档的新数组字段的名称。新的数组字段包含from集合中匹配的文档。如果在文档中指定的名称已经存在,现有的领域覆盖。

看下执行的结果,满足预期,age大于9的有两条,而classId为5的,在class表里不存在,所以是空list

// 1
{
    "_id": NumberInt("4"),
    "name": "lucy",
    "age": NumberInt("10"),
    "classId": NumberInt("1"),
    "classInfo": [
        {
            "_id": NumberInt("1"),
            "name": "One",
            "total": NumberInt("10")
        }
    ]
}

// 2
{
    "_id": NumberInt("5"),
    "name": "lily",
    "age": NumberInt("11"),
    "classId": NumberInt("5"),
    "classInfo": [ ]
}

mongo shell的操作结束,下面就是具体spring-data-mongodb的实现代码

package com.lihuia.demo.service;

import com.alibaba.fastjson.JSON;
import com.lihuia.demo.entity.UserClassResult;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperation;
import org.springframework.data.mongodb.core.aggregation.LookupOperation;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

import static org.springframework.data.mongodb.core.aggregation.Aggregation.newAggregation;

@Service
public class AggregateService implements IAggregateService {

@Resource
private MongoTemplate mongoTemplate;

public String lookupOperation() {

LookupOperation lookup = LookupOperation.newLookup()
.from("class")
.localField("classId")
.foreignField("_id")
.as("classInfo");
AggregationOperation match = Aggregation.match(Criteria.where("age").gt(9));
Aggregation aggregation = newAggregation(lookup, match);
List<UserClassResult> results = mongoTemplate
.aggregate(aggregation, "user", UserClassResult.class)
.getMappedResults();
return JSON.toJSONString(results, true);
}
}

参数很清晰,可惜结果毫不客气来了个空指针

com.mongodb.MongoCommandException: Command failed with error 9: 'The 'cursor' option is required, except for aggregate with the explain argument' on server localhost:27017. The full response is { "ok" : 0.0, "errmsg" : "The 'cursor' option is required, except for aggregate with the explain argument", "code" : 9, "codeName" : "FailedToParse" }
	at com.mongodb.CommandResult.getException(CommandResult.java:80) ~[mongodb-driver-3.4.3.jar:na]
	at com.mongodb.CommandResult.throwOnError(CommandResult.java:94) ~[mongodb-driver-3.4.3.jar:na]
	at org.springframework.data.mongodb.core.MongoTemplate.handleCommandError(MongoTemplate.java:2100) ~[spring-data-mongodb-1.10.8.RELEASE.jar:na]
	at org.springframework.data.mongodb.core.MongoTemplate.aggregate(MongoTemplate.java:1577) ~[spring-data-mongodb-1.10.8.RELEASE.jar:na]
	at org.springframework.data.mongodb.core.MongoTemplate.aggregate(MongoTemplate.java:1511) ~[spring-data-mongodb-1.10.8.RELEASE.jar:na]
	at com.lihuia.demo.service.AggregateService.lookupOperation(AggregateService.java:33) ~[classes/:na]
	at com.lihuia.demo.controller.UserController.aggregate(UserController.java:55) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_241]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_241]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_241]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_241]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) ~[spring-web-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) ~[spring-webmvc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) ~[spring-webmvc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) ~[spring-webmvc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) ~[spring-webmvc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) ~[tomcat-embed-core-8.5.23.jar:8.5.23]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[tomcat-embed-core-8.5.23.jar:8.5.23]

具体的错误原因,下面这个帖子说得很清楚:

https://blog.csdn.net/LLF_1241352445/article/details/82084548?utm_source=blogxgwz1?utm_medium=distribute.pc_relevant.none-task-blog-baidujs-1

之前已经查过了,mongo的版本是4,可是我的spring版本超级老

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.8.RELEASE</version>
</parent>

网上的解决方案基本都是升级spring版本,我简单试了下,换成1.5.10的确能够解决这个问题,能够正常返回mongo shell执行的结果

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.10.RELEASE</version>
</parent>

 

但是目前整个工程框架用的版本就是1.5.8,不能因为这一个功能而修改版本,因此急需寻找解决方案

又看到了下面这个帖子

https://blog.csdn.net/u010522235/article/details/89245701?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase

这里没有直接调用mongoTemplate的aggregate方法,而是调用集合collection的aggregate方法,而且同样错误的方式也列出来了,试了一下是OK的

源码如下

package com.lihuia.demo.service;

import com.alibaba.fastjson.JSON;
import com.mongodb.AggregationOptions;
import com.mongodb.Cursor;
import com.mongodb.DBObject;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperation;
import org.springframework.data.mongodb.core.aggregation.LookupOperation;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

@Service
public class AggregateService implements IAggregateService {

@Resource
private MongoTemplate mongoTemplate;

public String lookupOperation() {

LookupOperation lookup = LookupOperation.newLookup()
.from("class")
.localField("classId")
.foreignField("_id")
.as("classInfo");
AggregationOperation match = Aggregation.match(Criteria.where("age").gt(9));
List<DBObject> list = new ArrayList<>();
list.add(lookup.toDBObject(Aggregation.DEFAULT_CONTEXT));
list.add(match.toDBObject(Aggregation.DEFAULT_CONTEXT));
Cursor cursor = mongoTemplate.getCollection("user")
.aggregate(list, AggregationOptions
.builder()
.allowDiskUse(true)
.outputMode(AggregationOptions.OutputMode.CURSOR)
.build());
return JSON.toJSONString(cursor, true);
}
}

执行结果和mongo shell一致

[
    {
        "classInfo": [
            {
                "total": 10,
                "name": "One",
                "_id": 1
            }
        ],
        "classId": 1,
        "name": "lucy",
        "_id": 4,
        "age": 10
    },
    {
        "classInfo": [
        ],
        "classId": 5,
        "name": "lily",
        "_id": 5,
        "age": 11
    }
]

解决

到了这一步,修改一下返回值

package com.lihuia.demo.entity;

import lombok.Data;

import java.util.List;

@Data
public class UserClassResult {

private Integer _id;
private String name;
private Integer age;
private Integer classId;
private List<ClassInfo> classInfo;

@Data
public static class ClassInfo {
private Integer _id;
private String name;
private Integer total;
}
}

将DBObject改为Java Object,找了半天才发现spring-data-mongodb里就有现成的

package com.lihuia.demo.service;

import com.lihuia.demo.entity.UserClassResult;
import com.mongodb.AggregationOptions;
import com.mongodb.Cursor;
import com.mongodb.DBObject;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperation;
import org.springframework.data.mongodb.core.aggregation.LookupOperation;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

@Service
public class AggregateService implements IAggregateService {

@Resource
private MongoTemplate mongoTemplate;

public List<UserClassResult> lookupOperation() {

LookupOperation lookup = LookupOperation.newLookup()
.from("class")
.localField("classId")
.foreignField("_id")
.as("classInfo");
AggregationOperation match = Aggregation.match(Criteria.where("age").gt(9));
List<DBObject> list = new ArrayList<>();
list.add(lookup.toDBObject(Aggregation.DEFAULT_CONTEXT));
list.add(match.toDBObject(Aggregation.DEFAULT_CONTEXT));
Cursor cursor = mongoTemplate.getCollection("user")
.aggregate(list, AggregationOptions
.builder()
.allowDiskUse(true)
.outputMode(AggregationOptions.OutputMode.CURSOR)
.build());

List<UserClassResult> results = new ArrayList<>();
while (cursor.hasNext()) {
DBObject object = cursor.next();
UserClassResult result = mongoTemplate.getConverter().read(UserClassResult.class, object);
results.add(result);
}
return results;
}
}

到这里结束,参数可以自行传,返回list自行控制,甚至可以order by排序

比如,mongo shell里,按age大小逆序

db.user.aggregate([
	{
		$lookup:
		{
			from: "class",
			localField: "classId",
			foreignField: "_id",
			as: "classInfo"
		}
	},
	{
		$match:
		{
			age: { $gt: 9}
		}
	},
	{
		$sort:
		{
			age: -1
		}
	}
])

结果也是age从大到小排序

// 1
{
    "_id": NumberInt("5"),
    "name": "lily",
    "age": NumberInt("11"),
    "classId": NumberInt("5"),
    "classInfo": [ ]
}

// 2
{
    "_id": NumberInt("4"),
    "name": "lucy",
    "age": NumberInt("10"),
    "classId": NumberInt("1"),
    "classInfo": [
        {
            "_id": NumberInt("1"),
            "name": "One",
            "total": NumberInt("10")
        }
    ]
}

代码里只需要新增一个$sort的聚合条件

SortOperation sort = Aggregation.sort(new Sort(Sort.Direction.DESC, "age"));
List<DBObject> list = new ArrayList<>();
list.add(lookup.toDBObject(Aggregation.DEFAULT_CONTEXT));
list.add(match.toDBObject(Aggregation.DEFAULT_CONTEXT));
list.add(sort.toDBObject(Aggregation.DEFAULT_CONTEXT));

OVER

发表评论