Preface
In this article, we will introduce in detail how to use MyBatis-Plus' paging plugin to implement the paging function of concatenated table query in XML files.
Environmental preparation
Project environment
- Java Version: 1.8+
- Spring Boot Version:
- MyBatis-Plus Version:
- database: MySQL
Add dependencies
Make sure you add MyBatis-Plus dependencies to your project. If you use Maven, you canAdd the following dependencies to the file:
<dependencies>
<dependency>
<groupId></groupId>
<artifactId>spring-boot-starter-data-mybatis-plus</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- Other dependencies -->
</dependencies>
Configure database connections
existorConfigure database connection information in the file:
=jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC =root =root -class-name=
Entity class definition
First, define several basic entity classes:
package ;
import ;
import ;
import ;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
private Long id;
private String name;
// Getters and Setters
}
package ;
import ;
import ;
import ;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserRole {
private Long userId;
private Long roleId;
// Getters and Setters
}
package ;
import ;
import ;
import ;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Role {
private Long id;
private String roleName;
// Getters and Setters
}
(View object used for display)
package ;
import ;
import ;
import ;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class RoleVo {
private Long userId;
private String roleName;
// Getters and Setters
}
Pagination returns object
In order to encapsulate the object returned by the paging, we define aPageVOkind:
package ;
import .;
import ;
import ;
import ;
import ;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Schema(title = "Pagination")
public class PageVO<T> {
@Schema(title = "total")
private Long total;
@Schema(title = "Number of display per page")
private Long size;
@Schema(title = "Current Page")
private Long current;
@Schema(title = "Data List")
private List<T> records;
}
Request parameter encapsulation
Next, we create a BO (Business Object) object to encapsulate the request parameters:
package ;
import .;
import ;
import ;
import ;
/**
* User role query BO
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Schema(title = "User Role Query BO")
public class UserRoleQueryBO {
/**
* User ID
*/
@Schema(title = "User ID")
private Long userId;
/**
* Role name
*/
@Schema(title = "Role Name")
private String roleName;
}
DAO layer
Interface definition
Create an interface at the DAO layer and define a pagination query method:
package ;
import ;
import ;
import ;
import ;
import ;
public interface UserMapper {
/**
* Conjunction table query implements pagination, query the user's role list based on user ID
* Treat the data obtained from the conjunction table query as a single table
*
* @param page pagination object
* @param bo Query conditional object
* @return Paginated role list
*/
IPage<RoleVo> getUlserListByMulTable(@Param("page") IPage<RoleVo> page, @Param("bo") UserRoleQueryBO bo);
}
Mapping files
Create the corresponding XML mapping file, and write SQL query statements in it:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/">
<mapper namespace="">
<select resultType="">
select sr.role_name
from sys_user su
left join sys_user_role sur on = sur.user_id
left join sys_role sr on sur.role_id =
where = #{}
<if test=" != null and != ''">
and sr.role_name like concat('%', #{}, '%')
</if>
<include ref/>
</select>
</mapper>
Used here<if>Tags to dynamically splice SQL statements and use<include ref/>To reference the pagination SQL syntax provided by MyBatis-Plus.
Service layer
Interface definition
Define Service interface:
package ;
import ;
import ;
import ;
public interface UserService {
/**
* Get the user role list
*
* @param currentPage Current page number
* @param pageSize Number per page
* @param bo Query conditional object
* @return Paginated role list
*/
PageVO<RoleVo> getUserRoleList(int currentPage, int pageSize, UserRoleQueryBO bo);
}
Implementation Class
Implement the Service interface:
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, RoleVo> implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public PageVO<RoleVo> getUserRoleList(int currentPage, int pageSize, UserRoleQueryBO bo) {
IPage<RoleVo> page = new Page<>(currentPage, pageSize);
IPage<RoleVo> result = (page, bo);
PageVO<RoleVo> pageVO = new PageVO<>();
(());
(());
(());
(());
return pageVO;
}
}
Controller layer
Create a Controller class to handle front-end requests:
package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@RestController
@RequestMapping("/users")
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/roles")
public PageVO<RoleVo> getUserRoleList(
@RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
@RequestParam(value = "pageSize", defaultValue = "10") int pageSize,
@RequestParam(value = "userId", required = false) Long userId,
@RequestParam(value = "roleName", required = false) String roleName) {
UserRoleQueryBO bo = new UserRoleQueryBO(userId, roleName);
return (currentPage, pageSize, bo);
}
}
test
Now you can start the Spring Boot app and test your paging concatenated table query function. You can send an HTTP GET request to/users/rolesTo test the above functions, for example:
GET /users/roles?currentPage=1&pageSize=2&userId=1&roleName=adminGET /users/roles?currentPage=1&pageSize=2&userId=1
With this example, you learned how to encapsulate request parameters and use these encapsulated parameters in the XML file of MyBatis-Plus. This helps keep the code clean and maintainable.
This is the article about the example code for implementing joint table query paging using MyBatis-Plus. For more related contents of MyBatis-Plus, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!