【mongoDB】学习（三）java + mongodb 多表联合查询

你的名字 2023-02-08 12:38 5阅读 0赞

## 【mongoDB】 学习（三）java + mongodb 多表联合查询 ##

### 关键字 ###

mongoDB、Java、$lookup、 $project、 $match 等

### 1、先介绍这次使用的 mongoDB 内的方法以及使用方式（Robo 3T 1.3.1） ###

> mongoDB 官网  
> [https://docs.mongodb.com/manual/reference/operator/aggregation/project/index.html][https_docs.mongodb.com_manual_reference_operator_aggregation_project_index.html]

##### （1）$lookup #####

类似SQL server里面的 join

"$lookup": {
         
        "from": "users",//关联目标表名
        "localField": "human",//外键
        "foreignField": "humanSerialNumber", //目标表明内连接字名
        "as": "huamn"//别名
      }

##### （2）$project #####

类似SQL server里面 select 后面的内容

"$project" : {
         
            "deviceID" :1,//1=显示字段
            "humanLoc":1,
            "human":"$human",//引入列表
            "time":1,
            "checkFor":"$devices.area",//重命名列 -表devices下的area 列 (类似sql 里面的as)
        }

##### （3）$match #####

类似SQL server里面where 后面内容

"$match":{
        
        "origin":"QA",//eq
        "bind":{
        "$regex":"12972247"}//like
        }

##### （4）$split #####

字符串切割生成字符数组

{
         "$split": ["$bind", "0000000000"] }

##### （5）$cond #####

{
        "$cond":{
        
        "if":{
         
            "$gte": [ {
        "$size":{
         "$split": ["$bind", "0000000000"] }}
                       , 2 
                    ]
             }
         ,"then":1
         ,"else":0
      }
    }

##### （6）$arrayElemAt #####

取列表元素

{
         "$arrayElemAt": ["$devices",0]}

### 2、表bindings、users、devices 三表连表查询 ###

###### （1）表 bindings–二维码绑定记录表 ######

{
        
        "_id" : ObjectId("5c99d700d360a612e21cccba"),
        "qrcode" : "66E88CFEFB964D074290160E2F",
        "bind" : "000000000010126460",
        "device_info" : "X888D77S6||10126460",
        "device_name" : "电动车:0.4t",
        "detail" : "设备号: 10126460\n序列号: X888D77S6\n设备类型:CS0001 电动车:1.4t\n送达方客户名: 服务组001\n设备状态: 在客户处 [CUST]",
        "human" : "82306690787299121764776791679022450814",
        "humanLoc" : "121.610741,31.185281,上海市浦东新区张江高科技园区,上海市浦东新区张江高科技园区，上海市浦东新区张江高科技园区",
        "origin" : "QA",
        "timestamp" : "1553585920323",
        "area_code" : "6053"
    }

###### （2）表 devices–设备表 ######

{
        
        "_id" : ObjectId("5e96a93749b0833d2ad1b211"),
        "bindingSerial" : "[X888D77S6] [11313212]",
        "origin" : "QA",
        "deviceID" : "11313212",
        "bindContent" : [ 
            {
        
                "name" : "电动车:0.4t",
                "bindFlag" : "11313212",
                "bindValues" : [ 
                    {
        
                        "fieldName" : "设备号",
                        "fieldValue" : "11313212"
                    }, 
                    {
        
                        "fieldName" : "序列号",
                        "fieldValue" : "G74104-001"
                    }, 
                    {
        
                        "fieldName" : "设备类型",
                        "fieldValue" : "CS0040"
                    }, 
                    {
        
                        "fieldName" : "设备状态",
                        "fieldValue" : "在客户处 [CUST]"
                    }, 
                    {
        
                        "fieldName" : "销售组",
                        "fieldValue" : "CTZ"
                    }, 
                    {
        
                        "fieldName" : "设备属性",
                        "fieldValue" : "R"
                    }
                ]
            }
        ],
        "area" : "6053",
        "createTime" : "1586931965"
    }

###### （3）表 users–用户信息表 ######

{
        
        "_id" : ObjectId("5c6b5084b7a8cb502a7d8aa7"),
        "humanSerialNumber" : "82306690787299121764776791679022450814",
        "humanHash" : "82306690787299121764776791679022450814",
        "origin" : "QA",
        "humanName" : "QA-qa007",
        "passwd" : "test123",
        "customContext" : [ 
            {
        
                "paramName" : "username",
                "paramType" : "string",
                "paramValue" : "qa007",
                "showOrNot" : 1
            }, 
            {
        
                "paramName" : "phoneNumber",
                "paramType" : "string",
                "paramValue" : "13625012007",
                "showOrNot" : 1
            }
        ],
        "birthTime" : NumberLong(1550536836237)
    }

### 3、构建完整查询语句（Robo 3T 1.3.1） ###

db.getCollection("bindings").aggregate([
    //关联users 表
    {
        
      "$lookup": {
         
        "from": "users",
        "localField": "human",
        "foreignField": "humanSerialNumber", 
        "as": "userInfo"//users 表的别名--查询结果别名
      }
    }
    ,
    //查询条件
    {
        "$match":{
        
        "origin":"QA",
        "bind":{
        "$regex":"12972247"}
        }}
    ,
    //处理结果数据
    {
        "$project" : {
         
            //对bind内容作处理，即去除bind 前导0 信息（开始连续10个0），取出id
            "deviceID":{
         "$arrayElemAt": [{
         "$split": ["$bind", "0000000000"] }, {
        "$cond":{
        "if":{
         "$gte": [ {
        "$size":{
         "$split": ["$bind", "0000000000"] }}, 2 ] },"then":1,"else":0}} ]},
            "humanLoc":"$humanLoc",
            "human":"$userInfo.humanName",//as 重命名列名
            "time":1,//显示时间列
        } }
    ,
    //将上述结果再次链表查询 设备信息
    {
        
      "$lookup": {
         
        "from": "devices",
        "localField": "deviceID",
        "foreignField": "deviceID", 
        "as": "devices"//devices 表查询结果别名
      }
    }
    ,
    //处理数据 
    {
        "$project" : {
         
            "deviceID" :1,
            "humanLoc":1,
            "human":{
         "$arrayElemAt": ["$human",0]},
            "time":1,
            "device":{
         "$arrayElemAt": ["$devices",0]},
        } }
    ,
    {
        "$project" : {
         
            "deviceID" :1,
            "humanLoc":1,
            "human":"$human",
            "time":1,
            "area":"$device.area",
            "device":{
         "$arrayElemAt": ["$device.bindContent.bindValues",0]},
        } } 
    
    ])

查询结果如下：

{
        
        "_id" : ObjectId("5f6d68824a3d4781a310ca97"),
        "time" : "1601005657470",
        "deviceID" : "12972247",
        "humanLoc" : "121.610741,31.185281,上海市浦东新区张江高科技园区,上海市浦东新区张江高科技园区，上海市浦东新区张江高科技园区",
        "area" : "6053",
        "device" : [ 
            		{
        
                        "fieldName" : "设备号",
                        "fieldValue" : "11313212"
                    }, 
                    {
        
                        "fieldName" : "序列号",
                        "fieldValue" : "G74104-001"
                    }, 
                    {
        
                        "fieldName" : "设备类型",
                        "fieldValue" : "CS0040"
                    }, 
                    {
        
                        "fieldName" : "设备状态",
                        "fieldValue" : "在客户处 [CUST]"
                    }, 
                    {
        
                        "fieldName" : "销售组",
                        "fieldValue" : "CTZ"
                    }, 
                    {
        
                        "fieldName" : "设备属性",
                        "fieldValue" : "R"
                    }
        ]
    }

### 4、Java 中实现查询语句 ###

##### （1）引入mongoDB 包 #####

//org.springframework.data:spring-data-mongodb:2.1.6.RELEASE
    <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-mongodb</artifactId>
    </dependency>

##### （2）构造查询语句 #####

这里使用Document 类

List<Document> arrs = new ArrayList<Document>();
     arrs.add(new Document("$lookup",
                    new Document("from", "Human_Slices")
                            .append("localField", "human")
                            .append("foreignField", "humanSerialNumber")
                            .append("as", "huamn")));
     Document match = new Document("origin", origin);
     //****************查询条件--开始***********************
     //String origin, long startDate, long endDate, String queryFieldName, String queryFieldValue, String humanName, String freebieWord
     //time
            if (startDate > 0 && endDate > 0) {
        
                match.append("time", new Document("$lte", String.valueOf(endDate)))
                        .append("time", new Document("$gte", String.valueOf(startDate)));
            }
            //detail
            if (queryFieldName != null && queryFieldValue != null &&
                    !queryFieldName.isEmpty() && !queryFieldValue.isEmpty()) {
        
                match.append("detail", new Document("$regex", String.join(": ", queryFieldName, queryFieldValue)));
            }
            //human
            if (humanName != null && !humanName.isEmpty()) {
        
                match.append("huamn.humanName", humanName);
            }
            //name
            if (freebieWord != null && !freebieWord.isEmpty()) {
        
                match.append("name", new Document("$regex", freebieWord));
            }
     //*****************查询条件--结束***********************
     arrs.add(new Document("$match",match));
            arrs.add(new Document("$project",
                    new Document("deviceID",
                            new Document("$arrayElemAt", Arrays.asList(new Document("$split", Arrays.asList("$bind", "0000000000")),
                                    new Document("$cond",
                                            new Document("if",
                                                    new Document("$gte", Arrays.asList(new Document("$size",
                                                            new Document("$split", Arrays.asList("$bind", "0000000000"))), 2L)))
                                                    .append("then", 1L)
                                                    .append("else", 0L)))))
                            .append("humanLoc", "$humanLoc")
                            .append("human", "$huamn.humanName")
                            .append("time", 1L)));
    arrs.add(new Document("$lookup",
                    new Document("from", "BindingBoundageLarge")
                            .append("localField", "deviceID")
                            .append("foreignField", "bFlagOut")
                            .append("as", "devices")));
    arrs.add(new Document("$project",
                    new Document("deviceID", 1L)
                            .append("humanLoc", 1L)
                            .append("human",
                                    new Document("$arrayElemAt", Arrays.asList("$human", 0L)))
                            .append("time", 1L)
                            .append("device",
                                    new Document("$arrayElemAt", Arrays.asList("$devices", 0L)))));
    arrs.add(new Document("$project",
                    new Document("deviceID", 1L)
                            .append("humanLoc", 1L)
                            .append("human", "$human")
                            .append("time", 1L)
                            .append("checkFor", "$device.checkFor")
                            .append("device",
                                    new Document("$arrayElemAt", Arrays.asList("$device.bindContent.bindValues", 0L)))));

### 5、实现查询功能 ###

##### （1）连接数据库 mongoDB #####

使用 Maven: org.springframework:spring-beans:5.1.6.RELEASE

@Bean
    	public MongoClient mongoClient() {
        
    		ServerAddress address = new ServerAddress(mongoHost, mongoPort);
    		MongoClientOptions options = new MongoClientOptions.Builder().build();
    		return new MongoClient(address, options);
    	}
    
    	@Bean
    	@Primary
    	public MongoDbFactory mongoDbFactory() {
        
    		return new SimpleMongoDbFactory(mongoClient(), dbName);
    	}
    	
    	@Bean(name = "mongoTemplate")
    	@Primary
    	public MongoTemplate mongoTemplate() {
        
    		return new MongoTemplate(mongoDbFactory());
    	}

##### （2）获取collection 实例 #####

使用 Maven: org.springframework:spring-beans:5.1.6.RELEASE

//获取collection 实例（数据库）
    	@Autowired
        @Qualifier("mongoTemplate")
        private MongoTemplate mongoTemplate;
    
    	//数据转换工具类
     	@Autowired
        MongoConverter mongoConverter;
    	
      //查询数据
      public List<ExportBindingAssetDto> getBounds(String origin, long startDate, long endDate, String queryFieldName, String queryFieldValue, String humanName, String freebieWord, int env) {
        
            MongoTemplate mt =  mongoTemplate;
            MongoCollection<Document> coll = mt.getCollection("bindings");
    
            List<Document> querys = QueryUtil.queryBindAssets(origin, startDate, endDate, queryFieldName, queryFieldValue, humanName, freebieWord);
            AggregateIterable<Document> results = coll.aggregate(querys);//先以document类型查询出来
            
    		//数据类型转化
            MongoCursor<Document> it = results.iterator();
            List<ExportBindingAssetDto> resultList = new ArrayList<>();
            try {
        
                while (it.hasNext()) {
        
                    ExportBindingAssetDto item = mongoConverter.read(ExportBindingAssetDto.class, it.next());
                    resultList.add(item);
                }
            } finally {
        
                it.close();//转化完成一定要关闭 
            }
            return resultList;
        }

类ExportBindingAssetDto.java  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvYmJ5X0Fubnk_size_16_color_FFFFFF_t_70_pic_center]

*  遗留问题：  
    1、数据库存储的时间戳是 string 类型的，暂时没有找到怎么用sql语句直接转化成时间格式的方法  
    2、字符存储长度不定，即前导0（10个连续的0），暂时没有找到怎么用sql语句直接进行判断截取的方法。文中使用的是切割后获取的方法

[https_docs.mongodb.com_manual_reference_operator_aggregation_project_index.html]: https://docs.mongodb.com/manual/reference/operator/aggregation/project/index.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xvYmJ5X0Fubnk_size_16_color_FFFFFF_t_70_pic_center]: /images/20230208/c105811be87a4fcb919fa338c5b17f34.png