MybatisPlus自定义 Sql 实现多表查询

概述

编写代码

案例流程说明

概述

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

原文链接：https://blog.csdn.net/zouliping123456/article/details/121860456