PageHelper是一个Mybatis的分页插件, 负责将已经写好的sql语句, 进行分页加工.
设置
现在使用的是PageHelper 5.0 版本 :
在build.gradle先引用jar包:
// https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper
compile group: 'com.github.pagehelper', name: 'pagehelper', version: '5.0.0'
然后在mybatis的config文件中增加配置项, 我的config文件是 mybatis_general_config.xml:
注意里面的顺序非常重要一定要放在正确的位置
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 引用db.properties配置文件 -->
<!--<properties resource="config/mybatis/jdbc-mysql.properties"/>-->
<properties resource="config/mybatis/jdbc-mssql.properties"/>
<plugins>
<!-- PageHelper4.1.1 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="helperDialect" value="sqlserver"/>
<property name="offsetAsPageNum" value="false"/>
<property name="rowBoundsWithCount" value="false"/>
<property name="pageSizeZero" value="true"/>
<property name="reasonable" value="false"/>
<property name="supportMethodsArguments" value="true"/>
<property name="returnPageInfo" value="none"/>
</plugin>
</plugins>
<!--
development : 开发模式
work : 工作模式
-->
<environments default="mssql_dev">
<!--<environment id="development">-->
<!--<transactionManager type="JDBC"/>-->
<!--<!– 配置数据库连接信息 –>-->
<!--<dataSource type="POOLED">-->
<!--<property name="driver" value="${jdbc.driverClassName}"/>-->
<!--<property name="url" value="${jdbc.url}"/>-->
<!--<property name="username" value="${jdbc.user}"/>-->
<!--<property name="password" value="${jdbc.pass}"/>-->
<!--</dataSource>-->
<!--</environment>-->
<environment id="mssql_dev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--<!– 注册userMapper.xml文件，-->
<!--userMapper.xml位于me.gacl.mapping这个包下，所以resource写成me/gacl/mapping/userMapper.xml–>-->
<!--<mapper resource="resources/mapper/studentMapper.xml"/>-->
<!--<mapper class="lyb.anno_mapper.StudentMapper"/>-->
<!--<mapper class="lyb.mapper.StudentMapper"/>-->
<!--<mapper resource="resources/mapper/classesMapper.xml"/>-->
<!--<mapper resource="mybatis_mapper/StudentMapper.xml"/>-->
<!--<mapper resource="mybatis_mapper/PosInReportMapper.xml"/>-->
<mapper class="lyb.mapper.PosInReportMapper"/>
<mapper class="lyb.mapper.SalesOutReportMapper"/>
<mapper class="lyb.mapper.TransVouchMapper"/>
<mapper class="lyb.mapper.CurrentStockMapper"/>
<mapper class="lyb.mapper.Wcc_User_Customer_ConnMapper"/>
</mappers>
</configuration>
**这里有个坑 : **
<plugin interceptor="com.github.pagehelper.PageInterceptor">
这里的4.X版本的interceptor是PageHelper, 但是5.0版本后改成了PageInterceptor
如果设置的不对会报错.
有关具体设置详见:
https://pagehelper.github.io/docs/howtouse/
<!--
plugins在配置文件中的位置必须符合要求，否则会报错，顺序如下:
properties?, settings?,
typeAliases?, typeHandlers?,
objectFactory?,objectWrapperFactory?,
plugins?,
environments?, databaseIdProvider?, mappers?
-->
<plugins>
<!-- com.github.pagehelper为PageHelper类所在包名 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 使用下面的方式配置参数，后面会有所有的参数介绍 -->
<property name="param1" value="value1"/>
</plugin>
</plugins>
如果顺序放置的不对, 也会出现不能初始化sqlSessionFactory的错误
代码里的使用方法 :
直接加上一个PageHelper的静态方法
PageHelper.startPage(2,10);
List<CurrentStockReport> reportList = mapper.getReport(params.getCustomer_code(),
params.getRegion_name(),
params.getInv_class_code(),
params.getInvcode(),
params.getBarcode());
仅有PageHelper.startPage(int pageNum, int pageSize) 后面的一个查询是会分页的, 我在这里使用的对应的mapper中的查询语句是:
SELECT
CurrentStock.AutoID,
Customer.cCusName,
DistrictClass.cDCName,
Customer.cCusCode,
CurrentStock.cWhCode,
CurrentStock.iQuantity as stock,
CurrentStock.cBatch,
Inventory.cInvStd,
Inventory.cInvName,
Inventory.cInvAddCode
FROM CurrentStock
INNER JOIN Customer ON SUBSTRING(CurrentStock.cWhCode, 1, 6) = Customer.cCusCode
INNER JOIN DistrictClass ON customer.cdccode = DistrictClass.cdccode
INNER JOIN Inventory ON Inventory.cInvCode = CurrentStock.cInvCode;
而由plugin产生的sql语句是 :
SELECT TOP 10
cCusName,
AutoID,
cDCName,
cWhCode,
stock,
cBatch,
cInvStd,
cInvName,
cInvAddCode
FROM (SELECT
ROW_NUMBER()
OVER (
ORDER BY RAND() ) PAGE_ROW_NUMBER,
cCusName,
AutoID,
cDCName,
cWhCode,
stock,
cBatch,
cInvStd,
cInvName,
cInvAddCode
FROM (SELECT
Customer.cCusName,
CurrentStock.AutoID,
DistrictClass.cDCName,
CurrentStock.cWhCode,
CurrentStock.iQuantity AS stock,
CurrentStock.cBatch,
Inventory.cInvStd,
Inventory.cInvName,
Inventory.cInvAddCode
FROM CurrentStock
LEFT OUTER JOIN Customer ON SUBSTRING(CurrentStock.cWhCode, 1, 6) = Customer.cCusCode
LEFT OUTER JOIN DistrictClass ON customer.cdccode = DistrictClass.cdccode
LEFT OUTER JOIN Inventory ON Inventory.cInvCode = CurrentStock.cInvCode
WHERE (Customer.cCusCode = '100001')) AS PAGE_TABLE_ALIAS) AS PAGE_TABLE_ALIAS
WHERE PAGE_ROW_NUMBER > 10
ORDER BY PAGE_ROW_NUMBER
这里可以看出, 本质上对于我们自己的sql插件做的事就是在外面套了一层select, 其中增加了一个column作为排序的依据.
关键在于这一句:
ROW_NUMBER() OVER (ORDER BY RAND())
ROW_NUMBER() 函数就是根据OVER之中的条件, 不断给出一个从1开始自增1的序列, 如果条件正确就一直生成.
这里通过RAND() 生成随机数, 来进行生成ROW_NUMBER(), 然后再在外面套一层SELECT作为选择分页的条件, 如PAGE_ROW_NUMBER > x 就是根据从某一页开始取, TOP y就是取多少个. 这些都是根据刚才的调用 **PageHelper.startPage(int pageNum, int pageSize) ** 计算得到的.
所以可以对任意的sql语句来进行构建分页, 以后可以看下这个插件源码.