开始
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
- 根据 id 和 逻辑删除标记找到对应的用户组
- 根据 userIds 拆分用户组
- 用户组表和用户表关联查询
- 隐藏 id 字段,显示 user 字段 (非必要,为了演示 $project 操作)
- 把 user 字段的第一个数组内容提升到顶层
- 把删除标识置空(非必要,为了演示 $set 操作)
- 对用户的用户名和描述进行模糊查询
- 对数据算总数,并分页
- 把 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);
}