博客

背景：公司接口文档迁移到Yapi上，领导想要统计不同员工负责得接口数量。使用springboot 连接yapi的mongodb数据库，写了几个接口用于统计数量。

1.按项目和负责人统计，获取不同项目下不同负责人负责的接口数量；

2.按负责人统计，获取不同负责人负责的接口总数量；

3.根据项目id和负责人id，获取该负责人在该项目下，负责的接口信息列表；

一、springboot 配置 mongodb

1.maven导入mongodb依赖

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

2.配置mongodb数据库

在 application.ymal 文件中新增

spring:
    data:
        mongodb:
            host: localhost
            port: 27017
            database: yapi

二、使用 MongoTemplate 进行查询

使用到的返回参数封装类

package com.ylx.yapi.vo;

/**
* 接口信息
*/
public class InterfaceResponse {
    String _id;

    String status;

    String method;

    String title;

    String path;

    String add_time;

    UserResponse userInfo;

    ProjectResponse projectInfo;

    public String get_id() {
        return _id;
    }

    public void set_id(String _id) {
        this._id = _id;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public String getMethod() {
        return method;
    }

    public void setMethod(String method) {
        this.method = method;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getPath() {
        return path;
    }

    public void setPath(String path) {
        this.path = path;
    }

    public String getAdd_time() {
        return add_time;
    }

    public void setAdd_time(String add_time) {
        this.add_time = add_time;
    }

    public UserResponse getUserInfo() {
        return userInfo;
    }

    public void setUserInfo(UserResponse userInfo) {
        this.userInfo = userInfo;
    }

    public ProjectResponse getProjectInfo() {
        return projectInfo;
    }

    public void setProjectInfo(ProjectResponse projectInfo) {
        this.projectInfo = projectInfo;
    }
}

package com.ylx.yapi.vo;

import lombok.Data;

/**
* 用户信息（用户id，用户名称）
*/
@Data
public class UserResponse {

    String _id;

    String username;

}

package com.ylx.yapi.vo;

import lombok.Data;

/**
* 项目信息（项目id，项目名称）
*/
@Data
public class ProjectResponse {
    String _id;

    String name;
}

service中查询

package com.ylx.yapi.service.impl;

import com.ylx.common.Result;
import com.ylx.yapi.service.YapiService;
import com.ylx.yapi.vo.InterfaceResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.*;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;

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

import static org.springframework.data.mongodb.core.aggregation.Aggregation.*;

@Service
public class YapiServiceImpl implements YapiService {

    @Autowired
    private MongoTemplate mongoTemplate;
    
    /**
     * 根据项目id，用户id，获取指定项目指定人员下的接口信息
     *
     * @param projectId 项目id
     * @param uid       用户id
     * @return
     */
    @Override
    public Result interfaceInfo(Integer projectId, Integer uid) {
        Result result = new Result();

		// 关联 project 集合（表）
        LookupOperation lookupOperation1 = LookupOperation.newLookup()
                .from("project") // 要关联的 集合名
                .localField("project_id") // interface 集合中的 项目id
                .foreignField("_id") // project 集合中的主键
                .as("projectInfo"); // 别名

        LookupOperation lookupOperation2 = LookupOperation.newLookup()
                .from("user")
                .localField("uid") // interface 集合中的 用户id
                .foreignField("_id") // user 集合中的主键
                .as("userInfo");
		// 要显示的字段
        ProjectionOperation project = Aggregation.project( "project_id", "uid", "title", "path", "status", "method",
                "projectInfo.name", "userInfo.username");

		// Aggregation.unwind("projectInfo") 一条接口记录对应的项目信息列表展开成多条，这里只有一条记录；Aggregation.unwind("userInfo") 同理
		// Aggregation.match 相当于mysql的 WHERE 条件
        Aggregation aggregation = newAggregation(
                lookupOperation1,  Aggregation.unwind("projectInfo"),
                lookupOperation2, Aggregation.unwind("userInfo"),
                Aggregation
                .match(new Criteria("uid").is(uid).and("project_id").is(projectId))
        );

		// 注意 mongoTemplate.aggregate 这里的主表（集合）为 interface，查询结果转成封装的返回参数
        AggregationResults<InterfaceResponse> aggregationResults = mongoTemplate
                .aggregate(aggregation, "interface", InterfaceResponse.class);

        List<InterfaceResponse> interfaceList = aggregationResults.getMappedResults();

        System.out.println("interfaceList :====>>>" + interfaceList);
        result.setCode("1");
        result.setData(interfaceList);
        result.setMsg("success");
        return result;
    }

    /**
     * 按员工统计，每个员工负责得接口数量
     * @return
     */
    @Override
    public Result groupByUser(){
        Result result = new Result();

        LookupOperation lookupOperation2 = LookupOperation.newLookup()
                .from("user")
                .localField("uid")
                .foreignField("_id")
                .as("userInfo");


        GroupOperation groupOperation = Aggregation.group( "uid").count()
                .as("apiCount")
                .first("userInfo.username").as("userName");
        ProjectionOperation project = Aggregation.project(  "uid",
                 "userInfo.username");

        Aggregation agg = Aggregation.newAggregation( project,
                lookupOperation2, Aggregation.unwind("userInfo"),
                groupOperation );

        AggregationResults<Map> results = mongoTemplate
                .aggregate(agg, "interface", Map.class);

        result.setCode("1");
        result.setData(results.getMappedResults());
        result.setMsg("success");
        return result;
    }


    /**
     * 按项目和人员统计，不同项目下不同员工负责得接口数量
     * @return
     */
    @Override
    public Result groupByProjectAndUser(){
        Result result = new Result();

        LookupOperation lookupOperation1 = LookupOperation.newLookup()
                .from("project")
                .localField("project_id")
                .foreignField("_id")
                .as("projectInfo");

        LookupOperation lookupOperation2 = LookupOperation.newLookup()
                .from("user")
                .localField("uid")
                .foreignField("_id")
                .as("userInfo");


        GroupOperation groupOperation = Aggregation.group("project_id", "uid").count()
                .as("apiCount")
                .first("projectInfo.name").as("projectName")
                .first("userInfo.username").as("userName");
        ProjectionOperation project = Aggregation.project( "project_id", "uid",
                "projectInfo.name", "userInfo.username");

        Aggregation agg = Aggregation.newAggregation( project,
                lookupOperation1,  Aggregation.unwind("projectInfo"),
                lookupOperation2, Aggregation.unwind("userInfo"),
                 groupOperation );

        AggregationResults<Map> results = mongoTemplate
                .aggregate(agg, "interface", Map.class);

        result.setCode("1");
        result.setData(results.getMappedResults());
        result.setMsg("success");
        return result;
    }
}

mongodb 查询语句

按项目id和用户id分组查询

db.interface.aggregate([
    {
        $lookup: 
        {
            from: "user",
            localField: "uid",
            foreignField: "_id",
            as: "userInfo"
        }
    },
    {
        $unwind: "$userInfo"
    },
    {
        $lookup: 
        {
            from: "project",
            localField: "project_id",
            foreignField: "_id",
            as: "projectInfo"
        }
    },
    {
        $unwind: "$projectInfo"
    },
    {
        $group: 
        {
            _id: {
                project_id: "$project_id",
                uid: "$uid",
                userName: "$userInfo.username",
                projectName: "$projectInfo.name",
                
            },
            count: {
                $sum: 1
            }
        }
    }
])