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

by

2020-07-20

22:09:48

Leave a comment on Spring Data MongoDB实现$lookup，$match聚合操作，以及低版本Spring引起的Cursor问题

MongoDB

,

在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

发表回复