Springboot整合MongoDB系列(五)---LookupOperation关联查询
继续MongoDB系列博客的第五篇，前面记录了使用MongoTemplate和MongoRepository进行查询的操作，今天记录一下mongo的关联查询，各位看到此博客的小伙伴，如有不对的地方请及时通过私信我或者评论此博客的方式指出，以免误人子弟。多谢！目录准备测试数据学生与班级关联(学生为主表) - 多对一不带条件的关联查询查询条件来自主表查询条件来自从表多条件查询模拟下实际情况$unwin
·
继续MongoDB系列博客的第五篇，前面记录了使用MongoTemplate和MongoRepository进行查询的操作，今天记录一下mongo的关联查询，各位看到此博客的小伙伴，如有不对的地方请及时通过私信我或者评论此博客的方式指出，以免误人子弟。多谢！
目录
准备测试数据
关联查询的测试，我们以学生、班级、学校为测试模型，进行一对多、多对一、多对多等情况下的关联查询。
@Test
public void addStudent(){
List<Student> students = new ArrayList<>();
Student student = Student.builder().id(1).username("zhangsan").classId(1).build();
Student student1 = Student.builder().id(2).username("lisi").classId(2).build();
Student student2 = Student.builder().id(3).username("wangwu").classId(2).build();
students.add(student);
students.add(student1);
students.add(student2);
mongotemplate.insertAll(students);
}
@Test
public void addStudentClass(){
List<StudentClass> studentClasses = new ArrayList<>();
StudentClass studentClass = new StudentClass(1,"class one",1);
StudentClass studentClass1 = new StudentClass(2,"class two",2);
studentClasses.add(studentClass);
studentClasses.add(studentClass1);
mongotemplate.insertAll(studentClasses);
}
@Test
public void addSchool(){
List<School> schoolList = new ArrayList<>();
School school = new School(1,"一中");
School school1 = new School(2,"二中");
schoolList.add(school);
schoolList.add(school1);
mongotemplate.insertAll(schoolList);
}学生与班级关联(学生为主表) - 多对一
我们使用 ClassStudentsDto 为返回实体：
@Data
public class ClassStudentsDto {
private Integer id;
private String username;
private Integer classId;
private List<StudentClass> classStudents;
}先看下LookupOperation的使用方法：
LookupOperation lookupOperation = LookupOperation.newLookup()
.from("studentClass")//关联从表名
.localField("classId")//主表中的关联字段
.foreignField("_id")//从表关联的字段
.as("classStudents");//查询结果名但是源码中不推荐使用这种方式，建议使用静态工厂方法Aggregation.lookup(String, String, String, String)而不是直接创建此类的实例，以下我们都会使用静态工厂方法来创建LookupOperation。
不带条件的关联查询
先看一下不带条件的关联查询，我们分别使用Map和对象的方式接收放回结果。
@Test
public void test(){
LookupOperation lookupOperation = LookupOperation.newLookup()
.from("studentClass")//关联从表名
.localField("classId")//主表中的关联字段
.foreignField("_id")//从表关联的字段
.as("classStudents");//查询结果名
// 源码中建议使用静态工厂方法Aggregation.lookup(String, String, String, String)而不是直接创建此类的实例
LookupOperation lookup = Aggregation.lookup("studentClass", "classId", "_id",
"classStudents");
Aggregation aggregation = Aggregation.newAggregation(lookup);
// 使用Map接收结果
AggregationResults<Map> results = mongotemplate.aggregate(aggregation, "student",
Map.class);
System.out.println(results.getMappedResults());
// 使用对象接收结果
AggregationResults<ClassStudentsDto> results1 = mongotemplate.aggregate(aggregation,
"student", ClassStudentsDto.class);
System.out.println(results1.getMappedResults());
}查询条件来自主表
@Test
public void test1(){
LookupOperation lookup = Aggregation.lookup("studentClass", "classId", "_id",
"classStudents");
// 追加查询条件 -- 查询条件来自主表
Criteria criteria = Criteria.where("classId").is(2);
// 将筛选条件放入管道
MatchOperation match = Aggregation.match(criteria);
Aggregation aggregation1 = Aggregation.newAggregation(lookup, match);
AggregationResults<ClassStudentsDto> results2 = mongotemplate.aggregate(aggregation1,
"student", ClassStudentsDto.class);
System.out.println(results2.getMappedResults());
}查询条件来自从表
查询条件来自从表时，不能直接使用从表的属性进行查询，而是要通过LookupOperation的查询结果名(也就是Aggregation.lookup的第四个参数)来获取，即：查询结果名.属性。
@Test
public void test2(){
LookupOperation lookup = Aggregation.lookup("studentClass", "classId", "_id",
"classStudents");
// 追加查询条件 -- 查询条件来自从表 classStudents为上面as 查询结果名
Criteria criteria1 = Criteria.where("classStudents._id").is(2);
MatchOperation match1 = Aggregation.match(criteria1);
Aggregation aggregation2 = Aggregation.newAggregation(lookup, match1);
AggregationResults<ClassStudentsDto> results3 = mongotemplate.aggregate(aggregation2,
"student", ClassStudentsDto.class);
System.out.println(results3.getMappedResults());
}多条件查询
/**
* 多条件追加：
* 1.通过使用 andOperator(Criteria... criteria) 追加多个条件
* 2.通过and(String key) 追加多个条件
*/
@Test
public void test3(){
LookupOperation lookup = Aggregation.lookup("studentClass", "classId", "_id",
"classStudents");
// 多个条件
Criteria criteria2 = Criteria.where("classId").is(2);
Criteria criteria3 = Criteria.where("username").is("lisi");
Criteria criterias = new Criteria().andOperator(criteria2,criteria3);
MatchOperation match2 = Aggregation.match(criterias);
Aggregation aggregation3 = Aggregation.newAggregation(lookup, match2);
AggregationResults<ClassStudentsDto> results4 = mongotemplate.aggregate(aggregation3,
"student", ClassStudentsDto.class);
System.out.println(results4.getMappedResults());
Criteria criteria = new Criteria();
criteria.and("classId").is(2).and("username").is("lisi");
MatchOperation match = Aggregation.match(criteria);
Aggregation aggregation = Aggregation.newAggregation(lookup, match);
AggregationResults<ClassStudentsDto> results = mongotemplate.aggregate(aggregation,
"student", ClassStudentsDto.class);
System.out.println(results.getMappedResults());
}模拟下实际情况
/**
* 通常查询条件要判断一下是否为空 然后再进行条件设置
*/
@Test
public void test4(){
LookupOperation lookup = Aggregation.lookup("studentClass", "classId", "_id",
"classStudents");
Integer classId = 2;
String username = "lisi";
Criteria criteria = new Criteria();
if(classId != null){
criteria.and("classId").is(classId);
}
if(StringUtils.isNotBlank(username)){
criteria.and("username").is(username);
}
MatchOperation match = Aggregation.match(criteria);
Aggregation aggregation = Aggregation.newAggregation(lookup, match);
AggregationResults<ClassStudentsDto> results = mongotemplate.aggregate(aggregation,
"student", ClassStudentsDto.class);
System.out.println(results.getMappedResults());
}$unwind的效果
/**
* $unwind：将文档中的某一个数组类型字段拆分成多条，每条包含数组中的一个值。
* 啥效果啥作用呢？
* 看两张截图
* 没使用unwind前会多嵌套了一层 使用后好比是把里面一层拍扁了 少了一层利于取值
* 其实在使用对象接收查询结果后就不必使用unwind了 对象取值还是很方便的
* 而且使用对象接收后 使用unwind会报类型转换异常的错误
*/
@Test
public void test5(){
LookupOperation lookup = Aggregation.lookup("studentClass", "classId", "_id",
"classStudents");
Criteria criteria = Criteria.where("classId").is(2);
MatchOperation match = Aggregation.match(criteria);
Aggregation aggregation = Aggregation.newAggregation(lookup, match,Aggregation.unwind("classStudents"));
AggregationResults<Map> results = mongotemplate.aggregate(aggregation,
"student", Map.class);
System.out.println(results);
}
学生与班级关联(班级为主表) - 一对多
我们使用 StudentDto为返回实体：
@Data
public class StudentDto {
private Integer id;
private String className;
private List<Student> studentList;
} @Test
public void test6(){
LookupOperation lookup = Aggregation.lookup("student", "_id", "classId",
"studentList");
Aggregation aggregation = Aggregation.newAggregation(lookup);
AggregationResults<StudentDto> results = mongotemplate.aggregate(aggregation, "studentClass"
, StudentDto.class);
System.out.println(results.getMappedResults());
}学生、班级、学校关联(班级为主表) - 多对多
/**
* 多表关联查询时 可能从表会是下一次查询的主表 比如学员关联班机、班级关联学校，班级关联学校时 班级表就成了主表
* 那么在班级表和学校表关联时 localField就不能直接写schoolId 而是通过上一步的结果集来取值
* 如：classStudents.schoolId
* 同样我们想只返回指定字段时，也需要通过上一步的结果集来取想要的属性，
* 如：classStudents.className schoolClasses.schoolName
*/
@Test
public void test7(){
// 学员表关联班级表
LookupOperation lookup = Aggregation.lookup("studentClass", "classId", "_id",
"classStudents");
// 班级表关联学校表
LookupOperation lookup1 = Aggregation.lookup("school", "classStudents.schoolId", "_id",
"schoolClasses");
Aggregation aggregation = Aggregation.newAggregation(lookup, lookup1);
AggregationResults<Map> results = mongotemplate.aggregate(aggregation, "student",
Map.class);
System.out.println(results);
AggregationResults<StudentClassSchoolDto> results1 = mongotemplate.aggregate(aggregation,
"student", StudentClassSchoolDto.class);
// [StudentClassSchoolDto(id=1, username=zhangsan, classStudents=StudentClass(id=1, className=class one, schoolId=1), schoolClasses=School(id=1, schoolName=一中)), StudentClassSchoolDto(id=2, username=lisi, classStudents=StudentClass(id=2, className=class two, schoolId=2), schoolClasses=School(id=2, schoolName=二中)), StudentClassSchoolDto(id=3, username=wangwu, classStudents=StudentClass(id=2, className=class two, schoolId=2), schoolClasses=School(id=2, schoolName=二中))]
System.out.println(results1.getMappedResults());
// 只返回指定字段
ProjectionOperation project = Aggregation.project("id","username","classStudents.className","schoolClasses.schoolName");
Aggregation aggregation1 = Aggregation.newAggregation(lookup, lookup1, project);
AggregationResults<StudentClassSchoolDto> results2 = mongotemplate.aggregate(aggregation1,
"student", StudentClassSchoolDto.class);
// [StudentClassSchoolDto(id=1, username=zhangsan, className=class one, schoolName=一中), StudentClassSchoolDto(id=2, username=lisi, className=class two, schoolName=二中), StudentClassSchoolDto(id=3, username=wangwu, className=class two, schoolName=二中)]
System.out.println(results2.getMappedResults());
}分页查询
@Test
public void test8(){
LookupOperation lookup = Aggregation.lookup("studentClass", "classId", "_id",
"classStudents");
int pageNum = 0;
int pageSize = 2;
Pageable pageable = PageRequest.of(pageNum, pageSize);
SkipOperation skip = Aggregation.skip((long) pageable.getPageNumber() > 0 ? (pageable.getPageNumber() - 1) * pageable.getPageSize() : 0L);
LimitOperation limit = Aggregation.limit(pageable.getPageSize());
Aggregation aggregation = Aggregation.newAggregation(lookup, skip, limit);
// 当前页数据
AggregationResults<ClassStudentsDto> results = mongotemplate.aggregate(aggregation,
"student", ClassStudentsDto.class);
// 总条数
AggregationResults<ClassStudentsDto> results1 =
mongotemplate.aggregate(Aggregation.newAggregation(lookup), "student",
ClassStudentsDto.class);
long total = results1.getMappedResults().size();
Page<ClassStudentsDto> tPage = PageableExecutionUtils.getPage(results.getMappedResults(),
pageable, () -> total);
System.out.println("---getTotalElements---" + tPage.getTotalElements());// 总记录数
System.out.println("---getTotalPages---" + tPage.getTotalPages());// 总页数
System.out.println("---getNumber---" + tPage.getNumber());// 当前页
System.out.println("---getSize---" + tPage.getSize());// 每页大小
System.out.println("---getContent---" + tPage.getContent()); // 数据
System.out.println("---getNumberOfElements---" + tPage.getNumberOfElements());// 当前页的元素数
}
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
7
0- 0
已为社区贡献11条内容
所有评论(0)