关于Mongodb + java + 多表关联查询（MongoTemplate）

自己关于使用MongoTemplate的一点总结

1.创建集合

// 物料
db.material.insert([
   { "_id" : "M00001", "name" : "油漆", "price" : 12},
   { "_id" : "M00002", "name" : "地板装", "price" : 20 },
   { "_id" : "M00003", "name": "水泥", "price": 50  }
])

// 材料商
db.merchant.insert([
   { "_id" : "T00001", "name" : "张三建材", "materialCode" : "M00001"},
   { "_id" : "T00002", "name" : "李四建材", "materialCode" : "M00002" },
   { "_id" : "T00003", "name" : "王五建材", "materialCode": "M00003"  }
])

2.多表关联

2.1关联查询

两个表关联查询

package com.hollysys.tn.controller;

import io.swagger.annotations.Api;
import lombok.Data;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.LookupOperation;
import org.springframework.data.mongodb.core.aggregation.ProjectionOperation;
import org.springframework.data.mongodb.core.mapping.Document;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping(value = "/test")
@Api(tags = "测试")
public class TestController {

	@Autowired
	private MongoTemplate mongo;

	@Data
	@Document("material")
	class Material{
		private String _id;
		private String name;
		private Integer price;
	}

	@Data
	@Document("merchant")
	class Merchant{
		private String _id;
		private String name;
		private String materialCode;
	}

	@Data
	class ResultEntity{
		private String _id;
		private String name;
		private Integer price;
		private String merchantName;
	}

	@GetMapping("/test")
	public List<Map> test() {
		LookupOperation lookupOperation = LookupOperation.newLookup()
				.from("merchant")  // 被关联表
				.localField("_id")	// 主表关联字段
				.foreignField("materialCode")	// 被关联字段
				.as("merchant_as");	// 别名

		ProjectionOperation project = Aggregation.project("_id","name","price")
				.and("merchant_as.name").as("merchantName");

		Aggregation aggregation = Aggregation.newAggregation(lookupOperation,project);

		List<Map> result =  mongo.aggregate(aggregation,"material",Map.class).getMappedResults();

		//List<ResultEntity> result =  mongo.aggregate(aggregation,"material",ResultEntity.class).getMappedResults();
		System.out.println(result);
		return result;
	}
}

执行的结果如下：

[
    {
        "_id": "M00001",
        "name": "油漆",
        "price": 12.0,
        "merchantName": [
            "张三建材"
        ]
    },
    {
        "_id": "M00002",
        "name": "地板装",
        "price": 20.0,
        "merchantName": [
            "李四建材"
        ]
    },
    {
        "_id": "M00003",
        "name": "水泥",
        "price": 50.0,
        "merchantName": [
            "王五建材"
        ]
    }
]

应该注意到了，返回结果中的merchantName是一个数组，如果需要像对象一样，如下：

[
    {
        "_id": "M00001",
        "name": "油漆",
        "price": 12,
        "merchantName": "张三建材"
    },
    {
        "_id": "M00002",
        "name": "地板装",
        "price": 20,
        "merchantName": "李四建材"
    },
    {
        "_id": "M00003",
        "name": "水泥",
        "price": 50,
        "merchantName": "王五建材"
    }
]

有两种方法可以实现上面的效果

• 1.可以单独设置一个返回对象，具体见代码注释部分中的ResultEntity对象
• 2.修改代码，加Aggregation.unwind("merchantName")，用来打散数组

Aggregation aggregation = Aggregation.newAggregation(lookupOperation,project,Aggregation.unwind("merchantName"));

下面是具体的mongo查询语句：

db.material.aggregate([
    {
        $lookup:{
            from: "merchant",
            localField: "_id",
            foreignField: "materialCode",
            as: "merchant_as"
        }
    },
    {
        $project:{
            "_id":1,
            "name":1,
            "price":1,
            "merchantName":"$merchant_as.name"
        }
    },
    {
        $unwind:"$merchantName"
    }
])

2.2 关联 + 查询条件

其实如果理解了mong的语法，下面的理解起来就更简单了。

• 1.如先筛选主表，查询价格小于30的物料

Criteria criteria = new Criteria().and("price").lt(30);
Aggregation aggregation = Aggregation.newAggregation(
				Aggregation.match(criteria),
				lookupOperation,
				project,
				Aggregation.unwind("merchantName"));

结果如下：

[
    {
        "_id": "M00001",
        "name": "油漆",
        "price": 12.0,
        "merchantName": "张三建材"
    },
    {
        "_id": "M00002",
        "name": "地板装",
        "price": 20.0,
        "merchantName": "李四建材"
    }
]

• 2.对结果再次筛选，筛选出材料商是【张三建材】

Criteria criteria2 = new Criteria().and("merchantName").is("张三建材");

		Aggregation aggregation = Aggregation.newAggregation(
				Aggregation.match(criteria),
				lookupOperation,
				project,
				Aggregation.unwind("merchantName"),
				Aggregation.match(criteria2));

结果如下：

[
    {
        "_id": "M00001",
        "name": "油漆",
        "price": 12.0,
        "merchantName": "张三建材"
    }
]

下面是具体的mongo查询语句：

db.material.aggregate([
    {
        $match:{"price" : { "$lt" : 30 } }
    },
    {
        $lookup:{
            from: "merchant",
            localField: "_id",
            foreignField: "materialCode",
            as: "merchant_as"
        }
    },
    {
        $project:{
            "_id":1,
            "name":1,
            "price":1,
            "merchantName":"$merchant_as.name"
        }
    },
    {
        $unwind:"$merchantName"
    },
    {
        $match : { "merchantName" : "张三建材" }
    },
])

2.3 小结

使用MongoTemplate之前最好熟悉下mongo的基本语法。个人用mongo时间也不长，还没到熟练的阶段，有什么问题希望提出来，大家一起学习进步！

这篇就到这里，后面有空再补充其他的。