Springbooot整合mybatis-pageHelper

mybatis-pageHelper官网：https://github.com/pagehelper

本篇博文在上篇博文：SpringBoot使用Maven建立多模块工程(二)基础上继续更新。

【1】版本与配置文件

Springboot版本：2.1.0.RELEASE。在pom中添加mybatis-pageHelper配置如下：

<!--pagehelper自动依赖mybatis -->
<dependency>
	<groupId>com.github.pagehelper</groupId>
	<artifactId>pagehelper-spring-boot-starter</artifactId>
	<version>1.2.10</version>
</dependency>

application.yml配置文件如下：

server:
  port: 8081
# 配置mybatis
mybatis:
  config-location: classpath:mybatis/mybatis-config.xml
  mapper-locations: classpath*:mybatis/mapper/*.xml

pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql

spring:
    datasource:
      url: jdbc:mysql://localhost:3306/hhprovince?serverTimezone=GMT%2B8
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
      initialSize: 50
      maxActive: 200
      maxWait: 60000
      minIdle: 20
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: true
      testOnReturn: false
      poolPreparedStatements: true
  #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,log4j
      maxPoolPreparedStatementPerConnectionSize: 20
      useGlobalDataSourceStat: true
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
debug: true
logging:
  level.com.github.pagehelper: debug
  level.com.jane: debug

---

【2】添加Mapper类

SysApkMapper2如下所示，这里使用注解：

@Mapper
public interface SysApkMapper2 {

    @Select("SELECT * FROM tb_sys_apk")
    Page<SysApk> getSysApkList();
}

Page<SysAPk>是什么？其继承了ArrayList<E>，如下图所示：

添加了如下属性：

 private static final long serialVersionUID = 1L;
    private int pageNum;
    private int pageSize;
    private int startRow;
    private int endRow;
    private long total;
    private int pages;
    private boolean count;
    private Boolean reasonable;
    private Boolean pageSizeZero;
    private String countColumn;
    private String orderBy;
    private boolean orderByOnly;

也就意味着，当你获取到了Page<SysAPk>后，你可以手动获取分页信息返回给前端需要的分页数据格式。当然，通常不会这么做，因为有一个PageInfo<T>来帮助我们处理。

---

【3】Controller中添加测试方法

testPageHelper方法如下：

	@RequestMapping("/page")
    public Object testPageHelper(){
        PageHelper.startPage(1, 2,true);
        Page<SysApk> sysApkList= apkMapper2.getSysApkList();
        System.out.println("sysApkList : "+sysApkList);
        PageInfo pageInfo = new PageInfo(sysApkList);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("pageInfo",pageInfo);
        return jsonObject;
    }

PageInfo<T>是什么？

如下所示，其拥有分页相关属性并实现了序列号接口：

public class PageInfo<T> extends PageSerializable<T> {
    private int pageNum;
    private int pageSize;
    private int size;
    private int startRow;
    private int endRow;
    private int pages;
    private int prePage;
    private int nextPage;
    private boolean isFirstPage;
    private boolean isLastPage;
    private boolean hasPreviousPage;
    private boolean hasNextPage;
    private int navigatePages;
    private int[] navigatepageNums;
    private int navigateFirstPage;
    private int navigateLastPage;

其构造方法如下所示：

 public PageInfo() {
        this.isFirstPage = false;
        this.isLastPage = false;
        this.hasPreviousPage = false;
        this.hasNextPage = false;
    }

    public PageInfo(List<T> list) {
        this(list, 8);
    }

    public PageInfo(List<T> list, int navigatePages) {
        super(list);
        this.isFirstPage = false;
        this.isLastPage = false;
        this.hasPreviousPage = false;
        this.hasNextPage = false;
        //判断list 是否为Page类型
        if (list instanceof Page) {
            Page page = (Page)list;
            this.pageNum = page.getPageNum();
            this.pageSize = page.getPageSize();
            this.pages = page.getPages();
            this.size = page.size();
            if (this.size == 0) {
                this.startRow = 0;
                this.endRow = 0;
            } else {
                this.startRow = page.getStartRow() + 1;
                this.endRow = this.startRow - 1 + this.size;
            }
            // list 为原生Collection类型
        } else if (list instanceof Collection) {
            this.pageNum = 1;
            this.pageSize = list.size();
            this.pages = this.pageSize > 0 ? 1 : 0;
            this.size = list.size();
            this.startRow = 0;
            this.endRow = list.size() > 0 ? list.size() - 1 : 0;
        }

        if (list instanceof Collection) {
            this.navigatePages = navigatePages;
            this.calcNavigatepageNums();
            this.calcPage();
            this.judgePageBoudary();
        }

    }

从上面可以看到，传给PageInfo<T>的list既可以是 Page<SysApk>也可以是List<SysApk>。

---

查看console：

可以发现进行了count统计和limit分页。

页面返回数据：

{
	"pageInfo": {
		"total": 3,
		"list": [{
			"id": 1,
			"versionCode": 1,
			"versionName": "1",
			"versionMsg": "1",
			"apkName": "1",
			"downUrl": "1",
			"createDate": "2019-03-19T01:19:12.000+0000",
			"ifWork": 1,
			"ifDelete": 0,
			"ifForce": 0,
			"memo": "111",
			"phoneType": 1
		}, {
			"id": 2,
			"versionCode": 2,
			"versionName": "2",
			"versionMsg": "2",
			"apkName": "2",
			"downUrl": "2",
			"createDate": "2019-03-19T01:19:50.000+0000",
			"ifWork": 1,
			"ifDelete": 0,
			"ifForce": 0,
			"memo": "222",
			"phoneType": 2
		}],
		"pageNum": 1,
		"pageSize": 2,
		"size": 2,
		"startRow": 1,
		"endRow": 2,
		"pages": 2,
		"prePage": 0,
		"nextPage": 2,
		"isFirstPage": true,
		"isLastPage": false,
		"hasPreviousPage": false,
		"hasNextPage": true,
		"navigatePages": 8,
		"navigatepageNums": [1, 2],
		"navigateFirstPage": 1,
		"navigateLastPage": 2
	}
}

---

【4】startPage方法的几个参数

startPage方法源码如下：

 public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {
        Page<E> page = new Page(pageNum, pageSize, count);
        page.setReasonable(reasonable);
        page.setPageSizeZero(pageSizeZero);
        Page<E> oldPage = getLocalPage();
        if (oldPage != null && oldPage.isOrderByOnly()) {
            page.setOrderBy(oldPage.getOrderBy());
        }

        setLocalPage(page);
        return page;
    }

count为true时将会进行count统计，为false时不会进行count统计。

此时返回的信息为：

{
	"pageInfo": {
		"total": -1,
		"list": [{
			"id": 1,
			"versionCode": 1,
			"versionName": "1",
			"versionMsg": "1",
			"apkName": "1",
			"downUrl": "1",
			"createDate": "2019-03-19T01:19:12.000+0000",
			"ifWork": 1,
			"ifDelete": 0,
			"ifForce": 0,
			"memo": "111",
			"phoneType": 1
		}, {
			"id": 2,
			"versionCode": 2,
			"versionName": "2",
			"versionMsg": "2",
			"apkName": "2",
			"downUrl": "2",
			"createDate": "2019-03-19T01:19:50.000+0000",
			"ifWork": 1,
			"ifDelete": 0,
			"ifForce": 0,
			"memo": "222",
			"phoneType": 2
		}],
		"pageNum": 1,
		"pageSize": 2,
		"size": 2,
		"startRow": 1,
		"endRow": 2,
		"pages": 1,
		"prePage": 0,
		"nextPage": 0,
		"isFirstPage": true,
		"isLastPage": true,
		"hasPreviousPage": false,
		"hasNextPage": false,
		"navigatePages": 8,
		"navigatepageNums": [1],
		"navigateFirstPage": 1,
		"navigateLastPage": 1
	}
}

pageSizeZero：默认值为 false，当该参数设置为 true 时，如果 pageSize=0 或者 RowBounds.limit = 0 就会查询出全部的结果（相当于没有执行分页查询，但是返回结果仍然是 Page 类型）。

reasonable：分页合理化参数，默认值为false。当该参数设置为 true 时，pageNum<=0 时会查询第一页， pageNum>pages（超过总数时），会查询最后一页。默认false 时，直接根据参数进行查询。

更多参数详情参看官网：mybatis-pageHelper-DOC