一、PageHelper中分页存在的问题：
- 主表结构如下：
provider_order表，订单主表。我们这里简化处理，它只有一个字段，即id。
VARCHAR | po_id | 订单表id
- 从表结构如下：
order_info表，订单从表。它负责记录订单的一些信息，与主表是一对多的关系。
VARCHAR | oi_id | 订单信息id VARCHAR | po_id | 订单表id TEXT | info | 信息详情
- 现在我们需要一个简单的连表查询操作，sql语句如下：
<select id="getProviderOrderWithOrderInfoTest" resultMap="ResultMapWithOrderInfo" parameterType="java.lang.String"> SELECT * FROM (SELECT * FROM provider_order WHERE po_id LIKE #{po_id}) AS limitable LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id </select>- 进行PageHelper的分页操作，查询前十条记录：
Page page = PageHelper.startPage(0, 10); List<ProviderOrder> providerOrderList = testService.getProviderOrderWithOrderInfoTest("%-%"); PageInfo<ProviderOrder> pageInfo = new PageInfo<>(providerOrderList); System.out.println(pageInfo); - 目前我们的主表有两条记录，从表有二十条记录。
我们会发现，查询出来的总数是20，然后查询结果是两个list（各5条从表信息），不仅如此，分页数据也很奇怪，我们只有两条主表记录，但却被分成了两页（pages = 2），并且hasNextPage = true。这并不是我们要的结果。我们想要查询的效果是：
前十条 订单 记录，然后连表查询出该十条订单记录下的所有 订单信息
也就是说，结果集中，首先我们应该得到两个list，list中各10条从表信息，而且也不该有下一页，总页数应为1，总数据数应为2。
查看PageHelper的源码我们来看看，它的count，以及limit，到底是怎么回事。
PageHelper的count实现
private Long executeAutoCount(Executor executor, MappedStatement countMs, Object parameter, BoundSql boundSql, RowBounds rowBounds, ResultHandler resultHandler) throws IllegalAccessException, SQLException { Map<String, Object> additionalParameters = (Map) this.additionalParametersField.get(boundSql); CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql); String countSql = this.dialect.getCountSql(countMs, boundSql, parameter, rowBounds, countKey); BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter); Iterator var11 = additionalParameters.keySet() .iterator(); while (var11.hasNext()) { String key = (String) var11.next(); countBoundSql.setAdditionalParameter(key, additionalParameters.get(key)); } Object countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql); Long count = (Long) ((List) countResultList).get(0); // 获取count return count; }我们很容易看出来，count的sql语句，在这里经过了处理，这里调用了dialect的getCountSql方法。 它的实现如下：
public String getSmartCountSql(String sql, String name) { Statement stmt = null; if (sql.indexOf("/*keep orderby*/") >= 0) { return this.getSimpleCountSql(sql); } else { try { stmt = CCJSqlParserUtil.parse(sql); } catch (Throwable var8) { return this.getSimpleCountSql(sql); } Select select = (Select)stmt; SelectBody selectBody = select.getSelectBody(); try { this.processSelectBody(selectBody); } catch (Exception var7) { return this.getSimpleCountSql(sql); } this.processWithItemsList(select.getWithItemsList()); this.sqlToCount(select, name); String result = select.toString(); return result; } } public String getSimpleCountSql(String sql) { return this.getSimpleCountSql(sql, "0"); } public String getSimpleCountSql(String sql, String name) { StringBuilder stringBuilder = new StringBuilder(sql.length() + 40); stringBuilder.append("select count("); stringBuilder.append(name); stringBuilder.append(") from ("); stringBuilder.append(sql); stringBuilder.append(") tmp_count"); return stringBuilder.toString(); }简单来说，就是在sql外面套一层select count语句，我们的sql语句变成了 SELECT count(0) FROM (我们的sql语句)（真实情况没有这么简单，这只是其中一种情况。）这样就很容易得知，为什么最后的total是20（所有数据数），而不是2（主表数据数）了。
三、 PageHelper的分页实现
与上面相同，这里以dialect（为了适配各种数据库）为mysql为例，我们可以看到，如果你没有在sql语句里面写limit，就会在sql语句的最后面，添加limit语句。
public String getPageSql(String sql, RowBounds rowBounds, CacheKey pageKey) { StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14); sqlBuilder.append(sql); if (rowBounds.getOffset() == 0) { sqlBuilder.append(" LIMIT "); sqlBuilder.append(rowBounds.getLimit()); } else { sqlBuilder.append(" LIMIT "); sqlBuilder.append(rowBounds.getOffset()); sqlBuilder.append(","); sqlBuilder.append(rowBounds.getLimit()); pageKey.update(rowBounds.getOffset()); } pageKey.update(rowBounds.getLimit()); return sqlBuilder.toString(); }程序运行得到sql语句如下，PageHelper对查询的总结果集（包括主表数据与从表数据），进行了分页
在得知PageHelper在这两个步骤的原理后，我们开始对PageHelper进行改造。
四、 count结果修正
在上面的count源码中，我们可以看到这么一段代码：
public String getSmartCountSql(String sql, String name) { Statement stmt = null; if (sql.indexOf("/*keep orderby*/") >= 0) { return this.getSimpleCountSql(sql); } else { try { stmt = CCJSqlParserUtil.parse(sql); } catch (Throwable var8) { return this.getSimpleCountSql(sql); } Select select = (Select)stmt; SelectBody selectBody = select.getSelectBody(); try { this.processSelectBody(selectBody); } catch (Exception var7) { return this.getSimpleCountSql(sql); } this.processWithItemsList(select.getWithItemsList()); this.sqlToCount(select, name); String result = select.toString(); return result; } } public void sqlToCount(Select select, String name) { SelectBody selectBody = select.getSelectBody(); List<SelectItem> COUNT_ITEM = new ArrayList(); COUNT_ITEM.add(new SelectExpressionItem(new Column("count(" + name + ")"))); if (selectBody instanceof PlainSelect && this.isSimpleCount((PlainSelect)selectBody)) { ((PlainSelect)selectBody).setSelectItems(COUNT_ITEM); } else { PlainSelect plainSelect = new PlainSelect(); SubSelect subSelect = new SubSelect(); subSelect.setSelectBody(selectBody); subSelect.setAlias(TABLE_ALIAS); plainSelect.setFromItem(subSelect); plainSelect.setSelectItems(COUNT_ITEM); select.setSelectBody(plainSelect); } }实际上PageHelper是有提供给我们一套指定colum查询的入口的。我们将查询代码做如下改造：
Page page = PageHelper.startPage(0, 10); page.setCountColumn("DISTINCT po_id"); List<ProviderOrder> providerOrderList = testService.getProviderOrderWithOrderInfoTest("%-%"); PageInfo<ProviderOrder> pageInfo = new PageInfo<>(providerOrderList); System.out.println(pageInfo); 我们会发现，我们需要的分页数据正常了，总页码（pages：1），total：2。
五、 查询结果集修正
我们在三中，发现了PageHelper limit添加的位置不正确，我们需要将其放在正确的位置下。所以新建一个类如下：
/** * Created by Anur IjuoKaruKas on 2018/6/8 */ public class PageSqlResolver { public static final String SQL_SIGN = "AS limitable"; public static final String LIMIT_SIGN = "LIMIT ?"; public static final String LIMIT_SIGN_EX = "LIMIT ?, ?"; public static String resolveLimit(String pageSql) { if (pageSql == null) { return null; } if (pageSql.contains(SQL_SIGN)) {// 如果需要特殊分页 String changer = ""; if (pageSql.contains(LIMIT_SIGN_EX)) { changer = LIMIT_SIGN_EX; } else if (pageSql.contains(LIMIT_SIGN)) { changer = LIMIT_SIGN; } pageSql = pageSql.replace(changer, ""); StringBuilder sqlBuilder = new StringBuilder(pageSql); StringBuilder mae = new StringBuilder(sqlBuilder.substring(0, sqlBuilder.indexOf(SQL_SIGN)));// mae 截止sql语句到 limitable StringBuilder uShiRo = new StringBuilder(sqlBuilder.substring(sqlBuilder.indexOf(SQL_SIGN), sqlBuilder.length()));// 剩余的 mae.insert(mae.lastIndexOf(")"), String.format(" %s", changer)); return mae.append(uShiRo) .toString(); } else { return pageSql; } } }这段代码的含义是：将AS limitable，也就是主表，标记为真正分页的地方，并向其添如limit ? ,?
SELECT * FROM (SELECT * FROM provider_order WHERE po_id LIKE ?) AS limitable LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id LIMIT ? sql语句从上面变成了：
SELECT * FROM (SELECT * FROM provider_order WHERE po_id LIKE ? LIMIT ?) AS limitable LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id 我们发现这就是我们想要的结果，它的分页是针对主表进行的，而不是针对结果集。
解析到这里就结束了
https://github.com/anurnomeru/page-helper-ext demo示例请点击这里
六、改造步骤详解
1、引入依赖 这里使用了mybatis 3.4.6（tk.mybatis 4.0.2），pagehelper5.1.4（pagehelper-spring-boot-starter1.2.5），你可以引入原依赖，也可以用这里使用的tkMybatis，以及pagehelper-spring-boot-starter。
<?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.anur</groupId> <artifactId>page-helper</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>page-helper</name> <description>Demo project for Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.2.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> <!-- page - helper --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.5</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- codeGen --> <dependency> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-core</artifactId> <version>1.3.6</version> </dependency> <!-- tk - mybatis --> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper</artifactId> <version>4.0.2</version> </dependency> <!-- mysql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.47</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <proc>none</proc> </configuration> </plugin> </plugins> </build> </project> 2、新增两个类，用于修正查询时sql语句
package com.anur.pagehelper.page; // // Source code recreated from a .class file by IntelliJ IDEA // (powered by Fernflower decompiler) // import com.github.pagehelper.Dialect; import com.github.pagehelper.PageException; import com.github.pagehelper.cache.Cache; import com.github.pagehelper.cache.CacheFactory; import com.github.pagehelper.util.MSUtils; import com.github.pagehelper.util.StringUtil; import java.lang.reflect.Field; import java.sql.SQLException; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Properties; import org.apache.ibatis.cache.CacheKey; import org.apache.ibatis.executor.Executor; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; import org.apache.ibatis.session.Configuration; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.session.RowBounds; /** * Created by Anur IjuoKaruKas on 2018/6/8 */ @Intercepts({ @Signature( type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class } ), @Signature( type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class } ) }) public class CustomPageInterceptor implements Interceptor { protected Cache<String, MappedStatement> msCountMap = null; private Dialect dialect; private String default_dialect_class = "com.github.pagehelper.PageHelper"; private Field additionalParametersField; private String countSuffix = "_COUNT"; public CustomPageInterceptor() { } /** * 拦截 */ public Object intercept(Invocation invocation) throws Throwable { try { Object[] args = invocation.getArgs(); // 对应一个Mapper节点，描述一条sql语句 MappedStatement ms = (MappedStatement) args[0]; // 参数 Object parameter = args[1]; // mybatis自带分页 RowBounds rowBounds = (RowBounds) args[2]; // 结果集处理器 ResultHandler resultHandler = (ResultHandler) args[3]; // 执行器 Executor executor = (Executor) invocation.getTarget(); CacheKey cacheKey; BoundSql boundSql; if (args.length == 4) { boundSql = ms.getBoundSql(parameter); cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql); } else { cacheKey = (CacheKey) args[4]; boundSql = (BoundSql) args[5]; } List resultList; if (this.dialect.skip(ms, parameter, rowBounds)) { resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql); } else { String msId = ms.getId(); Configuration configuration = ms.getConfiguration(); Map<String, Object> additionalParameters = (Map) this.additionalParametersField.get(boundSql); if (this.dialect.beforeCount(ms, parameter, rowBounds)) { String countMsId = msId + this.countSuffix; MappedStatement countMs = this.getExistedMappedStatement(configuration, countMsId); Long count; if (countMs != null) { count = this.executeManualCount(executor, countMs, parameter, boundSql, resultHandler); } else { countMs = (MappedStatement) this.msCountMap.get(countMsId); if (countMs == null) { countMs = MSUtils.newCountMappedStatement(ms, countMsId); this.msCountMap.put(countMsId, countMs); } // 这里是count，可能要改 // boundSql = PageSqlResolver.resolveCount(configuration, boundSql, parameter); count = this.executeAutoCount(executor, countMs, parameter, boundSql, rowBounds, resultHandler); } if (!this.dialect.afterCount(count, parameter, rowBounds)) { Object var24 = this.dialect.afterPage(new ArrayList(), parameter, rowBounds); return var24; } } if (!this.dialect.beforePage(ms, parameter, rowBounds)) { resultList = executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, boundSql); } else { parameter = this.dialect.processParameterObject(ms, parameter, boundSql, cacheKey); String pageSql = this.dialect.getPageSql(ms, boundSql, parameter, rowBounds, cacheKey); // 对sql进行改造 pageSql = PageSqlResolver.resolveLimit(pageSql); BoundSql pageBoundSql = new BoundSql(configuration, pageSql, boundSql.getParameterMappings(), parameter); Iterator var17 = additionalParameters.keySet() .iterator(); while (true) { if (!var17.hasNext()) { resultList = executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, pageBoundSql); break; } String key = (String) var17.next(); pageBoundSql.setAdditionalParameter(key, additionalParameters.get(key)); } } } Object var22 = this.dialect.afterPage(resultList, parameter, rowBounds); return var22; } finally { this.dialect.afterAll(); } } private Long executeManualCount(Executor executor, MappedStatement countMs, Object parameter, BoundSql boundSql, ResultHandler resultHandler) throws IllegalAccessException, SQLException { CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql); BoundSql countBoundSql = countMs.getBoundSql(parameter); Object countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql); Long count = ((Number) ((List) countResultList).get(0)).longValue(); return count; } private Long executeAutoCount(Executor executor, MappedStatement countMs, Object parameter, BoundSql boundSql, RowBounds rowBounds, ResultHandler resultHandler) throws IllegalAccessException, SQLException { Map<String, Object> additionalParameters = (Map) this.additionalParametersField.get(boundSql); CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql); String countSql = this.dialect.getCountSql(countMs, boundSql, parameter, rowBounds, countKey); BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter); Iterator var11 = additionalParameters.keySet() .iterator(); while (var11.hasNext()) { String key = (String) var11.next(); countBoundSql.setAdditionalParameter(key, additionalParameters.get(key)); } Object countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql); Long count = (Long) ((List) countResultList).get(0); // 获取count return count; } private MappedStatement getExistedMappedStatement(Configuration configuration, String msId) { MappedStatement mappedStatement = null; try { mappedStatement = configuration.getMappedStatement(msId, false); } catch (Throwable var5) { ; } return mappedStatement; } public Object plugin(Object target) { return Plugin.wrap(target, this); } public void setProperties(Properties properties) { this.msCountMap = CacheFactory.createCache(properties.getProperty("msCountCache"), "ms", properties); String dialectClass = properties.getProperty("dialect"); if (StringUtil.isEmpty(dialectClass)) { dialectClass = this.default_dialect_class; } try { Class<?> aClass = Class.forName(dialectClass); this.dialect = (Dialect) aClass.newInstance(); } catch (Exception var6) { throw new PageException(var6); } this.dialect.setProperties(properties); String countSuffix = properties.getProperty("countSuffix"); if (StringUtil.isNotEmpty(countSuffix)) { this.countSuffix = countSuffix; } try { this.additionalParametersField = BoundSql.class.getDeclaredField("additionalParameters"); this.additionalParametersField.setAccessible(true); } catch (NoSuchFieldException var5) { throw new PageException(var5); } } } package com.anur.pagehelper.page; /** * Created by Anur IjuoKaruKas on 2018/6/8 */ public class PageSqlResolver { public static final String SQL_SIGN = "AS limitable"; public static final String LIMIT_SIGN = "LIMIT ?"; public static final String LIMIT_SIGN_EX = "LIMIT ?, ?"; public static String resolveLimit(String pageSql) { if (pageSql == null) { return null; } if (pageSql.contains(SQL_SIGN)) {// 如果需要特殊分页 String changer = ""; if (pageSql.contains(LIMIT_SIGN_EX)) { changer = LIMIT_SIGN_EX; } else if (pageSql.contains(LIMIT_SIGN)) { changer = LIMIT_SIGN; } pageSql = pageSql.replace(changer, ""); StringBuilder sqlBuilder = new StringBuilder(pageSql); StringBuilder mae = new StringBuilder(sqlBuilder.substring(0, sqlBuilder.indexOf(SQL_SIGN)));// mae 截止sql语句到 limitable StringBuilder uShiRo = new StringBuilder(sqlBuilder.substring(sqlBuilder.indexOf(SQL_SIGN), sqlBuilder.length()));// 剩余的 mae.insert(mae.lastIndexOf(")"), String.format(" %s", changer)); return mae.append(uShiRo) .toString(); } else { return pageSql; } } } 3、新增配置文件如下 注意，这里配置了我们的CustomPageInterceptor（2步骤里的那个类）作为插件，如果不进行此处配置，我们的修改是不起作用的。
/** * Created by Anur IjuoKaruKas on 2017/12/13. */ @Configuration public class MybatisConfiguration { @Bean public SqlSessionFactory sqlSessionFactoryBean(@SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection") DataSource dataSource) throws Exception { SqlSessionFactoryBean factory = new SqlSessionFactoryBean(); factory.setDataSource(dataSource); factory.setTypeAliasesPackage(Constant.MODEL_PACKAGE); // 配置分页插件，详情请查阅官方文档 PageHelperProperties properties = new PageHelperProperties(); properties.setPageSizeZero("true");// 分页尺寸为0时查询所有纪录不再执行分页 properties.setReasonable("true");// 页码<=0 查询第一页，页码>=总页数查询最后一页 properties.setSupportMethodsArguments("true");// 支持通过 Mapper 接口参数来传递分页参数 CustomPageInterceptor pageInterceptor = new CustomPageInterceptor(); pageInterceptor.setProperties(properties.getProperties()); // 添加插件 factory.setPlugins(new Interceptor[]{pageInterceptor}); // 添加XML目录 ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); factory.setMapperLocations(resolver.getResources("classpath:mapper/*.xml")); return factory.getObject(); } }4、sql语句注意事项 在需要被查询的主表上，你需要手动包装一层select，并将其别名设置为limitable。AS必须大写。
SELECT * FROM (SELECT * FROM provider_order) AS limitable LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id这是原语句：
SELECT * FROM provider_order limitable LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id5、查询时注意事项 在查询之前，我们需要将主表的主键（必须唯一）设置到countColumn中。
Page page = PageHelper.startPage(0, 10); page.setCountColumn("DISTINCT po_id"); List<ProviderOrder> providerOrderList = testService.getProviderOrderWithOrderInfoTest("%-%"); 
