MyBatis 分页插件 PageHelper 使用
1. 引入Maven依赖
1 <?xml version="1.0" encoding="UTF-8"?> 2 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 3 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 4 <modelVersion>4.0.0</modelVersion> 5 6 <groupId>com.cjs.example</groupId> 7 <artifactId>cjs-mybatis-example</artifactId> 8 <version>0.0.1-SNAPSHOT</version> 9 <packaging>jar</packaging> 10 11 <name>cjs-mybatis-example</name> 12 <description></description> 13 14 <parent> 15 <groupId>org.springframework.boot</groupId> 16 <artifactId>spring-boot-starter-parent</artifactId> 17 <version>2.0.3.RELEASE</version> 18 <relativePath/> <!-- lookup parent from repository --> 19 </parent> 20 21 <properties> 22 <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> 23 <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> 24 <java.version>1.8</java.version> 25 </properties> 26 27 <dependencies> 28 <dependency> 29 <groupId>org.springframework.boot</groupId> 30 <artifactId>spring-boot-starter-web</artifactId> 31 </dependency> 32 <dependency> 33 <groupId>org.springframework.boot</groupId> 34 <artifactId>spring-boot-starter-thymeleaf</artifactId> 35 </dependency> 36 <dependency> 37 <groupId>org.mybatis.spring.boot</groupId> 38 <artifactId>mybatis-spring-boot-starter</artifactId> 39 <version>1.3.2</version> 40 </dependency> 41 <dependency> 42 <groupId>com.github.pagehelper</groupId> 43 <artifactId>pagehelper-spring-boot-starter</artifactId> 44 <version>1.2.5</version> 45 </dependency> 46 47 <dependency> 48 <groupId>mysql</groupId> 49 <artifactId>mysql-connector-java</artifactId> 50 <scope>runtime</scope> 51 </dependency> 52 53 <dependency> 54 <groupId>org.springframework.boot</groupId> 55 <artifactId>spring-boot-starter-test</artifactId> 56 <scope>test</scope> 57 </dependency> 58 </dependencies> 59 60 <build> 61 <plugins> 62 <plugin> 63 <groupId>org.springframework.boot</groupId> 64 <artifactId>spring-boot-maven-plugin</artifactId> 65 </plugin> 66 67 <!-- http://www.mybatis.org/generator/configreference/xmlconfig.html --> 68 <!-- http://www.mybatis.org/generator/running/runningWithMaven.html --> 69 <!-- mvn mybatis-generator:generate --> 70 <!-- mvn -Dmybatis.generator.overwrite=true mybatis-generator:generate --> 71 <plugin> 72 <groupId>org.mybatis.generator</groupId> 73 <artifactId>mybatis-generator-maven-plugin</artifactId> 74 <version>1.3.7</version> 75 </plugin> 76 </plugins> 77 </build> 78 79 80 </project>
2. 生成Mapper文件
在src/main/resources下创建一个generatorConfig.xml文件，然后在终端命令行下执行 mvn mybatis-generator:generate 即可自动生成
具体参见 http://www.mybatis.org/generator/running/runningWithMaven.html
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE generatorConfiguration 3 PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" 4 "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"> 5 6 <!-- http://www.mybatis.org/generator/configreference/xmlconfig.html --> 7 8 <generatorConfiguration> 9 <classPathEntry location="C:/Users/Administrator/.m2/repository/mysql/mysql-connector-java/5.1.46/mysql-connector-java-5.1.46.jar" /> 10 11 <context id="DB2Tables" targetRuntime="MyBatis3"> 12 <jdbcConnection driverClass="com.mysql.jdbc.Driver" 13 connectionURL="jdbc:mysql://10.123.52.189:3306/oh_coupon" 14 userId="devdb" 15 password="d^V$0Fu!/6-<"> 16 </jdbcConnection> 17 18 <javaTypeResolver > 19 <property name="forceBigDecimals" value="false" /> 20 </javaTypeResolver> 21 22 <javaModelGenerator targetPackage="com.cjs.example.model" targetProject="src/main/java"> 23 <property name="enableSubPackages" value="false" /> 24 <property name="trimStrings" value="true" /> 25 </javaModelGenerator> 26 27 <sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources"> 28 <property name="enableSubPackages" value="false" /> 29 </sqlMapGenerator> 30 31 <javaClientGenerator type="XMLMAPPER" targetPackage="com.cjs.example.dao" targetProject="src/main/java"> 32 <property name="enableSubPackages" value="false" /> 33 </javaClientGenerator> 34 35 <table tableName="tb_coupon" domainObjectName="Coupon" > 36 <ignoreColumn column="FRED" /> 37 </table> 38 39 </context> 40 </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.md
https://github.com/pagehelper/Mybatis-PageHelper
Mapper
1 package com.cjs.example.dao;
2
3 import com.cjs.example.model.Coupon;
4 import com.cjs.example.model.CouponExample;
5 import java.util.List;
6
7 import com.github.pagehelper.PageRowBounds;
8 import org.apache.ibatis.annotations.Mapper;
9 import org.springframework.stereotype.Repository;
10
11 @Repository
12 @Mapper
13 public interface CouponMapper {
14
15 List<Coupon> selectByExample(CouponExample example);
16
17 List<Coupon> selectByExample(CouponExample example, PageRowBounds pageRowBounds);
18
19 }
Service
1 package com.cjs.example.service.impl;
2
3 import com.cjs.example.dao.CouponMapper;
4 import com.cjs.example.model.Coupon;
5 import com.cjs.example.model.CouponExample;
6 import com.cjs.example.service.CouponService;
7 import com.github.pagehelper.Page;
8 import com.github.pagehelper.PageHelper;
9 import com.github.pagehelper.PageInfo;
10 import com.github.pagehelper.PageRowBounds;
11 import org.springframework.beans.factory.annotation.Autowired;
12 import org.springframework.stereotype.Service;
13
14 import java.util.List;
15
16 /**
17 * https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/en/HowToUse.md
18 */
19 @Service
20 public class CouponServiceImpl implements CouponService {
21
22 @Autowired
23 private CouponMapper couponMapper;
24
25 /**
26 * 静态方法startPage
27 */
28 @Override
29 public List<Coupon> getCouponListByPage(CouponExample couponExample, Integer pageNum, Integer pageSize) {
30 // 在你需要进行分页的 MyBatis 查询方法前调用 PageHelper.startPage 静态方法即可，紧跟在这个方法后的第一个MyBatis 查询方法会被进行分页。
31 // 只要你可以保证在 PageHelper 方法调用后紧跟 MyBatis 查询方法，这就是安全的
32 PageHelper.startPage(pageNum, pageSize);
33 return couponMapper.selectByExample(couponExample);
34 }
35
36 /**
37 * 分页时，实际返回的结果list类型是Page<E>，如果想取出分页信息，需要强制转换为Page<E>
38 * 因为 public class Page<E> extends ArrayList<E> implements Closeable
39 */
40 @Override
41 public Page<Coupon> getCouponListByPage1(CouponExample couponExample, Integer pageNum, Integer pageSize) {
42 PageHelper.startPage(pageNum, pageSize);
43 List<Coupon> list = couponMapper.selectByExample(couponExample);
44 if (null != list) {
45 Page<Coupon> page = (Page<Coupon>) list;
46 System.out.println(page);
47 return page;
48 }
49 return null;
50 }
51
52 /**
53 * 用PageRowBounds
54 */
55 @Override
56 public List<Coupon> getCouponListByPage2(CouponExample couponExample, Integer pageNum, Integer pageSize) {
57 PageRowBounds pageRowBounds = new PageRowBounds(pageNum, pageSize);
58 List<Coupon> couponList = couponMapper.selectByExample(couponExample, pageRowBounds);
59
60 System.out.println(pageRowBounds.getTotal());
61
62 Page<Coupon> page = (Page<Coupon>) couponList;
63 System.out.println(page);
64
65 return couponList;
66 }
67
68 @Override
69 public Page<Coupon> getCouponListByPage3(CouponExample couponExample, Integer pageNum, Integer pageSize) {
70 Page<Coupon> page = PageHelper.startPage(pageNum, pageSize).doSelectPage(()->couponMapper.selectByExample(couponExample));
71 System.out.println(page);
72 return page;
73 }
74
75 /**
76 * 方法参数
77 */
78 @Override
79 public PageInfo<Coupon> getCouponListByPage4(CouponExample couponExample, Integer pageNum, Integer pageSize) {
80 PageInfo<Coupon> pageInfo = PageHelper.startPage(pageNum, pageSize).doSelectPageInfo(()->couponMapper.selectByExample(couponExample));
81 System.out.println(pageInfo);
82 return pageInfo;
83 }
84
85 /**
86 * PageInfo
87 */
88 @Override
89 public PageInfo<Coupon> getCouponListByPage5(CouponExample couponExample, Integer pageNum, Integer pageSize) {
90 List<Coupon> list = couponMapper.selectByExample(couponExample);
91 if (null == list) {
92 return null;
93 }
94 PageInfo<Coupon> pageInfo = new PageInfo<>(list);
95 System.out.println(pageInfo);
96 return pageInfo;
97 }
98
99 @Override
100 public Page<Coupon> getCouponListByPage6(CouponExample couponExample, Integer offset, Integer limit) {
101 return (Page<Coupon>) couponMapper.selectByExample(couponExample, new PageRowBounds(offset, limit));
102 }
103 }
Controller
1 package com.cjs.example.controller;
2
3 import com.cjs.example.domain.PageBean;
4 import com.cjs.example.model.Coupon;
5 import com.cjs.example.model.CouponExample;
6 import com.cjs.example.service.CouponService;
7 import com.github.pagehelper.Page;
8 import com.github.pagehelper.PageInfo;
9 import org.springframework.beans.factory.annotation.Autowired;
10 import org.springframework.web.bind.annotation.RequestMapping;
11 import org.springframework.web.bind.annotation.RestController;
12
13 import java.util.List;
14
15 @RestController
16 @RequestMapping("/coupon")
17 public class CouponController {
18
19 @Autowired
20 private CouponService couponService;
21
22 @RequestMapping("/list")
23 public List<Coupon> list() {
24 CouponExample example = new CouponExample();
25 return couponService.getCouponListByPage(example, 1, 5);
26 }
27
28 @RequestMapping("/list2")
29 public List<Coupon> list2() {
30 CouponExample example = new CouponExample();
31 return couponService.getCouponListByPage2(example, 0, 5);
32 }
33
34 @RequestMapping("/list3")
35 public List<Coupon> list3() {
36 CouponExample example = new CouponExample();
37 return couponService.getCouponListByPage3(example, 1, 5);
38 }
39
40 @RequestMapping("/list4")
41 public PageInfo<Coupon> list4() {
42 CouponExample example = new CouponExample();
43 return couponService.getCouponListByPage4(example, 1, 5);
44 }
45
46 @RequestMapping("/list5")
47 public PageInfo<Coupon> list5() {
48 CouponExample example = new CouponExample();
49 return couponService.getCouponListByPage5(example, 1, 5);
50 }
51
52
53 /**
54 * Bootstrap Table
55 * http://bootstrap-table.wenzhixin.net.cn/documentation/
56 */
57 @RequestMapping("/listPage")
58 public PageBean<Coupon> listPage(Integer offset, Integer limit) {
59 CouponExample example = new CouponExample();
60 example.or().andVendorIdEqualTo(10001L).andYnEqualTo(1);
61 Page<Coupon> page = couponService.getCouponListByPage6(example, offset, limit);
62 PageBean<Coupon> pageBean = new PageBean<>();
63 pageBean.setTotal(page.getTotal());
64 pageBean.setRows(page.getResult());
65 return pageBean;
66 }
67 }
5. index.html
http://bootstrap-table.wenzhixin.net.cn/documentation/
1 <!DOCTYPE html>
2 <html lang="zh">
3 <head>
4 <meta charset="UTF-8">
5 <title>Index</title>
6
7 <link rel="stylesheet" href="/bootstrap-3.3.7-dist/css/bootstrap.min.css">
8 <link rel="stylesheet" href="/bootstrap-table/bootstrap-table.css">
9
10 <script src="/jquery/jquery-3.3.1.min.js"></script>
11 <script src="/bootstrap-3.3.7-dist/js/bootstrap.min.js"></script>
12 <script src="/bootstrap-table/bootstrap-table.js"></script>
13 <script src="/bootstrap-table/locale/bootstrap-table-zh-CN.js"></script>
14 </head>
15 <body>
16 <div class="row">
17 <div class="col-xs-6">
18 <table id="table"></table>
19 </div>
20 </div>
21
22 <script type="text/javascript">
23 /**
24 * http://bootstrap-table.wenzhixin.net.cn/documentation/
25 */
26 $('#table').bootstrapTable({
27 sidePagination: 'server', // 服务器端分页
28 pagination: true,
29 pageNumber: 1,
30 pageSize: 10,
31 url: '/coupon/listPage',
32 columns: [{
33 field: 'id',
34 title: 'ID',
35 sortable: true
36 }, {
37 field: 'couponName',
38 title: '名称'
39 }, {
40 field: 'couponNum',
41 title: '数量'
42 }, {
43 field: 'couponAmount',
44 title: '金额'
45 }, {
46 field: 'releaseStartTime',
47 title: '开始时间'
48 }, {
49 field: 'releaseStartTime',
50 title: '结束时间'
51 }]
52 });
53 </script>
54 </body>
55 </html>
6. 分页效果
7. 工程结构及源码
代码上传至 https://github.com/chengjiansheng/cjs-mybatis-example.git
8. 小结
个人感觉，还是PageRowBounds和PageHelper.startPage(pageNum, pageSize).doSelectPage()比较实用
