背景:公司接口文档迁移到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
}
}
}
])