目录
概述
MyBatis-Plus (opens new window)(简称 MP)是一个 MyBatis (opens new window)的增强具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生。在我们实际开发中利用MP 框架,可以简化我们对SQL的编写,提高我们工作效率。这篇文章讲解是MP自定义SQL多表查询详解和一些案例。
愿景
我们的愿景是成为 MyBatis 最好的搭档,就像 魂斗罗 中的 1P、2P,基友搭配,效率翻倍。
编写代码
案例流程说明
1. 自定义构建多表查询条件语句
- 用户表关联角色用户关联表和角色表,模糊查询昵称和角色名称
- where 自定义条件查询
2. 自定义构建多表 Wrapper 条件语句查询
- 用户表关联角色用户关联表和角色表,模糊查询昵称和角色名称
- Wrapper 自定义条件查询
控制层
/**
* <p>
* 自定义分页查询
* </p>
*
* @author LiPing.Zou
* @since 2021-03-05
*/
@RestController
@RequiredArgsConstructor
@RequestMapping("/page")
@Api(value = "PageController", tags = "分页模块")
public class PageController {
private final AdminService adminService;
@GetMapping("getAdminPage")
@ApiOperation(value = "获取用户分页列表")
public Result<Pager<AdminResp>> getAdminPage(
@RequestParam(value="pageNumber",defaultValue = "1") @ApiParam(name="pageNumber",value="当前第几页(从第一页开始)",required = true) Integer pageNumber,
@RequestParam(value="pageSize",defaultValue = "10") @ApiParam(name="pageSize",value="每页多少条",required = true) Integer pageSize,
@RequestParam(value="roleName",required = false) @ApiParam(name="roleName",value="角色名称") String roleName
){
return Result.success(adminService.getAdminPage(pageNumber,pageSize,roleName));
}
@GetMapping("getAdminPageByWraaper")
@ApiOperation(value = "获取用户分页列表ByWraaper")
public Result<Pager<AdminResp>> getAdminPageByWraaper(
@RequestParam(value="pageNumber",defaultValue = "1") @ApiParam(name="pageNumber",value="当前第几页(从第一页开始)",required = true) Integer pageNumber,
@RequestParam(value="pageSize",defaultValue = "10") @ApiParam(name="pageSize",value="每页多少条",required = true) Integer pageSize,
@RequestParam(value="roleName",required = false) @ApiParam(name="roleName",value="角色名称") String roleName
){
return Result.success(adminService.getAdminPageByWraaper(pageNumber,pageSize,roleName));
}
}
服务层
说明:QueryWrapper 包装类查询,对应的字段必须是数据里面字段(如果你的表用了别名查询,查询对象字段最好别名.字段),如果没有匹配上,会报SQL错误
@Override
public Pager<AdminResp> getAdminPage(Integer pageNumber, Integer pageSize, String roleName) {
log.info("getAdminPage.req pageNumber={},pageSize={},roleName={}",pageNumber,pageSize,roleName);
IPage<AdminResp> page = new Page<>(pageNumber, pageSize);
IPage<AdminResp> pageData = adminMapper.pageData(page,roleName);
if (CollectionUtils.isNotEmpty(pageData.getRecords())) {
return new Pager<>(pageNumber,pageSize,(int)pageData.getPages(),pageData.getTotal(),pageData.getRecords());
}
return new Pager<>();
}
@Override
public Pager<AdminResp> getAdminPageByWraaper(Integer pageNumber, Integer pageSize, String roleName) {
log.info("getAdminPage.req pageNumber={},pageSize={},roleName={}",pageNumber,pageSize,roleName);
IPage<AdminResp> page = new Page<>(pageNumber, pageSize);
QueryWrapper<AdminResp> queryWrapper = Wrappers.query(new AdminResp());
if (StringUtils.isNotBlank(roleName)) {
queryWrapper.like("ua.nick_name",roleName).or()
.like("ur.`name`",roleName);
}
IPage<AdminResp> pageData = adminMapper.getAdminPageByWraaper(page,queryWrapper);
if (CollectionUtils.isNotEmpty(pageData.getRecords())) {
return new Pager<>(pageNumber,pageSize,(int)pageData.getPages(),pageData.getTotal(),pageData.getRecords());
}
return new Pager<>();
}
数据访问层
/**
* <p>
* 后台用户表 Mapper 接口
* </p>
*
* @author SurRen
* @since 2021-02-02
*/
public interface AdminMapper extends BaseMapper<Admin> {
/**
* 自定义分页查询
* @param page
* @param roleName
* @date: 2021/12/10 15:04
* @return: com.baomidou.mybatisplus.core.metadata.IPage<com.zlp.dto.AdminResp>
*/
IPage<AdminResp> pageData(IPage<AdminResp> page, String roleName);
/**
* Wrapper 包装类查询条件
* @param page
* @param queryWrapper
* @date: 2021/12/10 15:03
* @return: com.baomidou.mybatisplus.core.metadata.IPage<com.zlp.dto.AdminResp>
*/
IPage<AdminResp> getAdminPageByWraaper(IPage<AdminResp> page, @Param(Constants.WRAPPER) QueryWrapper<AdminResp> queryWrapper);
}
自定义 SQL
<select id="pageData" resultType="com.zlp.dto.AdminResp">
SELECT
ua.id as userId,
ua.username as username,
ua.icon as icon,
ua.email as email,
ua.nick_name as nickName,
ur.`name` as roleName,
ua.create_time as createTime
FROM
ums_admin ua
INNER JOIN ums_admin_role_relation uarr ON ua.id = uarr.admin_id
LEFT JOIN ums_role ur on uarr.role_id = ur.id
<where>
<if test="roleName !=null and roleName !=''">
and (ua.username like concat('%',#{roleName},'%') or ur.`name` like concat('%',#{roleName},'%'))
</if>
</where>
</select>
<select id="getAdminPageByWraaper" resultType="com.zlp.dto.AdminResp">
SELECT
ua.id as userId,
ua.username as username,
ua.icon as icon,
ua.email as email,
ua.nick_name as nickName,
ur.`name` as roleName,
ua.create_time as createTime
FROM
ums_admin ua
INNER JOIN ums_admin_role_relation uarr ON ua.id = uarr.admin_id
LEFT JOIN ums_role ur on uarr.role_id = ur.id
${ew.customSqlSegment}
</select>
测试
http://127.0.0.1:9080/doc.html#/
调用 /page/getAdminPage 接口
后台打印sql语句,自动帮我们分页
SELECT ua.id as userId, ua.username as username, ua.icon as icon, ua.email as email, ua.nick_name as nickName, ur.`name` as roleName, ua.create_time as createTime FROM ums_admin ua INNER JOIN ums_admin_role_relation uarr ON ua.id = uarr.admin_id LEFT JOIN ums_role ur on uarr.role_id = ur.id WHERE (ua.username like concat('%',?,'%') or ur.`name` like concat('%',?,'%')) LIMIT ?,?
调用 /page/getAdminPageByWraaper 接口
SELECT ua.id as userId, ua.username as username, ua.icon as icon, ua.email as email, ua.nick_name as nickName, ur.`name` as roleName, ua.create_time as createTime FROM ums_admin ua INNER JOIN ums_admin_role_relation uarr ON ua.id = uarr.admin_id LEFT JOIN ums_role ur on uarr.role_id = ur.id WHERE (ua.nick_name LIKE ? OR ur.`name` LIKE ?) LIMIT ?,?
MybatiPlus文档
官方文档里面也做介绍,版本需要大于3.0.7
官方链接:使用 Wrapper 自定义SQL