java mongodb 多表关联查询，多条件查询，分页，排序

蔚落 2023-07-18 12:56 2阅读 0赞

# 前言： #

由于最近`项目赶`，版本`迭代快`，不知道大BOSS从哪里听别人说MongoDB用来做关系型数据库好，而且速度快，性能高；听到这话的我，立马就反驳了回去：“MongoDB不支持事物”！  
        结果查阅了大量资料，反被大BOSS啪啪打脸…，哎…，最终还是本着为大BOSS服务和学习的态度，技术选型上，关系型数据库由`MySQL`转变成为`MongoDB-4.2`；  
        由于一时半会儿还不太适应MongoDB的一些写法，查阅了大量的资料才搞定MongoDB在java中的实现方式，此次运用到的是spring的一个插件`spring-data-mongodb`，版本号：`2.2.6.RELEASE`；

# 使用spring-data-mongodb对mongo进行联表分页查询 #

*  MongoDB数据库版本（已测试）：`mongodb-3.4.0` 及以上
 *  安装（单机版）教程：[centos7 安装MongoDB 4.2][centos7 _MongoDB 4.2]
 *  安装（集群版）教程：[centos7 单机搭建MongoDB 4.2.5副本集群（支持事务）][centos7 _MongoDB 4.2.5]
 *  spring-data-mongodb maven依赖版本：

<dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-mongodb</artifactId>
        <version>2.2.6.RELEASE</version>
    </dependency>

## 1、一对一：`两张表关联查询，带多条件查询，分页，排序` ##

传统的MongoDB的`SQL语句实现`，如下：

db.shop.aggregate([
        { 
            "$lookup":{ 
                "from":"member",		// 从表表名
                "localField":"userId",	// 如shop被查询主表的userId，相对于member表的外键
                "foreignField":"_id",   // 如member从表的主键_id，相对于member表的主键
                "as":"docs_member"		// 联合查询出的别名，用于多条件查询表明前缀，相当于SQL中的临时表名
            }
        },
        { 
            "$match":{ 
                "shopStatus":1,
                "blockade":{ 
                	// in包含查询
                    "$in":[
                        0,
                        null
                    ]
                },
                "dataFlag":1
            }
        },
        { 
            "$skip":{ 
                "$numberLong":"0"
            }
        },
        { 
            "$limit":{ 
                "$numberLong":"10"
            }
        },
        { 
            "$sort":{ 
                "modifyTime":-1
            }
        }
    ])

Java中实现以上写法，如下：

package /**这里是包名**/;
    
    import com.sszh.mall.shop.entity.ShopDO;
    import org.bson.types.ObjectId;
    import org.springframework.data.domain.Sort;
    import org.springframework.data.mongodb.core.aggregation.Aggregation;
    import org.springframework.data.mongodb.core.aggregation.AggregationResults;
    import org.springframework.data.mongodb.core.query.Criteria;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    import java.util.regex.Pattern;
    
    import org.springframework.data.mongodb.core.MongoTemplate;
    
    public class ShopServiceImpl { 
    
        @Autowired
        protected MongoTemplate mongoTemplate;
    
        // 一对一：两表关联查询
        private List<ShopDO> twoTableQuery(String keyword, int pageNum, int pageSize) { 
            // TODO 店长是否实名认证
            // 构建 Aggregation：添加查询条件
            Aggregation aggregation = Aggregation.newAggregation(
                    // 关联member表
                    Aggregation.lookup(
                            "member",           // 从表表名
                            "userId",           // 如shop被查询主表的userId，相对于member表的外键
                            "_id",              // 如member从表的主键_id，相对于member表的主键
                            "docs_member"       // 联合查询出的别名，用于多条件查询表明前缀，相当于SQL中的临时表名
                    ),
                    // ============================================= 以上内容可举一反三 =============================================
                    // 查询条件
                    null == keyword || "".equals(keyword.trim())
                            ?
                            Aggregation.match(
                                    Criteria.where("shopStatus").is(1)                                                      // 店铺状态: 1:已审核
                                            .and("blockade").in(0, null)                                                    // 店铺是否有效: 0=解封 -1=封锁
                                            .and("dataFlag").is(1)                                                          // 店铺是否有效: 1:有效
                                            .and("docs_member.telephone").is("8615323710096")                               // 添加member表查询条件，如用户手机号，此处可举一反三
                            )
                            :
                            Aggregation.match(
                                    Criteria.where("shopStatus").is(1)
                                            .and("blockade").in(0, null)
                                            .and("dataFlag").is(1)
                                            // 根据店铺名称模糊搜索
                                            .andOperator(Criteria.where("shopName").regex(Pattern.compile(keyword, Pattern.CASE_INSENSITIVE)))
                            ),
                    // 分页：页码
                    Aggregation.skip(Long.valueOf(pageNum)),
                    // 分页：条数
                    Aggregation.limit((long) pageSize),
                    // 排序
                    Aggregation.sort(Sort.Direction.DESC,"modifyTime")
            );
            // 执行查询，这里的shop必须是查询的主表名
            AggregationResults<ShopDO> results = mongoTemplate.aggregate(aggregation, "shop", ShopDO.class);
            return results.getMappedResults();
        }

# 补充语法： #

一对多关联查询（`商品表为主表、取商品规格集合`）：

db.mall_goods.aggregate([
       { 
         $lookup:
           { 
             from: "mall_specs",
             localField: "id",
             foreignField: "goods_id",
             as: "inventory_docs"
           }
      }
    ]);

一对多关联查询（`订单表为主表，根据商品ID取商品名称、商品规格集合`）：

db.mall_order.aggregate([
      { 
        $lookup:
          { 
            from: "mall_goods",
            localField: "id",
            foreignField: "goods_id",
            as: "goods"
          }
     },
      { 
        $lookup:
          { 
            from: "mall_specs",
            localField: "goods_id",
            foreignField: "goods_id",
            as: "specs"
          }
     }
    ])

## 2、高级聚合查询：`排序：根据商品【销售量 + 虚拟交易量】之和，进行倒叙排序（DESC）：-1 降序 1 升序； 再根据价格，由小到大排序` ##

传统的MongoDB的`SQL语句实现`，如下：

db.product.aggregate([
        { 
            $project: { 
                _id : 1,
                // ---------------- 部分代码省略 ----------------
                shopId: 1,
                "saleNumCount" : { 
                    $sum: ["$saleNum", "$salesVolumeNum"]
                }
            }
        },
        { 
            $sort: { "saleNumCount" : -1},
            $sort: { "price" : 1}
        }
        // ---------------- 分页代码省略（注意：这整块SQL语句都有先后顺序要求） ----------------
    ])

备注：

*  $project 属性：设置返回指定字段
 *  语句【 \_id : 1 】解释：此处的 1 代表返回，0 代表不返回
 *  语句【 $sort: \{“saleNumCount” : -1\} 】解释：根据统计结果从大到小排序
 *  语句【 $sum: \["$saleNum", “$salesVolumeNum”\] 】解释：计算 【虚拟交易量 + 交易量】，发回字段 saleNumCount

Java中实现以上写法，如下：

package /*包名省略*/
    
    import com.alibaba.fastjson.JSONObject;
    import com.mongodb.BasicDBObject;
    import com.mongodb.client.MongoCollection;
    import com.mongodb.client.MongoCursor;
    import com.sszh.common.core.MongoOperator;
    import com.sszh.mall.product.entity.ProductDO;	/*这个是返回的实体对象，不指定也可以*/
    import com.sszh.mall.product.repository.ProductRepository;
    import com.sszh.mall.shop.entity.ShopDO;
    import com.sszh.mongodb.springdata.BaseMongoRepository;
    import com.sszh.utils.DateUtil;
    import com.sszh.utils.StringUtils;
    import org.bson.Document;
    import org.bson.types.ObjectId;
    import org.springframework.data.mongodb.core.query.Criteria;
    import org.springframework.data.mongodb.core.query.Query;
    import org.springframework.data.mongodb.core.query.Update;
    import org.springframework.stereotype.Repository;
    
    import java.math.BigDecimal;
    import java.util.*;
    
    public class ProductRepositoryImpl implements ProductRepository { 
    
    	@Autowired
        protected MongoTemplate mongoTemplate;
    
        public MongoTemplate getDatastore(){ 
            return mongoTemplate;
        }
    
    	@Override
    	public List<ProductDO> getProductListByWhere(int categoryId,int pageNum,int pageSize) { 
            List<Document> pipeline = new ArrayList<>();
            // 设置返回指定字段，1 返回 0/不指定 不返回
            Document basicDBObject = new Document();
            basicDBObject.put("_id", 1);
            basicDBObject.put("shopId", 1);
            basicDBObject.put("categoryId", 1);
            basicDBObject.put("shieldSearch", 1);
            basicDBObject.put("productName", 1);
            basicDBObject.put("productPic", 1);
            basicDBObject.put("price", 1);
            basicDBObject.put("fare", 1);
            basicDBObject.put("profit", 1);
            basicDBObject.put("warnStock", 1);
            basicDBObject.put("productStock", 1);
            basicDBObject.put("isSale", 1);
            basicDBObject.put("isBest", 1);
            basicDBObject.put("productDescTitle", 1);
            basicDBObject.put("productDescEnd", 1);
            basicDBObject.put("saleNum", 1);
            basicDBObject.put("saleTime", 1);
            basicDBObject.put("dataFlag", 1);
            basicDBObject.put("praiseNum", 1);
            basicDBObject.put("forwardNum", 1);
            basicDBObject.put("attributes", 1);
            basicDBObject.put("forwardCommission", 1);
            basicDBObject.put("createTime", 1);
            basicDBObject.put("modifyTime", 1);
            basicDBObject.put("salesVolumeNum", 1);
            // 计算 【虚拟交易量 + 交易量】之和，返回字段 saleNumCount
            basicDBObject.put("saleNumCount", new BasicDBObject("$sum", Arrays.asList("$saleNum", "$salesVolumeNum")));
            Document project = new Document(MongoOperator.PROJECT, basicDBObject);
            pipeline.add(project);
            // 排序：根据商品【销售量 + 虚拟交易量】之和，进行倒叙排序（DESC）：-1 降序 1 升序； 再根据价格，由小到大排序
            basicDBObject = new Document();
            basicDBObject.put("saleNumCount", -1);
            basicDBObject.put("price", 1);
            Document sort = new Document(MongoOperator.SORT, basicDBObject);
            pipeline.add(sort);
            // 查询条件
            basicDBObject = new Document();
            basicDBObject.put("isSale", 1);		//查询条件，状态是否可用
            basicDBObject.put("dataFlag", new BasicDBObject(MongoOperator.NE, -1));		//商品是否被删除
            basicDBObject.put("shieldSearch", new BasicDBObject(MongoOperator.NE, 1));	//商品是否可以搜索
            Document match = new Document(MongoOperator.MATCH, basicDBObject);		//将查询条件添加到match中
            pipeline.add(match);
            // 分页：开始位置
            Document skip = new Document(MongoOperator.SKIP, (pageNum * pageSize));
            pipeline.add(skip);
            // 分页：结束位置
            Document limit = new Document(MongoOperator.LIMIT, ((pageNum + 1) * pageSize));
            pipeline.add(limit);
            // 执行聚合查询操作：获取数据库连接、指定表名：product，发回结果集：cursor 
            final MongoCursor<Document> cursor = getDatastore().getCollection("product").aggregate(pipeline).iterator();
            // 处理结果集
            List<ProductDO> list = new ArrayList<>();
            ProductDO productDO;
            while (cursor.hasNext()) { 
                Document dbObject = cursor.next();
                productDO = JSONObject.toJavaObject(JSONObject.parseObject(JSONObject.toJSONString(dbObject)), ProductDO.class);
                productDO.setId((ObjectId)dbObject.get("_id"));
                list.add(productDO);
            }
            return list;
        }
    
    
    
    }

mongodb 关键词常量类：

package com.sszh.common.core;
    
    public final class MongoOperator { 
    
        // id
        public static final String ID = "_id";
    
        // query condition
        public static final String GT = "$gt";
        public static final String GTE = "$gte";
        public static final String LT = "$lt";
        public static final String LTE = "$lte";
        public static final String NE = "$ne";
        public static final String IN = "$in";
        public static final String NIN = "$nin";
        public static final String MOD = "$mod";
        public static final String ALL = "$all";
        public static final String SLICE = "$slice";
        public static final String SIZE = "$size";
        public static final String EXISTS = "$exists";
        public static final String WHERE = "$where";
        public static final String REGEX ="$regex";
    
        // query logic
        public static final String AND = "$and";
        public static final String OR = "$or";
    
        // 2d and geo
        public static final String NEAR = "$near";
        public static final String WITHIN = "$within";
        public static final String CENTER = "$center";
        public static final String BOX = "$box";
    
        // update
        public static final String SET = "$set";
        public static final String UNSET = "$unset";
        public static final String INC = "$inc";
        public static final String MUL = "$mul";
        public static final String PUSH = "$push";
        public static final String PULL = "$pull";
        public static final String EACH = "$each";
        public static final String POP = "$pop";
        public static final String MIN = "$min";
        public static final String MAX = "$max";
        public static final String BIT = "$bit";
    
        // aggregation
        public static final String PROJECT = "$project";
        public static final String MATCH = "$match";
        public static final String LIMIT = "$limit";
        public static final String SKIP = "$skip";
        public static final String UNWIND = "$unwind";
        public static final String GROUP = "$group";
        public static final String SORT = "$sort";
    
    }

参考文献：  
spring-data-mongodb官方参考文档：  
[https://docs.spring.io/spring-data/mongodb/docs/3.0.0.RELEASE/reference/html/\#mongodb.repositories.queries.aggregation][https_docs.spring.io_spring-data_mongodb_docs_3.0.0.RELEASE_reference_html_mongodb.repositories.queries.aggregation]

mongoTemplate.aggregate() 聚合查询，关联查询：  
[https://blog.csdn.net/C18298182575/article/details/100698885][https_blog.csdn.net_C18298182575_article_details_100698885]

使用spring-data-mongodb对mongo进行联表分页查询：  
[https://blog.csdn.net/qazwsx081/article/details/97897187][https_blog.csdn.net_qazwsx081_article_details_97897187]

Spring Data Mongodb多表关联查询：  
[https://blog.csdn.net/zhang135123/article/details/85273957][https_blog.csdn.net_zhang135123_article_details_85273957]

SpringBoot整合MongoDB实现聚合查询（多表联查）以及一套简单CRUD：  
[https://blog.csdn.net/weixin\_44530530/article/details/91901631][https_blog.csdn.net_weixin_44530530_article_details_91901631]

MongoDB的多表关联查询：  
[https://blog.csdn.net/watersprite\_ct/article/details/78500997][https_blog.csdn.net_watersprite_ct_article_details_78500997]

Spring Data Mongodb多表关联查询：  
[https://blog.csdn.net/zhang135123/article/details/85273957][https_blog.csdn.net_zhang135123_article_details_85273957]

Java使用mongodb进行数据存储及多表关联,多条件查询：  
[https://blog.csdn.net/lchq1995/article/details/102586243][https_blog.csdn.net_lchq1995_article_details_102586243]

Java mongodb复杂多条件查询：  
[https://blog.csdn.net/xiaTianCsDN/article/details/96116338?depth\_1-utm\_source=distribute.pc\_relevant.none-task-blog-BlogCommendFromBaidu-1&utm\_source=distribute.pc\_relevant.none-task-blog-BlogCommendFromBaidu-1][https_blog.csdn.net_xiaTianCsDN_article_details_96116338_depth_1-utm_source_distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1_utm_source_distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1]

Spring Data MongoDB-参考文档：  
[https://docs.spring.io/spring-data/mongodb/docs/2.2.3.RELEASE/reference/html/\#mapping-usage][https_docs.spring.io_spring-data_mongodb_docs_2.2.3.RELEASE_reference_html_mapping-usage]

注：以上内容仅提供参考和交流，请勿用于商业用途，如有侵权联系本人删除！

--------------------

## 持续更新中… ##

如有对思路不清晰或有更好的解决思路，欢迎与本人交流，微信：seesun2012（`非紧急项目请加QQ群解答`），QQ群：273557553  
你遇到的问题是小编创作灵感的来源！

--------------------

[centos7 _MongoDB 4.2]: https://blog.csdn.net/seesun2012/article/details/105467308
[centos7 _MongoDB 4.2.5]: https://blog.csdn.net/seesun2012/article/details/106038925
[https_docs.spring.io_spring-data_mongodb_docs_3.0.0.RELEASE_reference_html_mongodb.repositories.queries.aggregation]: https://docs.spring.io/spring-data/mongodb/docs/3.0.0.RELEASE/reference/html/#mongodb.repositories.queries.aggregation
[https_blog.csdn.net_C18298182575_article_details_100698885]: https://blog.csdn.net/C18298182575/article/details/100698885
[https_blog.csdn.net_qazwsx081_article_details_97897187]: https://blog.csdn.net/qazwsx081/article/details/97897187
[https_blog.csdn.net_zhang135123_article_details_85273957]: https://blog.csdn.net/zhang135123/article/details/85273957
[https_blog.csdn.net_weixin_44530530_article_details_91901631]: https://blog.csdn.net/weixin_44530530/article/details/91901631
[https_blog.csdn.net_watersprite_ct_article_details_78500997]: https://blog.csdn.net/watersprite_ct/article/details/78500997
[https_blog.csdn.net_lchq1995_article_details_102586243]: https://blog.csdn.net/lchq1995/article/details/102586243
[https_blog.csdn.net_xiaTianCsDN_article_details_96116338_depth_1-utm_source_distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1_utm_source_distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1]: https://blog.csdn.net/xiaTianCsDN/article/details/96116338?depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1&utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1
[https_docs.spring.io_spring-data_mongodb_docs_2.2.3.RELEASE_reference_html_mapping-usage]: https://docs.spring.io/spring-data/mongodb/docs/2.2.3.RELEASE/reference/html/#mapping-usage