MyBatis 分页插件 PageHelper 使用

1. 引入Maven依赖

 <?xml version="1.0" encoding="UTF-8"?>
 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <modelVersion>4.0.0</modelVersion>
 
     <groupId>com.cjs.example</groupId>
     <artifactId>cjs-mybatis-example</artifactId>
     <version>0.0.1-SNAPSHOT</version>
     <packaging>jar</packaging>
 
     <name>cjs-mybatis-example</name>
     <description></description>
 
     <parent>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-parent</artifactId>
         <version>2.0.3.RELEASE</version>
         <relativePath/> <!-- lookup parent from repository -->
     </parent>
 
     <properties>
         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
         <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
         <java.version>1.8</java.version>
     </properties>
 
     <dependencies>
         <dependency>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter-web</artifactId>
         </dependency>
         <dependency>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter-thymeleaf</artifactId>
         </dependency>
         <dependency>
             <groupId>org.mybatis.spring.boot</groupId>
             <artifactId>mybatis-spring-boot-starter</artifactId>
             <version>1.3.2</version>
         </dependency>
         <dependency>
             <groupId>com.github.pagehelper</groupId>
             <artifactId>pagehelper-spring-boot-starter</artifactId>
             <version>1.2.5</version>
         </dependency>
 
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <scope>runtime</scope>
         </dependency>
         
         <dependency>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter-test</artifactId>
             <scope>test</scope>
         </dependency>
     </dependencies>
 
     <build>
         <plugins>
             <plugin>
                 <groupId>org.springframework.boot</groupId>
                 <artifactId>spring-boot-maven-plugin</artifactId>
             </plugin>
 
             <!-- http://www.mybatis.org/generator/configreference/xmlconfig.html -->
             <!-- http://www.mybatis.org/generator/running/runningWithMaven.html -->
             <!-- mvn mybatis-generator:generate -->
             <!-- mvn -Dmybatis.generator.overwrite=true mybatis-generator:generate -->
             <plugin>
                 <groupId>org.mybatis.generator</groupId>
                 <artifactId>mybatis-generator-maven-plugin</artifactId>
                 <version>1.3.7</version>
             </plugin>
         </plugins>
     </build>
 
 
 </project>

2. 生成Mapper文件

在src/main/resources下创建一个generatorConfig.xml文件,然后在终端命令行下执行 mvn mybatis-generator:generate 即可自动生成具体参见http://www.mybatis.org/generator/running/runningWithMaven.html

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE generatorConfiguration
         PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
         "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
 
 <!-- http://www.mybatis.org/generator/configreference/xmlconfig.html -->
 
 <generatorConfiguration>
     <classPathEntry location="C:/Users/Administrator/.m2/repository/mysql/mysql-connector-java/5.1.46/mysql-connector-java-5.1.46.jar" />
 
     <context id="DB2Tables" targetRuntime="MyBatis3">
         <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                         connectionURL="jdbc:mysql://10.123.52.189:3306/oh_coupon"
                         userId="devdb"
                         password="d^V$0Fu!/6-&amp;lt;">
         </jdbcConnection>
 
         <javaTypeResolver >
             <property name="forceBigDecimals" value="false" />
         </javaTypeResolver>
 
         <javaModelGenerator targetPackage="com.cjs.example.model" targetProject="src/main/java">
             <property name="enableSubPackages" value="false" />
             <property name="trimStrings" value="true" />
         </javaModelGenerator>
 
         <sqlMapGenerator targetPackage="mapper"  targetProject="src/main/resources">
             <property name="enableSubPackages" value="false" />
         </sqlMapGenerator>
 
         <javaClientGenerator type="XMLMAPPER" targetPackage="com.cjs.example.dao"  targetProject="src/main/java">
             <property name="enableSubPackages" value="false" />
         </javaClientGenerator>
 
         <table tableName="tb_coupon" domainObjectName="Coupon" >
             <ignoreColumn column="FRED" />
         </table>
 
     </context>
 </generatorConfiguration>

3. application.yml配置

spring:
  datasource:
    url: jdbc:mysql://10.123.52.189:3306/oh_coupon
    username: devdb
    password: d^V$0Fu!/6-<
    driver-class-name: com.mysql.jdbc.Driver
mybatis:
  type-aliases-package: com.cjs.example.model
  mapper-locations: classpath:mapper/*.xml
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: true
  row-bounds-with-count: true
logging:
  level:
    com.cjs.example.dao: debug

4. PageHelper用法

具体用法文档中写得比较详细了,这里只结合实际项目情况,给出演示:参见https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/en/HowToUse.mdhttps://github.com/pagehelper/Mybatis-PageHelper

Mapper

 package com.cjs.example.dao;
 
 import com.cjs.example.model.Coupon;
 import com.cjs.example.model.CouponExample;
 import java.util.List;
 
 import com.github.pagehelper.PageRowBounds;
 import org.apache.ibatis.annotations.Mapper;
 import org.springframework.stereotype.Repository;
 
 @Repository
 @Mapper
 public interface CouponMapper {
 
     List<Coupon> selectByExample(CouponExample example);
 
     List<Coupon> selectByExample(CouponExample example, PageRowBounds pageRowBounds);
     
 }

Service

 package com.cjs.example.service.impl;
 
 import com.cjs.example.dao.CouponMapper;
 import com.cjs.example.model.Coupon;
 import com.cjs.example.model.CouponExample;
 import com.cjs.example.service.CouponService;
 import com.github.pagehelper.Page;
 import com.github.pagehelper.PageHelper;
 import com.github.pagehelper.PageInfo;
 import com.github.pagehelper.PageRowBounds;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Service;
 
 import java.util.List;
 
 /**
  * https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/en/HowToUse.md
  */
 @Service
 public class CouponServiceImpl implements CouponService {
 
     @Autowired
     private CouponMapper couponMapper;
 
     /**
      * 静态方法startPage
      */
     @Override
     public List<Coupon> getCouponListByPage(CouponExample couponExample, Integer pageNum, Integer pageSize) {
         //  在你需要进行分页的 MyBatis 查询方法前调用 PageHelper.startPage 静态方法即可,紧跟在这个方法后的第一个MyBatis 查询方法会被进行分页。
         //  只要你可以保证在 PageHelper 方法调用后紧跟 MyBatis 查询方法,这就是安全的
         PageHelper.startPage(pageNum, pageSize);
         return couponMapper.selectByExample(couponExample);
     }
 
     /**
      * 分页时,实际返回的结果list类型是Page<E>,如果想取出分页信息,需要强制转换为Page<E>
      * 因为  <strong>public class Page<E> extends ArrayList<E> implements Closeable
</strong> 39      */
     @Override
     public Page<Coupon> getCouponListByPage1(CouponExample couponExample, Integer pageNum, Integer pageSize) {
         PageHelper.startPage(pageNum, pageSize);
         List<Coupon> list = couponMapper.selectByExample(couponExample);
         if (null != list) {
             Page<Coupon> page = (Page<Coupon>) list;
             System.out.println(page);
             return page;
         }
         return null;
     }
 
     /**
      * 用PageRowBounds
      */
     @Override
     public List<Coupon> getCouponListByPage2(CouponExample couponExample, Integer pageNum, Integer pageSize) {
         PageRowBounds pageRowBounds = new PageRowBounds(pageNum, pageSize);
         List<Coupon> couponList = couponMapper.selectByExample(couponExample, pageRowBounds);
 
         System.out.println(pageRowBounds.getTotal());
 
         Page<Coupon> page = (Page<Coupon>) couponList;
         System.out.println(page);
 
         return couponList;
     }
 
     @Override
     public Page<Coupon> getCouponListByPage3(CouponExample couponExample, Integer pageNum, Integer pageSize) {
         Page<Coupon> page = PageHelper.startPage(pageNum, pageSize).doSelectPage(()->couponMapper.selectByExample(couponExample));
         System.out.println(page);
         return page;
     }
 
     /**
      * 方法参数
      */
     @Override
     public PageInfo<Coupon> getCouponListByPage4(CouponExample couponExample, Integer pageNum, Integer pageSize) {
         PageInfo<Coupon> pageInfo = PageHelper.startPage(pageNum, pageSize).doSelectPageInfo(()->couponMapper.selectByExample(couponExample));
         System.out.println(pageInfo);
         return pageInfo;
     }
 
     /**
      * PageInfo
      */
     @Override
     public PageInfo<Coupon> getCouponListByPage5(CouponExample couponExample, Integer pageNum, Integer pageSize) {
         List<Coupon> list = couponMapper.selectByExample(couponExample);
         if (null == list) {
             return null;
         }
         PageInfo<Coupon> pageInfo = new PageInfo<>(list);
         System.out.println(pageInfo);
         return pageInfo;
     }
 
     @Override
     public Page<Coupon> getCouponListByPage6(CouponExample couponExample, Integer offset, Integer limit) {
         return (Page<Coupon>) couponMapper.selectByExample(couponExample, new PageRowBounds(offset, limit));
     }
 }

Controller

 package com.cjs.example.controller;
 
 import com.cjs.example.domain.PageBean;
 import com.cjs.example.model.Coupon;
 import com.cjs.example.model.CouponExample;
 import com.cjs.example.service.CouponService;
 import com.github.pagehelper.Page;
 import com.github.pagehelper.PageInfo;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.web.bind.annotation.RequestMapping;
 import org.springframework.web.bind.annotation.RestController;
 
 import java.util.List;
 
 @RestController
 @RequestMapping("/coupon")
 public class CouponController {
 
     @Autowired
     private CouponService couponService;
 
     @RequestMapping("/list")
     public List<Coupon> list() {
         CouponExample example = new CouponExample();
         return couponService.getCouponListByPage(example, 1, 5);
     }
 
     @RequestMapping("/list2")
     public List<Coupon> list2() {
         CouponExample example = new CouponExample();
         return couponService.getCouponListByPage2(example, 0, 5);
     }
 
     @RequestMapping("/list3")
     public List<Coupon> list3() {
         CouponExample example = new CouponExample();
         return couponService.getCouponListByPage3(example, 1, 5);
     }
 
     @RequestMapping("/list4")
     public PageInfo<Coupon> list4() {
         CouponExample example = new CouponExample();
         return couponService.getCouponListByPage4(example, 1, 5);
     }
 
     @RequestMapping("/list5")
     public PageInfo<Coupon> list5() {
         CouponExample example = new CouponExample();
         return couponService.getCouponListByPage5(example, 1, 5);
     }
 
 
     /**
      * Bootstrap Table
      * http://bootstrap-table.wenzhixin.net.cn/documentation/
      */
     @RequestMapping("/listPage")
     public PageBean<Coupon> listPage(Integer offset, Integer limit) {
         CouponExample example = new CouponExample();
         example.or().andVendorIdEqualTo(10001L).andYnEqualTo(1);
         Page<Coupon> page = couponService.getCouponListByPage6(example, offset, limit);
         PageBean<Coupon> pageBean = new PageBean<>();
         pageBean.setTotal(page.getTotal());
         pageBean.setRows(page.getResult());
         return pageBean;
     }
 }

5. index.html

http://bootstrap-table.wenzhixin.net.cn/documentation/

 <!DOCTYPE html>
 <html lang="zh">
 <head>
     <meta charset="UTF-8">
     <title>Index</title>
 
     <link rel="stylesheet" href="/bootstrap-3.3.7-dist/css/bootstrap.min.css">
     <link rel="stylesheet" href="/bootstrap-table/bootstrap-table.css">
 
     <script src="/jquery/jquery-3.3.1.min.js"></script>
     <script src="/bootstrap-3.3.7-dist/js/bootstrap.min.js"></script>
     <script src="/bootstrap-table/bootstrap-table.js"></script>
     <script src="/bootstrap-table/locale/bootstrap-table-zh-CN.js"></script>
 </head>
 <body>
     <div class="row">
         <div class="col-xs-6">
             <table id="table"></table>
         </div>
     </div>
 
     <script type="text/javascript">
         /**
          * http://bootstrap-table.wenzhixin.net.cn/documentation/
          */
         $('#table').bootstrapTable({
             sidePagination: 'server',   //  服务器端分页
             pagination: true,
             pageNumber: 1,
             pageSize: 10,
             url: '/coupon/listPage',
             columns: [{
                 field: 'id',
                 title: 'ID',
                 sortable: true
             }, {
                 field: 'couponName',
                 title: '名称'
             }, {
                 field: 'couponNum',
                 title: '数量'
             }, {
                 field: 'couponAmount',
                 title: '金额'
             }, {
                 field: 'releaseStartTime',
                 title: '开始时间'
             }, {
                 field: 'releaseStartTime',
                 title: '结束时间'
             }]
         });
     </script>
 </body>
 </html>

6. 分页效果

图片描述

7. 工程结构及源码

代码上传至 https://github.com/chengjiansheng/cjs-mybatis-example.git
图片描述

8. 小结

个人感觉,还是PageRowBounds和PageHelper.startPage(pageNum, pageSize).doSelectPage()比较实用