1, Preface
(1) Background content
The most common selection component of DAO layer in software application technology architecture is MyBatis. Friends who are familiar with MyBatis know how beautiful MyBatis was in the past. XML files are used to solve the problem of complex database access. Today, the former Dragon Slayer has finally become a dragon. The database access technology based on XML file has become bloated and complex, and the maintenance difficulty has risen sharply.
MybatisPlus encapsulates the common database access, which greatly reduces the dependence on XML files, and developers get a great relief from the bloated XML files.
MybatisPlus does not officially provide a general solution for multi table join query, but join query is a very common demand. There are two requirements for solving connection queries. One is to continue to use MyBatis to provide XML file solutions; Another solution provided in this paper.
In fact, the author strongly recommends to bid farewell to accessing the database through XML and constantly explore new, more friendly and more natural solutions. Now I share the research results of the latest MybatisPlus technology.
<img src="https://www.altitude.xin/typora/image-20211021114957682.png" alt="image-20211021114957682" style="zoom:50%;" />
(2) Scenario description
In order to illustrate the relationship of connection query, here we take students, courses and their relationships as examples.
<img src="https://www.altitude.xin/typora/image-20211020194255298.png" alt="image-20211020194255298" style="zoom:50%;" />
(3) Preliminary preparation
This part requires readers to master the following contents: Lambda expressions, especially method references; Functional interface; Stream operation and so on, otherwise it will be a little difficult to understand.
<img src="https://www.altitude.xin/typora/image-20211021135113431.png" alt="image-20211021135113431" style="zoom:50%;" />
For the mapping relationship between entity class and Vo, the author creatively introduces a special constructor and makes rational use of inheritance relationship, which greatly facilitates developers to complete the transformation from entity class to Vo.
Null pointer exceptions are ignored and not handled with the help of Optional Class implementation, details step by step What's new in Java 8 see.
2, One to one query
The most typical application scenario of one-to-one query is to replace id with name, such as userId with userName.
(1) Query a single record
Querying a single record means that there is only one record with a return value. It is usually a query result returned with a unique index as a condition.
1. Sample code
/**
* Query individual student information (one student corresponds to one department)
*/
public UserVo getOneUser(Integer userId) {
LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery(User.class)
.eq(User::getUserId, userId);
// Query user information first
User user = userMapper.selectOne(wrapper);
// Convert to Vo
UserVo userVo = Optional.ofNullable(user).map(UserVo::new).orElse(null);
// Query information from other tables and then encapsulate it into Vo
Optional.ofNullable(userVo).ifPresent(this::addDetpNameInfo);
return userVo;
}Supplementary information of subsidiary table
/**
* Supplementary department name information
*/
private void addDetpNameInfo(UserVo userVo) {
LambdaQueryWrapper<Dept> wrapper = Wrappers.lambdaQuery(Dept.class)
.eq(Dept::getDeptId, userVo.getDeptId());
Dept dept = deptMapper.selectOne(wrapper);
Optional.ofNullable(dept).ifPresent(e -> userVo.setDeptName(e.getDeptName()));
}2. Theoretical analysis
Querying a single entity is divided into two steps: querying the main table data according to conditions (null pointer exception needs to be handled); Encapsulate Vo and query attached table data.
The query result (VO) has only one record, and the database needs to be queried twice. The time complexity is O(1).
(2) Query multiple records
Querying multiple records means that the query result is a list, which usually refers to the query result based on the general index.
1. Sample code
/**
* Batch query of student information (one student corresponds to one department)
*/
public List<UserVo> getUserByList() {
// Query user information first (in the form of list)
List<User> user = userMapper.selectList(Wrappers.emptyWrapper());
List<UserVo> userVos = user.stream().map(UserVo::new).collect(toList());
// This step can have more than one
addDeptNameInfo(userVos);
return userVos;
}Supplementary information
private void addDeptNameInfo(List<UserVo> userVos) {
// Extract user userId to facilitate batch query
Set<Integer> deptIds = userVos.stream().map(User::getDeptId).collect(toSet());
// Query deptName according to deptId (judge whether it is not empty before querying)
List<Dept> dept = deptMapper.selectList(Wrappers.lambdaQuery(Dept.class).in(Dept::getDeptId, deptIds));
// Construct mapping relationship to facilitate matching deptId and deptName
Map<Integer, String> hashMap = dept.stream().collect(toMap(Dept::getDeptId, Dept::getDeptName));
// Encapsulate Vo and add it to the collection (key content)
userVos.forEach(e -> e.setDeptName(hashMap.get(e.getDeptId())));
}2. Theoretical analysis
First query the list record containing id, extract id from the result set and convert it into batch query statements, then access the database and parse name from the second call result set.
The query result (VO) has multiple records, but only calls the database twice, and the time complexity is O(1).
(3) Query multiple records (pagination)
The idea of paging query entity is similar to that of query list, with additional one-step paging generic conversion.
1. Sample code
/**
* Paging query of student information (one student corresponds to one department)
*/
public IPage<UserVo> getUserByPage(Page<User> page) {
// Query user information first
IPage<User> xUserPage = userMapper.selectPage(page, Wrappers.emptyWrapper());
// Initialize Vo
IPage<UserVo> userVoPage = xUserPage.convert(UserVo::new);
if (userVoPage.getRecords().size() > 0) {
addDeptNameInfo(userVoPage);
}
return userVoPage;
}Query supplementary information
private void addDeptNameInfo(IPage<UserVo> userVoPage) {
// Extract user userId to facilitate batch query
Set<Integer> deptIds = userVoPage.getRecords().stream().map(User::getDeptId).collect(toSet());
// Query deptName according to deptId
List<Dept> dept = deptMapper.selectList(Wrappers.lambdaQuery(Dept.class).in(Dept::getDeptId, deptIds));
// Construct mapping relationship to facilitate matching deptId and deptName
Map<Integer, String> hashMap = dept.stream().collect(toMap(Dept::getDeptId, Dept::getDeptName));
// Add query supplementary information to Vo
userVoPage.convert(e -> e.setDeptName(hashMap.get(e.getDeptId())));
}The convert method in the IPage interface can be modified on the original instance.
2. Theoretical analysis
First query the list record containing id, extract id from the result set and convert it into batch query statements, then access the database and parse name from the second call result set.
The query result (VO) has multiple records, but only calls the database twice, and the time complexity is O(1).
3, One to many query
The most common scenario of one to many query is to query the student information contained in the Department. Because a Department corresponds to multiple students and each student corresponds to a department, it is called one to many query.
(1) Query a single record
1. Sample code
/**
* Query a single department (one department has multiple users)
*/
public DeptVo getOneDept(Integer deptId) {
// Query Department basic information
LambdaQueryWrapper<Dept> wrapper = Wrappers.lambdaQuery(Dept.class).eq(Dept::getDeptId, deptId);
DeptVo deptVo = Optional.ofNullable(deptMapper.selectOne(wrapper)).map(DeptVo::new).orElse(null);
Optional.ofNullable(deptVo).ifPresent(this::addUserInfo);
return deptVo;
}Supplementary additional information
private void addUserInfo(DeptVo deptVo) {
// Query student list according to deptId of Department
LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery(User.class).eq(User::getDeptId, deptVo.getDeptId());
List<User> users = userMapper.selectList(wrapper);
deptVo.setUsers(users);
}2. Theoretical analysis
The whole process is divided into two stages: query the specified Department information through the primary key in the Department table, query the student information through the Department ID foreign key in the student table, and merge the results to form a return value (Vo).
The whole process of one to many querying a single record requires at most two database queries. The query times are constant and the query time complexity is O(1).
(2) Query multiple records
1. Sample code
/**
* Query multiple departments (one department has multiple users)
*/
public List<DeptVo> getDeptByList() {
// Query department information by criteria
List<Dept> deptList = deptMapper.selectList(Wrappers.emptyWrapper());
List<DeptVo> deptVos = deptList.stream().map(DeptVo::new).collect(toList());
if (deptVos.size() > 0) {
addUserInfo(deptVos);
}
return deptVos;
}Supplementary additional information
private void addUserInfo(List<DeptVo> deptVos) {
// Prepare deptId to facilitate batch query of user information
Set<Integer> deptIds = deptVos.stream().map(Dept::getDeptId).collect(toSet());
// Query user information with batch deptId
List<User> users = userMapper.selectList(Wrappers.lambdaQuery(User.class).in(User::getDeptId, deptIds));
// Key: group users according to deptId
Map<Integer, List<User>> hashMap = users.stream().collect(groupingBy(User::getDeptId));
// Merge results, construct Vo and add set list
deptVos.forEach(e -> e.setUsers(hashMap.get(e.getDeptId())));
}2. Theoretical analysis
The whole process is divided into three stages: query several records from the Department table through the general index; Convert the Department ID into batch query and query the student records from the student table; Group student records by department ID, merge the results and convert them into Vo.
To query multiple records one to many, you need to call the database query twice, the query times are constant, and the query time complexity is O(1).
(3) Query multiple records (pagination)
1. Sample code
/**
* Query department information in pages (one department has multiple users)
*/
public IPage<DeptVo> getDeptByPage(Page<Dept> page) {
// Query department information by criteria
IPage<Dept> xDeptPage = deptMapper.selectPage(page, Wrappers.emptyWrapper());
IPage<DeptVo> deptVoPage = xDeptPage.convert(DeptVo::new);
if (deptVoPage.getRecords().size() > 0) {
addUserInfo(deptVoPage);
}
return deptVoPage;
}Query supplementary information
private void addUserInfo(IPage<DeptVo> deptVoPage) {
// Prepare deptId to facilitate batch query of user information
Set<Integer> deptIds = deptVoPage.getRecords().stream().map(Dept::getDeptId).collect(toSet());
LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery(User.class).in(User::getDeptId, deptIds);
// Query user information with batch deptId
List<User> users = userMapper.selectList(wrapper);
// Key: group users according to deptId
Map<Integer, List<User>> hashMap = users.stream().collect(groupingBy(User::getDeptId));
// Merge results, construct Vo and add set list
deptVoPage.convert(e -> e.setUsers(hashMap.get(e.getDeptId())));
}2. Theoretical analysis
The whole process is divided into three stages: query several records from the Department table through the general index; Convert the Department ID into batch query and query the student records from the student table; Group student records by department ID, merge the results and convert them into Vo.
To query multiple records one to many, you need to call the database query twice, the query times are constant, and the query time complexity is O(1).
4, Many to many query
MybatisPlus implementing many to many query is a challenging task and the most difficult part of connection query.
The problem of many to many query is solved by replacing time with space and using stream operation.
Compared with one to many queries, many to many queries add streaming grouping operations, batch HashMap values and other contents.
<img src="https://www.altitude.xin/typora/image-20211024115903848.png" alt="image-20211024115903848" style="zoom:50%;" />
(1) Query a single record
Querying a single record generally refers to finding a record in a matching table through two query criteria.
1. Sample code
public StudentVo getStudent(Integer stuId) {
// Query student information through primary key
StudentVo studentVo = ConvertUtils.convertObj(getById(stuId), StudentVo::new);
LambdaQueryWrapper<StuSubRelation> wrapper = Wrappers.lambdaQuery(StuSubRelation.class).eq(StuSubRelation::getStuId, stuId);
// Query matching relationship
List<StuSubRelation> stuSubRelations = stuSubRelationMapper.selectList(wrapper);
Set<Integer> subIds = stuSubRelations.stream().map(StuSubRelation::getSubId).collect(toSet());
if (studentVo != null && subIds.size() > 0) {
List<Subject> subList = subjectMapper.selectList(Wrappers.lambdaQuery(Subject.class).in(Subject::getId, subIds));
List<SubjectBo> subBoList = EntityUtils.toList(subList, SubjectBo::new);
HashBasedTable<Integer, Integer, Integer> table = getHashBasedTable(stuSubRelations);
subBoList.forEach(e -> e.setScore(table.get(stuId, e.getId())));
studentVo.setSubList(subBoList);
}
return studentVo;
}2. Theoretical analysis
Multiple to multiple single record query can access the database up to 3 times. First query the student information, then query the matching information between the student and the course, and finally query the course score information. The query time complexity is O(1).
(2) Query multiple records
1. Sample code
public List<StudentVo> getStudentList() {
// Query student information through primary key
List<StudentVo> studentVoList = EntityUtils.toList(list(), StudentVo::new);
// Batch query student ID
Set<Integer> stuIds = studentVoList.stream().map(Student::getId).collect(toSet());
LambdaQueryWrapper<StuSubRelation> wrapper = Wrappers.lambdaQuery(StuSubRelation.class).in(StuSubRelation::getStuId, stuIds);
List<StuSubRelation> stuSubRelations = stuSubRelationMapper.selectList(wrapper);
// Batch query of course ID
Set<Integer> subIds = stuSubRelations.stream().map(StuSubRelation::getSubId).collect(toSet());
if (stuIds.size() > 0 && subIds.size() > 0) {
HashBasedTable<Integer, Integer, Integer> table = getHashBasedTable(stuSubRelations);
List<Subject> subList = subjectMapper.selectList(Wrappers.lambdaQuery(Subject.class).in(Subject::getId, subIds));
List<SubjectBo> subjectBoList = EntityUtils.toList(subList, SubjectBo::new);
Map<Integer, List<Integer>> map = stuSubRelations.stream().collect(groupingBy(StuSubRelation::getStuId, mapping(StuSubRelation::getSubId, toList())));
for (StudentVo studentVo : studentVoList) {
// Get course list
List<SubjectBo> list = ListUtils.select(subjectBoList, e -> emptyIfNull(map.get(studentVo.getId())).contains(e.getId()));
// Fill fraction
list.forEach(e -> e.setScore(table.get(studentVo.getId(), e.getId())));
studentVo.setSubList(list);
}
}
return studentVoList;
}2. Theoretical analysis
Many to many N records query because batch query is used, the database can be accessed at most 3 times. First query the student information, then query the matching information between the student and the course, and finally query the course score information. The query time complexity is O(1).
(3) Query multiple records (pagination)
1. Sample code
public IPage<StudentVo> getStudentPage(IPage<Student> page) {
// Query student information through primary key
IPage<StudentVo> studentVoPage = EntityUtils.toPage(page(page), StudentVo::new);
// Batch query student ID
Set<Integer> stuIds = studentVoPage.getRecords().stream().map(Student::getId).collect(toSet());
LambdaQueryWrapper<StuSubRelation> wrapper = Wrappers.lambdaQuery(StuSubRelation.class).in(StuSubRelation::getStuId, stuIds);
// Query course scores by Student ID
List<StuSubRelation> stuSubRelations = stuSubRelationMapper.selectList(wrapper);
// Batch query of course ID
Set<Integer> subIds = stuSubRelations.stream().map(StuSubRelation::getSubId).collect(toSet());
if (stuIds.size() > 0 && subIds.size() > 0) {
HashBasedTable<Integer, Integer, Integer> table = getHashBasedTable(stuSubRelations);
// Student ID query course ID group
Map<Integer, List<Integer>> map = stuSubRelations.stream().collect(groupingBy(StuSubRelation::getStuId, mapping(StuSubRelation::getSubId, toList())));
List<Subject> subList = subjectMapper.selectList(Wrappers.lambdaQuery(Subject.class).in(Subject::getId, subIds));
List<SubjectBo> subBoList = EntityUtils.toList(subList, SubjectBo::new);
for (StudentVo studentVo : studentVoPage.getRecords()) {
List<SubjectBo> list = ListUtils.select(subBoList, e -> emptyIfNull(map.get(studentVo.getId())).contains(e.getId()));
list.forEach(e -> e.setScore(table.get(studentVo.getId(), e.getId())));
studentVo.setSubList(list);
}
}
return studentVoPage;
}2. Theoretical analysis
Many to many N records paging query because batch query is used, the database can be accessed at most 3 times. First query the student information, then query the matching information between the student and the course, and finally query the course score information. The query time complexity is O(1).
The dependencies that need to be used are used to handle the conversion between entity classes and VO.
<dependency>
<groupId>xin.altitude.cms.common</groupId>
<artifactId>ucode-cms-common</artifactId>
<version>1.3.3</version>
</dependency>5, Summary and expansion
(1) Summary
Through the above analysis, we can use MybatisPlus to solve one-to-one, one to many and many to many queries in multi table connection queries.
- The above code is compact and makes full use of the IDE's support for Lambda expressions to complete the check of the code during compilation.
- The business logic is clear, and the advantages of maintainability and modifiability are obvious.
- One query needs to access the database at most twice. The time complexity is o(1). Primary key query or index query has high query efficiency.
(2) Expand
MybatisPlus can well solve the problem of single table query, and can well solve the problem of connection query with the help of the encapsulation of single table query.
This scheme not only solves the problem of connection query, but also has the following expansion:
- When the amount of data is large, it still has stable query efficiency
When the amount of data reaches the level of one million, the traditional single table query through index has faced challenges. The performance of ordinary multi table join query decreases exponentially with the increase of the amount of data.
In this scheme, the join query is transformed into a primary key (index) query, and the query performance is equivalent to that of a single table query.
- Use with L2 cache to further improve query efficiency
When all queries are converted to single table based queries, the secondary cache can be safely introduced. The single table addition, deletion, modification and query operation of the secondary cache is self-adaptive linkage, which solves the problem of dirty data in the secondary cache.
I like this article ♥️ fabulous ♥️ Support me. Pay attention to me. See you next time. The relevant source code is in GitHub , video explanation Station B , collected in Special blog.