Spring Boot--Mybatis分页插件pagehelper

分页的解决方案一般是limit(page,pageSize)，用count获取总数，然后根据总数和pageSize计算出pages，每个分页操作都要涉及很多子操作。这个时候pagehelper闪亮登场，有了它以后，我们不需要再在sql上做多余动作，而是传入需要的page和pageSize，pagehelper自动会帮我们完成后续工作，具体过程请继续往下看：

引入依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.12</version>
</dependency>

application.properties配置

# pagehelper分页插件配置
pagehelper.helper-dialect=mysql
pagehelper.support-methods-arguments=true

Service层

@Override
public PagedData getSiteListNotDeleteByTagId(Integer tagId, Integer pageNum, Integer pageSize) {
    Example example = new Example(Site.class);
    criteria.andEqualTo("tagid", tagId);
    /*第一步：PageHelper.startPage必须放在最前面，只针对最近的一次查询*/
    PageHelper.startPage(pageNum, pageSize);
    /*第二步：获取数据*/
    List<Site> siteList = siteMapper.selectByExample(example);
    /*第三步：返回封装的分页数据*/
    return PageUtil.getPagedData(siteList);
}
这是一个普通的查找全部记录的查询语句，并不需要写分页SQL，分页插件会拦截查询请求，并读取前台传来的分页查询参数重新生成分页查询语句

分页数据工具类

public class PageUtil {
    public static PagedData getPagedData(List<?> list) {
        PageInfo<?> pageInfo = new PageInfo<>(list);
        PagedData pageData = new PagedData();
        pageData.setPageNum(pageInfo.getPageNum());
        pageData.setPageSize(pageInfo.getPageSize());
        pageData.setPages(pageInfo.getPages());
        pageData.setTotal(pageInfo.getTotal());
        pageData.setPageList(pageInfo.getList());
        return pageData;
    }
}

分页查询结果返回体

@Data
public class PagedData {
	// 当前页
	private int pageNum;
	// 总页数
	private int pages;
	// 每页记录数
	private int pageSize;
	// 总记录数
	private long total;
	// 分页内容
	private List<?> pageList;
}

Controller层

  @GetMapping("list")
  public ApiResult<PagedData> getSiteListNotDeleteByTagId(@RequestParam("tagId") Integer tagId,
                                                          @RequestParam("pageNum") Integer pageNum,
                                                          @RequestParam("pageSize") Integer pageSize) {
      PagedData pagedData = siteService.getSiteListNotDeleteByTagId(tagId, pageNum, pageSize);
      return ApiResult.success(pagedData);
  }

进行测试

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@685f3333] was not registered for synchronization because synchronization is not active
Cache Hit Ratio [SQL_CACHE]: 0.6666666666666666
JDBC Connection [HikariProxyConnection@2010927947 wrapping com.mysql.cj.jdbc.ConnectionImpl@20bc084f] will not be managed by Spring
==>  Preparing: SELECT count(0) FROM site WHERE (tagId = ? AND is_deleted = ?) 
==> Parameters: 15(Integer), 0(Integer)
<==    Columns: count(0)
<==        Row: 14
<==      Total: 1
==>  Preparing: SELECT id,name,description,url,iconUrl,starCount,createTime,updateTime,tagId,is_deleted FROM site WHERE ( tagId = ? and is_deleted = ? ) order by createTime DESC LIMIT ? 
==> Parameters: 15(Integer), 0(Integer), 10(Integer)
<==    Columns: id, name, description, url, iconUrl, starCount, createTime, updateTime, tagId, is_deleted
<==        Row: 18, 0, 0, 0, null, 0, 2020-11-29 18:21:38, 2020-11-29 18:21:38, 15, 0
<==        Row: 17, 8, 8, 8, null, 0, 2020-11-29 17:58:50, 2020-11-29 17:58:50, 15, 0
<==        Row: 16, 7, 7, 7, null, 0, 2020-11-29 17:23:06, 2020-11-29 17:23:06, 15, 0
<==        Row: 15, 333, 444, 555, null, 0, 2020-11-29 16:34:15, 2020-11-29 16:34:15, 15, 0
<==        Row: 14, 222, 333, 444, null, 0, 2020-11-29 16:28:49, 2020-11-29 16:28:49, 15, 0
<==        Row: 13, 京东, 最大的购物平台, http://jd.com, null, 0, 2020-11-29 16:15:35, 2020-11-29 22:27:48, 15, 0
<==        Row: 12, 猫眼, 猫眼看电影, http://maoyan.com, null, 0, 2020-11-29 16:10:35, 2020-11-29 16:10:35, 15, 0
<==        Row: 11, 支付宝技术团队, 支付宝技术团队公众号, https://baidu.com, /static/img/temp_icon.jpg, 4, 2020-11-14 00:03:33, 2020-11-26 23:10:04, 15, 0
<==        Row: 7, 美团技术团队, 美团移动技术团队公众号, https://baidu.com, null, 0, 2020-11-14 00:01:03, 2020-11-26 23:10:00, 15, 0
<==        Row: 8, 淘宝技术团队, 淘宝移动技术团队公众号, https://baidu.com, null, 1, 2020-11-14 00:01:03, 2020-11-29 18:24:34, 15, 0
<==      Total: 10
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@685f3333]

根据mybatis日志可以看出，分页插件进行了拦截并且插入了limit语句，获取了想要的页面，但是我们并没有在sql做任何操作，而是通过代码形式，就简单地完成了分页

参考资料

【pagehelper文档】https://pagehelper.github.io/docs/howtouse/