Mybatis-plus 两种分页方法（分单表和联表）

mybatis-plus分别使用

一、PageHelper插件分页使用

准备1：引入PageHelper依赖

			<!-- pagehelper 分页插件 -->
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.4.6</version>
            </dependency>

准备2：新建表格分页数据对象

import lombok.Data;
import java.io.Serializable;
import java.util.List;

/**
 * 表格分页数据对象
 */
@Data
public class TableDataInfo implements Serializable
{
    private static final long serialVersionUID = 1L;

    /** 总记录数 */
    private long total;

    /** 列表数据 */
    private List<?> rows;

    /** 消息状态码 */
    private int code;

    /** 消息内容 */
    private String msg;
}

准备3：分页数据类

import lombok.Data;

/**
 * 分页数据
 */
@Data
public class PageDomain
{
    /** 当前记录起始索引 */
    private Integer pageNum;

    /** 每页显示记录数 */
    private Integer pageSize;

    /** 排序列 */
    private String orderByColumn;

    /** 排序的方向desc或者asc */
    private String isAsc = "asc";

    /** 分页参数合理化 */
    private Boolean reasonable = true;

    public String getOrderBy()
    {
        if (StringUtils.isEmpty(orderByColumn))
        {
            return "";
        }
        return StringUtils.toUnderScoreCase(orderByColumn) + " " + isAsc;
    }
    public String getIsAsc()
    {
        return isAsc;
    }

    public void setIsAsc(String isAsc)
    {
        if (StringUtils.isNotEmpty(isAsc))
        {
            // 兼容前端排序类型
            if ("ascending".equals(isAsc))
            {
                isAsc = "asc";
            }
            else if ("descending".equals(isAsc))
            {
                isAsc = "desc";
            }
            this.isAsc = isAsc;
        }
    }

    public Boolean getReasonable()
    {
        if (StringUtils.isNull(reasonable))
        {
            return Boolean.TRUE;
        }
        return reasonable;
    }

    public void setReasonable(Boolean reasonable)
    {
        this.reasonable = reasonable;
    }
}

准备4：分页工具类

/**
 * 分页工具类
 */
public class PageUtils extends PageHelper
{
    /**
     * 设置请求分页数据
     */
    public static void startPage()
    {
        PageDomain pageDomain = TableSupport.buildPageRequest();
        Integer pageNum = pageDomain.getPageNum();
        Integer pageSize = pageDomain.getPageSize();
        String orderBy = SqlUtil.escapeOrderBySql(pageDomain.getOrderBy());
        Boolean reasonable = pageDomain.getReasonable();
        PageHelper.startPage(pageNum, pageSize, orderBy).setReasonable(reasonable);
    }
}

准备5：响应请求分页数据的方法

	/**
     * 响应请求分页数据
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public TableDataInfo getDataTable(List<?> list)
    {
        TableDataInfo rspData = new TableDataInfo();
        rspData.setCode(HttpStatus.SUCCESS);
        rspData.setMsg("查询成功");
        rspData.setRows(list);
        rspData.setTotal(new PageInfo(list).getTotal());
        return rspData;
    }

正式使用

1、如果只是单表查询，可以用QueryWrapper

controller层

	@GetMapping("/list")
    public TableDataInfo list(Student s)
    {
        PageUtils.startPage();
        List<Student> list = StudentService.selectStudentList(s);
        return getDataTable(list);
    }

在service层新增接口以及实现类的方法

List<Student> selectStudentList(Student s);

	@Override
    public List<Student> selectStudentList(Student s) {
        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        // 所有待查询的条件
        if (null != s.getId()) {
            queryWrapper.lambda().eq(Student::getId, Student.getId());
        }
        if (StringUtils.isNotEmpty(s.getStudentName())) {
            queryWrapper.lambda().like(Student::getStudentName, s.getStudentName());
        }
        return kaddOrInfoMapper.selectList(queryWrapper);
    }

2、或者自定义SQL要多表查询，可以自己写方法联表查询

controller层

	@GetMapping("/list")
    public TableDataInfo list(Student s)
    {
        PageUtils.startPage();
        List<Student> list = StudentService.selectStudentList(s);
        return getDataTable(list);
    }

service层忽略，就去调Mapper层方法就行
Mapper接口

List<Student> selectStudentList(Student s);

Mapper.xml

<select id="selectStudentList" resultType="com.StudentDTO">
        SELECT
        c.*,
        s.*
        FROM
        classroom c
        LEFT JOIN student s ON student.id = classroom.student_id
        <where>
            <if test="id != null and id != ''">
                AND s.id = #{id}
            </if>
            <if test="studentName != null and studentName != ''">
                AND s.bu like concat('%', #{studentName}, '%')
            </if>
<!--            <if test="beginTime != null and beginTime != ''">&lt;!&ndash; 开始时间检索 &ndash;&gt;-->
<!--                and date_format(create_time,'%y%m%d') &gt;= date_format(#{beginTime},'%y%m%d')-->
<!--            </if>-->
<!--            <if test="endTime != null and endTime != ''">&lt;!&ndash; 结束时间检索 &ndash;&gt;-->
<!--                and date_format(create_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')-->
<!--            </if>-->
        </where>
    </select>

二、使用Mybatis-plus自带分页Ipage进行分页

不需要准备，直接正式使用

1、如果只是单表查询，可以用QueryWrapper

controller层

	@GetMapping(value = "/listPage")
    public IPage findListByPage(StudentQO studentQO) {
         return studentService.queryPageList(studentQO);
    }

设置查询参数

	/**
     * 设置查询参数
     * @return
     */
    private QueryWrapper<Student> getQueryParam(StudentQO studentQO) {
        QueryWrapper<KaddPoaOrMatch> queryWrapper = new QueryWrapper<>();
        // 所有待查询的条件
        if (studentQO.getId() != null) {
            queryWrapper.lambda().eq(Student::getId, studentQO.getId());
        }
        if (studentQO.getStudentName() != null) {
            queryWrapper.lambda().like(Student::getStudentName, studentQO.getStudentName());
        }
        return queryWrapper;
    }

service层

@Override
    public IPage queryPageList(StudentQO studentQO) {
        IPage<StudentQO> page = new Page<>(studentQO.getPageNum(),studentQO.getPageSize());
        IPage<StudentQO> iPage = studentMapper.selectPage(page, getQueryParam(studentQO));
        return iPage;
    }

2、或者自定义SQL要多表查询，可以自己写方法联表查询

controller层一样

	@GetMapping(value = "/listPage")
    public IPage findListByPage(StudentQO studentQO) {
         return studentService.queryPageList(studentQO);
    }

service层

@Override
    public IPage queryPageList(StudentQO studentQO) {
        IPage<StudentQO> page = new Page<>(studentQO.getPageNum(),studentQO.getPageSize());
        IPage<StudentQO> iPage = studentMapper.selectStudentList(page, studentQO);
        return iPage;
    }

Mapper接口

IPage<StudentQO> selectStudentList(@Param("iPage") IPage<StudentQO> iPage, @Param("param") StudentQO studentQO);

Mapper.xml

<select id="selectStudentList" resultType="com.StudentDTO">
        SELECT
        c.*,
        s.*
        FROM
        classroom c
        LEFT JOIN student s ON student.id = classroom.student_id
        <where>
            <if test="param.id != null and param.id != ''">
                AND s.id = #{param.id}
            </if>
            <if test="param.studentName != null and param.studentName != ''">
                AND s.bu like concat('%', #{param.studentName}, '%')
            </if>
        </where>
    </select>