pagehelper 优化自定义分页和排序位置

pagehelper开源地址

https://github.com/pagehelper/Mybatis-PageHelper

1.手写Count查询优化

源码分页count时首先是判断是否存在手写的 {业务查询id}_COUNT 的查询count统计

private Long count(Executor executor, MappedStatement ms, Object parameter,RowBounds rowBounds, ResultHandler resultHandler,BoundSql boundSql) throws SQLException {String countMsId = ms.getId() + countSuffix;Long count;//先判断是否存在手写的 count 查询MappedStatement countMs = ExecutorUtil.getExistedMappedStatement(ms.getConfiguration(), countMsId);if (countMs != null) {count = ExecutorUtil.executeManualCount(executor, countMs, parameter, boundSql, resultHandler);} else {if (msCountMap != null) {countMs = msCountMap.get(countMsId);}//自动创建if (countMs == null) {//根据当前的 ms 创建一个返回值为 Long 类型的 mscountMs = MSUtils.newCountMappedStatement(ms, countMsId);if (msCountMap != null) {msCountMap.put(countMsId, countMs);}}count = ExecutorUtil.executeAutoCount(this.dialect, executor, countMs, parameter, boundSql, rowBounds, resultHandler);}return count;}

手写count用法：

<select id="selectReportList"  parameterType="xxxx" resultMap="xxx">****业务sql*****
</select >
<select id="selectReportList_COUNT"  parameterType="xxxx" resultType="java.lang.Integer">	****业务sql手写优化count*****
</select >

2.自定义分页排序位置

分页查询时复杂业务需要多表关联查询，关联表越多越影响查询效率；根据业务可将不影响查询结果的表分页后再查询需要自定义分页或排序位置
以mysql为例：

package com.github.pagehelper.dialect.helper;import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.RowBounds;import com.github.pagehelper.Page;
import com.github.pagehelper.dialect.AbstractHelperDialect;
import com.github.pagehelper.parser.OrderByParser;
import com.github.pagehelper.util.MetaObjectUtil;
import com.github.pagehelper.util.StringUtil;/*** 重写MySqlDialect满足自定义分页需求，* 类放在package 为 com.github.pagehelper.dialect.helper包覆盖源   码才生效*/
public class MySqlDialect extends AbstractHelperDialect {/*** 自定义分页key:*//*customizeLimit*/public static final String CUSTOMIZE_LIMIT="/\\*customizeLimit\\*/";	public static final Pattern customizeLimitFixed = Pattern.compile(CUSTOMIZE_LIMIT);/*** 自定义排序 key:*//*customizeOrderBy*/public static final String CUSTOMIZE_ORDERBY="/\\*customizeOrderBy\\*/";	public static final Pattern customizeOrderbyFixed = Pattern.compile(CUSTOMIZE_ORDERBY);@Overridepublic Object processPageParameter(MappedStatement ms, Map<String, Object> paramMap, Page page, BoundSql boundSql, CacheKey pageKey) {paramMap.put(PAGEPARAMETER_FIRST, page.getStartRow());paramMap.put(PAGEPARAMETER_SECOND, page.getPageSize());//处理pageKeypageKey.update(page.getStartRow());pageKey.update(page.getPageSize());//处理参数配置if (boundSql.getParameterMappings() != null) {List<ParameterMapping> newParameterMappings = new ArrayList<ParameterMapping>(boundSql.getParameterMappings());if (page.getStartRow() == 0) {newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, int.class).build());} else {newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_FIRST, long.class).build());newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, int.class).build());}MetaObject metaObject = MetaObjectUtil.forObject(boundSql);metaObject.setValue("parameterMappings", newParameterMappings);}return paramMap;}@Overridepublic String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {String sql = boundSql.getSql();Page page = getLocalPage();//支持 order byString orderBy = page.getOrderBy();if (StringUtil.isNotEmpty(orderBy)) {          	pageKey.update(orderBy);if(customizeOrderbyFixed.matcher(sql).find()) {//自定义排序位置替换  		       		sql = sql.replaceFirst(CUSTOMIZE_ORDERBY, " order by " + orderBy);}else {//源码逻辑sql = OrderByParser.converToOrderBySql(sql, orderBy);}}if (page.isOrderByOnly()) {return sql;}return getPageSql(sql, page, pageKey);}@Overridepublic String getPageSql(String sql, Page page, CacheKey pageKey) {if(customizeLimitFixed.matcher(sql).find()) {//自定义分页位置替换if (page.getStartRow() == 0) {sql = sql.replaceFirst(CUSTOMIZE_LIMIT, " LIMIT ? ");} else {sql = sql.replaceFirst(CUSTOMIZE_LIMIT, " LIMIT ?, ? ");}return sql;}else {//源码逻辑StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);sqlBuilder.append(sql);if (page.getStartRow() == 0) {sqlBuilder.append("\n LIMIT ? ");} else {sqlBuilder.append("\n LIMIT ?, ? ");}return sqlBuilder.toString();}}}

用法示例

<select id="selectReportList" parameterType="xxxxx" resultType="xxxxx">SELECT tmp.*, ci.xx, ma.xx, mb.xx FROM(SELECT  查询结果FROM xxx puINNER JOIN xxx ccs ON pu.xx= ccs.xxINNER JOIN xxx wui ON wui.xx= pu.xx<where>查询查询条件       </where>/*customizeOrderBy*//*customizeLimit*/) tmpLEFT JOIN xxx ci ON tmp.xx= ci.xxLEFT JOIN xxx ma ON tmp.xx= ma.xx LEFT JOIN xxx mb ON tmp.xx = mb.xx </select>

3.控制查询是否需要Count

• 场景：业务中分页查询列表数据变化实时性不高，分页查询时只有查询第一页才统计count，避免重复的count统计

修改设置请求分页数据方法

    /*** 设置请求分页数据(仅第一页查询count数)*/public static void startPageNoCount(){PageDomain pageDomain = TableSupport.buildPageRequest();Integer pageNum = pageDomain.getPageNum();Integer pageSize = pageDomain.getPageSize();String orderBy = SqlUtil.escapeOrderBySql(pageDomain.getOrderBy());Boolean reasonable = pageDomain.getReasonable();if(pageNum.intValue()==1) {//第一页查询countPageHelper.startPage(pageNum, pageSize, orderBy).setReasonable(reasonable);}else {PageHelper.startPage(pageNum, pageSize, false).setOrderBy(orderBy).setReasonable(reasonable);}}/*** 设置请求分页数据(仅第一页查询count数)*/public static void startPageNoCount(PageDomain pageDomain){pageDomain.setPageNum(Convert.toInt(pageDomain.getPageNum(), 1));pageDomain.setPageSize(Convert.toInt(pageDomain.getPageSize(), 10));Integer pageNum = pageDomain.getPageNum();Integer pageSize = pageDomain.getPageSize();String orderBy = SqlUtil.escapeOrderBySql(pageDomain.getOrderByNoScoreCase());//直接驼峰参数拼接Boolean reasonable = pageDomain.getReasonable();if(pageNum.intValue()==1) {//第一页查询countPageHelper.startPage(pageNum, pageSize, orderBy).setReasonable(reasonable);}else {PageHelper.startPage(pageNum, pageSize, false).setOrderBy(orderBy).setReasonable(reasonable);}}

原理基于 PageMethod.class 中startPage 方法控制实现

    /*** 开始分页** @param pageNum  页码* @param pageSize 每页显示数量* @param count    是否进行count查询*/public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count) {return startPage(pageNum, pageSize, count, null, null);}