SpringBoot+MongoDB如何实现类似多表关联查询分页？

Tags: /Java/ Date Created:

MongoDb中，CollectionA和CollectionB，其中A.xxxId和B.xxxID是关联的数据，现在如何查找A中的满足A.field1 = 1000，并且B.status = 'A' 或 'B' 的数据并进行分页？

等价SQL代码：

select * from CollectionA A, CollectionB B where A.xxxID = B.xxxID and B.status in ('A', 'B') group by xid order by xxxId limit 0, 15

关键在于 Pipeline部分 mongodb - $match in $lookup result - Stack Overflow，请注意，下面查询的代码，顺序非常重要，必须按照 group, sort, skip, limit 顺序来处理，否则会导致分页和排序不正确。

db.CollectionA.aggregate([
    {
        "$lookup": {
            "from": "CollectionB",
            "localField": "xxxID",
            "foreignField": "xxxID",
            "as": "B"，
            "pipeline": [
               {$in: { "status": ['A', 'B'] }}
            ]
        }
    },
	{
        $unwind: {
            path: '$B',
            preserveNullAndEmptyArrays: true // 保留没有匹配的CollectionA记录true: 类似 left join b on xxx
        }
    },
    {
        "$match": { "a.field1": 1000 }
    },
	{ $group: { _id: '$xxxID' } },
    { $sort: { _id: 1}},
    {  "$skip": 0 },
    {  "$limit": 15 }
])

Java代码：

String query = "{\n" +
" $lookup: {\n" +
" from: 'CollectionB',\n" +
" localField: 'xxxID',\n" +
" foreignField: 'xxxID',\n" +
" as: 'B',\n" +
" pipeline: [{\n" +
" $match: { status: {$in: ['A', 'B'] } }\n" +
" }]\n" +
" }\n" +
"}";
List<Document> listDoc = new ArrayList<>();
listDoc.add(Document.parse(query));
listDoc.add(Document.parse("{ $match: { 'xxxID': 12345 } }"));
listDoc.add(Document.parse("{ $group: { _id: '$xxxID' } }"));
listDoc.add(Document.parse("{ $sort: { _id: 1}}"));
listDoc.add(Document.parse("{ $skip: 0 }"));
listDoc.add(Document.parse("{ $limit: 15 }"));
List<Document> ret = mongoTemplate.getCollection("CollectionA")
.aggregate(listDoc)
.into(new ArrayList<>());

Java SpringBoot实现代码：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
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.AggregationResults;
import org.springframework.data.mongodb.core.aggregation.LookupOperation;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;

@Service
public class YourService {

    @Autowired
    private MongoTemplate mongoTemplate;

    public Page<YourResultClass> findFilteredAndPagedData(int page, int size) {
        // 定义聚合操作：$lookup
        LookupOperation lookupOperation = LookupOperation.newLookup()
                .from("CollectionB")
                .localField("xxxID")
                .foreignField("xxxID")
                .as("B");

        // 定义聚合操作：$match
        Criteria criteria = Criteria.where("a.field1").is(1000)
                .and("B.status").in("A", "B");
        AggregationOperation matchOperation = Aggregation.match(criteria);

        // 定义聚合操作：$skip 和 $limit
        AggregationOperation skipOperation = Aggregation.skip(0);
        AggregationOperation limitOperation = Aggregation.limit(size);

        // 构建聚合查询
        Aggregation aggregation = Aggregation.newAggregation(
                lookupOperation,
                matchOperation,
                skipOperation,
                limitOperation
        );

        // 执行聚合查询
        AggregationResults<YourResultClass> results = mongoTemplate.aggregate(
                aggregation, "CollectionA", YourResultClass.class);

        // 构建分页结果
        return PageableExecutionUtils.getPage(results.getMappedResults(),
                PageRequest.of(page - 1, size),
                () -> mongoTemplate.count(Aggregation.newAggregation(matchOperation), "CollectionA"));
    }
}

参考资料: java mongodb 多表关联查询，多条件查询，分页，排序_mongodb跨表查询分页_seesun2012的博客-CSDN博客