mybatis-plus多表分页查询最佳实现（简单）

1.简介

在Mybatis Plus 中，虽然IService 接口帮我们定义了很多常用的方法，但这些都是 T 对象有用，如果涉及到 多表的查询，还是需要自定义Vo 对象和自己编写sql 语句，Mybatis Plus提供了一个Page 对象,查询是需要设置其中的 size 字段 和 current 字段的值。

mybatis-plus的单表分页就不必多说了，那多表联查的分页该如何实现呢？其实也很简单，你只需要自己写好关联查询的sql再结合mybatis-plus提供的分页对象，就可以实现了。但是如何才能优雅的将分页参数和查询条件提供给mybatis-plus呢？我选择使用
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;这个对象来实现。直接继承baomidou提供的类可以省去每次手动再new对象，因为在获取参数时就已经自动构建了

2.实现

2.1版本

    3.5.1    8.0.18

                                    <dependency>                <groupId>com.baomidougroupId>                <artifactId>mybatis-plus-boot-starterartifactId>                <version>${mybatis-plus.version}version>            dependency>                        <dependency>                <groupId>com.baomidougroupId>                <artifactId>mybatis-plus-generatorartifactId>                <version>${mybatis-plus.version}version>            dependency>                                    <dependency>                <groupId>mysqlgroupId>                <artifactId>mysql-connector-javaartifactId>                <version>${connector.version}version>            dependency>

2.2分页插件

@MapperScan(“。。。”)这些基本配置我就不多说了

@Configurationpublic class MPConfig {        @Bean    public MybatisPlusInterceptor mybatisPlusInterceptor() {        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();        //乐观锁        //interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());        //分页锁        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));        return interceptor;    }}

2.3 分页参数 继承 自 Page

直接继承baomidou提供的类可以省去每次手动再new对象，因为在获取参数时就已经自动构建了

package com.dxf.common.utils;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;import io.swagger.annotations.ApiModelProperty;import io.swagger.annotations.ApiParam;import springfox.documentation.annotations.ApiIgnore;import java.util.List;public class PageParam<T> extends Page<T> {        @ApiParam(hidden = true)    private List<T> records;        @ApiParam(hidden = true)    private long total = 0;        @ApiParam(value = "每页大小，默认10",required = false, defaultValue = "10")    private long size = 10;        @ApiParam(value = "当前页，默认1",required = false,defaultValue = "1")    private long current = 1;        @ApiParam(hidden = true)    private boolean isSearchCount = true;    @Override    @ApiParam(hidden = true)    public List<T> getRecords() {        return this.records;    }    @Override    public Page<T> setRecords(List<T> records) {        this.records = records;        return this;    }    @Override    public long getTotal() {        return this.total;    }    @Override    public Page<T> setTotal(long total) {        this.total = total;        return this;    }    @ApiParam(hidden = true)    public boolean getSearchCount() {        if (total < 0) {            return false;        }        return isSearchCount;    }    @Override    @ApiParam(hidden = true)    public boolean isSearchCount() {        if (total < 0) {            return false;        }        return isSearchCount;    }    @Override    public Page<T> setSearchCount(boolean isSearchCount) {        this.isSearchCount = isSearchCount;        return this;    }    @Override    public long getSize() {        return this.size;    }    @Override    public Page<T> setSize(long size) {        this.size = size;        return this;    }    @Override    public long getCurrent() {        return this.current;    }    @Override    public Page<T> setCurrent(long current) {        this.current = current;        return this;    }}

2.4 实现的重点mapper

import org.apache.ibatis.annotations.Param;public interface SysRoleMapper extends BaseMapper<SysRole> {    IPage<SysRole> searchPage(PageParam<SysRole> pageParam, @Param("name") String name);}

关联的是角色表和用户表

DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.dxf.data.mapper.SysRoleMapper">    <select id="searchPage" resultType="com.dxf.data.entity.SysRole">        SELECT r.id,r.name,r.status,r.role_sort, a.name create_by ,r.create_time,r.update_time,r.remark        FROM sys_role r left join sys_user a on r.create_by=a.id        <where>            <if test="name != null and name != ''">                r.name like concat(#{name},'%')            if>        where>        order by r.role_sort    select>mapper>

2.5 其他层就是传下参数

@RestController@RequestMapping("/admin/role")@Api(tags  = "SysRoleControllr|角色控制器")public class SysRoleController {    @Autowired    SysRoleService roleService;        @GetMapping("/page")    @ApiOperation("角色列表分页查询")    public ResultJson page(PageParam<SysRole> pageParam,String likeKey) {        IPage<SysRole> iPage = roleService.searchPage(pageParam,likeKey);        Map<String, Object> map = new HashMap<>();        map.put("rows",iPage.getRecords());        map.put("total",iPage.getTotal());        return ResultJson.ok().data(map);    }public interface SysRole}Service extends IService<SysRole> {        IPage<SysRole> searchPage(PageParam<SysRole> pageParam,String name);}@Servicepublic class SysRoleServiceImpl extends ServiceImpl<SysRoleMapper, SysRole> implements SysRoleService {    @Override    public IPage<SysRole> searchPage(PageParam<SysRole> pageParam,String name) {        return baseMapper.searchPage(pageParam,name);    }}

来源地址：https://blog.csdn.net/weixin_42408648/article/details/127104946