文章目录

开始

pom 配置 文件

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

springboot 版本：2.5.x
配置 application.yml
uri实例：mongodb://root:password@192.168.101.01:30017/database

spring:
  data:
    mongodb:
      uri:  mongodb://[用户名]:[密码]@[ip]:[端口]/[数据库]

springboot 版本：2.7.x
配置 application.yml

spring:
  data:
    mongodb:
      host: 192.168.101.01
      port: 30017
      database: test
      authentication-database: admin
      username: root
      password: password

 

数据准备

MongoDB 非常适合实时的插入，更新与查询，但对于关联查询在灵活性上不如 MySQL，编码起来学习成本比较高，所以特地编写此文，一起学习如何把常用的MongoDB sql 用 JAVA 编写

需求：根据用户组的 id，查询用户组里面所有用户的详情

表结构

用户表

@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
@Document(collection = "user")
public class UserPO extends BasePO {

    private String userName;

    private String userDes;

    private Long userNum;

}

用户组表

@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
@Document(collection = "user_group")
public class UserGroupPO extends BasePO {

	@ApiModelProperty(value = "用户组名称")
    private String groupName;

	@ApiModelProperty(value = "用户组描述")
    private String groupDes;

	@ApiModelProperty(value = "用户id")
    private List<Long> userIds;
}

基础表

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class BasePO implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键")
    @Id
    private Long id;

    @ApiModelProperty(value = "逻辑删除标记")
    private String deleteFlag;
}

查询条件

@Data
public class UserQuery {

    @ApiModelProperty(value = "关键字")
    String keyword = "";

    @ApiModelProperty(value = "用户组id")
    private Long id;

	@ApiModelProperty(value = "分页第几页")
    private Integer page;
    
    @ApiModelProperty(value = "分页大小")
    private Integer size;
}

查询结果

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserPageVO {

    @ApiModelProperty(value = "分页总数")
    Long total;

    @ApiModelProperty(value = "分页数据")
    List<UserPO> records;
}

 

原生SQL

  1. 根据 id 和 逻辑删除标记找到对应的用户组
  2. 根据 userIds 拆分用户组
  3. 用户组表和用户表关联查询
  4. 隐藏 id 字段，显示 user 字段 （非必要，为了演示 $project 操作）
  5. 把 user 字段的第一个数组内容提升到顶层
  6. 把删除标识置空（非必要，为了演示 $set 操作）
  7. 对用户的用户名和描述进行模糊查询
  8. 对数据算总数，并分页
  9. 把 metadata 字段的第一个数组内容提升到顶层，组装成 UserPageVO 结构
db.getCollection("user_group").aggregate([{
    "$match": {
        "$and": [{
            "_id": NumberLong("4523281346310580824")
        }, {
            "deleteFlag": "N"
        }]
    }
}, {
    "$unwind": "$userIds"
}, {
    "$lookup": {
        "from": "user",
        "localField": "userIds",
        "foreignField": "_id",
        "as": "user"
    }
}, {
    "$project": {
        "user": 1,
        "_id": 0
    }
}, {
    "$replaceRoot": {
        "newRoot": {
            "$arrayElemAt": ["$user", 0]
        }
    }
}, {
    "$set": {
        "deleteFlag": ""
    }
}, {
    "$match": {
        "$or": [{
            "userName": {
                "$regex": ".*40.*"
            }
        }, {
            "userDes": {
                "$regex": ".*1.*"
            }
        }]
    }
}, {
    "$facet": {
        "metadata": [{
            "$count": "total"
        }],
        "records": [{
            "$skip": 0
        }, {
            "$limit": 10
        }]
    }
}, {
    "$replaceRoot": {
        "newRoot": {
            "$mergeObjects": [{
                "$arrayElemAt": ["$metadata", 0]
            }, "$$ROOT"]
        }
    }
}])

 

mongoTemplate

Aggregation 的静态方法

  • match：根据条件查询
  • unwind：拆分数据
  • lookup：关联查询
  • project：显示或隐藏字段
  • replaceRoot：移动数据至顶层
  • set：更改字段的值
  • Criteria：查询条件
  • facet：组装数据
  • count：计算总数
  • skip：分页
AggregationResults<UserPageVO> results = mongoTemplate.aggregate(newAggregation(
                /**
                 * 查找特定用户组id的用户
                 */
                match(new Criteria().andOperator(
                        Criteria.where("_id").is(userQuery.getId()),
                        Criteria.where("deleteFlag").is("N"))),

                /**
                 * 把 userIds 字段拆开为一个个文档，因为要排序所以提前拆
                 */
                unwind("$userIds"),

                /**
                 * 根据user_group表的userIds left join user表为一个新的user字段
                 */
                lookup("user", "userIds", "_id", "user"),

                /**
                 * 只要联合查询后的user字段，同时隐藏id字段
                 */
                project("user").andExclude("_id"),

                /**
                 * 提取user字段里的值作为根内容
                 */
                replaceRoot().withValueOf(ArrayOperators.ArrayElemAt.arrayOf("user").elementAt(0)),

                /**
                 *去除deleteFlag信息
                 */
                SetOperation.builder().set("deleteFlag").toValue(""),

                /**
                 * 根据keyword筛选数据
                 */
                match(new Criteria().orOperator(
                        Criteria.where("userName").regex(".*" + userQuery.getKeyword() + ".*"),
                        Criteria.where("userDes").regex(".*" + userQuery.getKeyword() + ".*"))),

                /**
                 * 分页以及组织成 UserPageVO 结构返回数据
                 */
                facet(count()
                        .as("total")).as("metadata")
                        .and(skip((userQuery.getPage().longValue() - 1) * userQuery.getSize().longValue()),
                                limit(userQuery.getSize().longValue())
                        ).as("records"),

                /**
                 * metadata 数组的一个数据提到顶级，即把 total 提到顶级
                 */
                replaceRoot().withValueOf(
                        ObjectOperators.valueOf(
                                ArrayOperators.ArrayElemAt.arrayOf("metadata").elementAt(0)
                        ).mergeWith(ROOT)
                )
        ), "user_group", UserPageVO.class);
        UserPageVO vo = results.getUniqueMappedResult();

 

新建 AggregationOperation

  • matchOperation：根据条件查询
  • unwindOperation：拆分数据
  • lookupOperation：关联查询
  • projectOperation：显示或隐藏字段
  • replaceRootOperation：移动数据至顶层
  • seOperationt：更改字段的值
  • CriteriaOperation：查询条件
  • facetOperation：组装数据
  • countOperation：计算总数
  • skipOperation：分页
List<AggregationOperation> operations = new ArrayList<>();

       Criteria criteriaGroup = new Criteria();
       criteriaGroup.andOperator(
               Criteria.where("_id").is(userQuery.getId()),
               Criteria.where("deleteFlag").is("N"));
       MatchOperation matchGroup = new MatchOperation(criteriaGroup);

       UnwindOperation unwindUser = new UnwindOperation(Fields.field("userIds"), true);

       Field fromEntityType = Fields.field("user");
       Field localFieldEntityType = Fields.field("userIds");
       Field foreignFieldEntityType = Fields.field("_id");
       Field asEntityType = Fields.field("user");
       LookupOperation lookUpUser = new LookupOperation(fromEntityType, localFieldEntityType,
               foreignFieldEntityType, asEntityType);

       Fields projectUserFields = Fields.fields("user");
       ProjectionOperation projectGroup = new ProjectionOperation(projectUserFields);
       projectGroup.andExclude("_id");

       AggregationExpression userAggregationExpression = ArrayOperators.ArrayElemAt.arrayOf("user").elementAt(0);
       ReplaceRootOperation replaceRootUser = new ReplaceRootOperation(userAggregationExpression);

       SetOperation set = new SetOperation("deleteFlag", "");

       Criteria criteriaUser = new Criteria();
       criteriaUser.orOperator(
               Criteria.where("userName").regex(".*" + userQuery.getKeyword() + ".*"),
               Criteria.where("userDes").regex(".*" + userQuery.getKeyword() + ".*"));
       MatchOperation matchUser = new MatchOperation(criteriaUser);

       List<AggregationOperation> dataOperations = new ArrayList<>();
       SkipOperation skipOperation = new SkipOperation((userQuery.getPage() - 1) * userQuery.getSize());
       LimitOperation limitOperation = new LimitOperation(userQuery.getSize());
       dataOperations.add(skipOperation);
       dataOperations.add(limitOperation);

       CountOperation countOperation = new CountOperation("total");

       FacetOperation facet = new FacetOperation().and(countOperation).as("metadata")
               .and(dataOperations.toArray(new AggregationOperation[dataOperations.size()])).as("records");

       AggregationExpression totalArray = ArrayOperators.ArrayElemAt.arrayOf("metadata").elementAt(0);
       ObjectOperators.MergeObjects om = ObjectOperators.valueOf(totalArray).mergeWith(ROOT);
       ReplaceRootOperation replaceRootTotal = new ReplaceRootOperation(om);

       operations.add(matchGroup);
       operations.add(unwindUser);
       operations.add(lookUpUser);
       operations.add(projectGroup);
       operations.add(replaceRootUser);
       operations.add(set);
       operations.add(matchUser);
       operations.add(facet);
       operations.add(replaceRootTotal);

       Aggregation aggregation = Aggregation.newAggregation(operations);
       AggregationResults<UserPageVO> results = mongoTemplate.aggregate(aggregation, "user_group",
               UserPageVO.class);
       UserPageVO vo = results.getUniqueMappedResult();

mongoClient

  • 新建 Document 列表
  • 每个对象，即{}，都需要新建一个 Document
  • 多个对象用 append 连接起来
  • 数组用 Arrays.asList() 包括起来
  • 最后生成的对象 MongoCursor 是一个迭代器，需要从其中获取结果
        List<Document> documents = new ArrayList<>();

        documents.add(new Document("$match",
                new Document("$or",
                        Arrays.asList(
                                new Document("deleteFlag", "N"),
                                new Document("_id", userQuery.getId())
                        )
                )
        ));

        documents.add(new Document("$unwind", "$userIds"));

        documents.add(new Document("$lookup",
                new Document(
                        new Document("from", "user")
                                .append("localField", "userIds")
                                .append("foreignField", "_id")
                                .append("as", "user")
                )
        ));

        documents.add(new Document("$project",
                new Document("user", 1)
                        .append("_id", 0)
        ));

        documents.add(new Document("$replaceRoot",
                new Document("newRoot", new Document("$arrayElemAt", Arrays.asList("$user", 0)))
        ));


        documents.add(new Document("$set", new Document("deleteFlag", "")));

        documents.add(new Document("$match",
                new Document("$or",
                        Arrays.asList(
                                new Document("userName", new Document("$regex", userQuery.getKeyword())),
                                new Document("userDes", new Document("$regex", userQuery.getKeyword()))
                        )
                )
        ));

        documents.add(new Document("$facet",
                        new Document("metadata",
                                Arrays.asList(new Document("$count", "total"))
                        ).append("records",
                                Arrays.asList(
                                        new Document("$skip", 0),
                                        new Document("$limit", 10)
                                )
                        )
                )
        );

        documents.add(new Document("$replaceRoot",
                        new Document("newRoot",
                                new Document("$mergeObjects",
                                        Arrays.asList(
                                                new Document("$arrayElemAt",
                                                        Arrays.asList("$metadata", 0)
                                                ),
                                                "$$ROOT"
                                        )
                                )
                        )
                )
        );

        System.out.println(documents);
        MongoCursor<Document> x =
                mongoClient.getDatabase("test").getCollection("user_group").aggregate(documents).iterator();

        UserPageVO vo = new UserPageVO();
        while (x.hasNext()) {
            String document = x.next().toJson();
            vo = JSONUtil.toBean(document, UserPageVO.class);
        }
# mongodb # java # 数据库
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

从原始边列表到邻接矩阵Python实现图数据处理的完整指南

avatar 华为云开发者联盟
cover

解锁HDC 2024之旅：从购票到报名，全程攻略

avatar 华为云开发者联盟
cover

华为云云原生FinOps解决方案，释放云原生最大价值

avatar 华为云开发者联盟