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 类型的 ms
                countMs = 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);

    @Override
    public 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());
        //处理pageKey
        pageKey.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;
    }

    
    @Override
    public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
        String sql = boundSql.getSql();
        Page page = getLocalPage();
        //支持 order by
        String 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);
    }
    
    
    
    @Override
    public 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 pu
            INNER JOIN xxx ccs ON pu.xx= ccs.xx
            INNER JOIN xxx wui ON wui.xx= pu.xx
	        <where>
	             查询查询条件       
	        </where>
	        /*customizeOrderBy*/
	        /*customizeLimit*/
           ) tmp
  LEFT JOIN xxx ci ON tmp.xx= ci.xx
  LEFT 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) {//第一页查询count
        	PageHelper.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) {//第一页查询count
        	PageHelper.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);
    }