MyBatis分页插件PageHelper自定义分页逻辑实现

天瑕

已于 2022-10-01 14:04:04 修改

阅读量4.9k

点赞数 2

分类专栏：工作点滴文章标签： PageHelper PageHelper自定义分页

于 2020-11-29 15:02:35 首次发布

本文链接：https://blog.csdn.net/weixin_30342639/article/details/110300394

版权

工作点滴专栏收录该内容

6 篇文章 1 订阅

订阅专栏

因为PageHelper对于MySQL的分页逻辑采用的是SQL后面追加limit子句的方式，这样在小数据量情况下是没有问题的。但是对于大数据量的时候，比如limit 100000, 10，MySQL的运作机理是查出100010条数据，再抛弃掉前100000条，留下剩余10条数据。所以执行效率并不高。

对于MySQL分页SQL的优化我在之前总结过《MySQL查询优化》，无非就是写个自关联，先走内部的主键索引/覆盖索引，这个效率很高。因为每页的数据一般都是10条，所以查出来的数据在和外部做关联的时候即使没有走索引，也不会慢。

正好在网上也没有搜到相关的自定义实现，所以我就在想能不能自己重写PageHelper中的分页逻辑。在看了看PageHelper源码中相关的实现后发现，PageHelper的分页逻辑是写在了AbstractHelperDialect类中，而不同的数据库分页实现是通过继承实现抽象方法的方式来实现的。对于MySQL的分页就是MySqlDialect类，典型的模板方法模式。

再来查看MySqlDialect类的实现：

可以看到就是重写了父类的两个抽象方法，processPageParameter方法是用来处理分页参数的，这个方法不需要管。而下面的getPageSql方法才是用来拼接分页SQL的，可以看到就是简单追加limit子句的方式。看到这里其实就能想到自己再写一个Dialect类，继承MySqlDialect类，覆写其中的getPageSql方法即可实现想要的自定义分页逻辑：

public class MyMySqlDialect extends MySqlDialect {

    @Override
    public String getPageSql(String sql, Page page, CacheKey pageKey) {
        //实现自定义分页逻辑
    }
}

写完了自定义的分页逻辑后，要想用到它需要做下面的配置：

pagehelper.helperDialect=com.hys.pagehelper.config.MyMySqlDialect

因为暂时没找到相关的抓取SQL中各个部分的源码（比如表名、字段名等等），只看到了分页的参数，所以我决定自己来实现。我是用正则表达式来实现的（正则大法好），我之前也写过讲解正则表达式的文章《Java中的正则表达式概述》，感兴趣的可以查看。同时我之前也写过一个抓取出不同数据库SQL中的源表和目标表的解析器HSP，也是用正则表达式来实现的。

下面演示一下我重写的分页逻辑的执行效果。首先是XML中的SQL：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD//EN Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hys.pagehelper.dao.UserDAO">

    <sql id="allColumns">
        id,
		name,
		sex,
		address
	</sql>

    <select id="list" resultType="com.hys.pagehelper.entity.UserDO">
        SELECT
        <include refid="allColumns"/>
        FROM user
    </select>

</mapper>

可以看到就是需要对user表进行分页处理。使用的话跟原生的PageHelper类的使用是没有区别的，manager层：

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.hys.pagehelper.dao.UserDAO;
import com.hys.pagehelper.entity.Pager;
import com.hys.pagehelper.entity.UserDO;
import com.hys.pagehelper.util.PageHelperUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
/**
 * 用户Manager
 *
 * @author Robert Hou
 * @since 2020年11月28日 10:59
 **/
@Component
public class UserManager {

    @Autowired
    private UserDAO userDAO;

    public Pager<UserDO> list(int pageNum, int pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        Page<UserDO> list = userDAO.list();
        return PageHelperUtils.pageTransform(list);
    }
}

主要看一下重写的自定义分页实现的代码：

import com.github.pagehelper.Page;
import com.github.pagehelper.dialect.helper.MySqlDialect;
import com.hys.pagehelper.exception.ParseException;
import com.hys.pagehelper.util.PageHelperUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.cache.CacheKey;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 自定义MySQL分页逻辑
 *
 * @author Robert Hou
 * @since 2020年11月27日 17:45
 **/
@Slf4j
public class MyMySqlDialect extends MySqlDialect {

    private static final Pattern PATTERN = Pattern.compile("SELECT\\s*([\\s|\\S]*?)\\s*?((FROM\\s*[0-9a-zA-Z_`]*)\\s*[\\s|\\S]*)", Pattern.CASE_INSENSITIVE);
    private static final Pattern CONTAINS_JOIN_PATTERN = Pattern.compile("[\\s|\\S]*JOIN[\\s|\\S]*", Pattern.CASE_INSENSITIVE);
    private static final Pattern CONTAINS_DISTINCT_PATTERN = Pattern.compile("\\s+DISTINCT\\s+", Pattern.CASE_INSENSITIVE);
    private static final Pattern CONTAINS_ALIAS_PATTERN = Pattern.compile("\\s*(\\S+)(\\s+AS\\s+\\S+)?\\s*", Pattern.CASE_INSENSITIVE);
    private static final Pattern CONTAINS_GROUP_BY_PATTERN = Pattern.compile("[\\s|\\S]*GROUP\\s+BY[\\s|\\S]*", Pattern.CASE_INSENSITIVE);
    private static final Pattern CONTAINS_ORDER_BY_PATTERN = Pattern.compile("[\\s|\\S]*ORDER\\s+BY[\\s|\\S]*", Pattern.CASE_INSENSITIVE);

    @Override
    public String getPageSql(String sql, Page page, CacheKey pageKey) {
        Matcher containsJoinMatcher = CONTAINS_JOIN_PATTERN.matcher(sql);
        if (containsJoinMatcher.find() || BooleanUtils.isTrue(PageHelperUtils.getIsRelegated())) {
            //多表分页逻辑没实现，用默认的SQL后面追加LIMIT子句的方式（对于不是JOIN方式来进行表连接的SQL（比如笛卡尔积），执行可能会报错。这个时候需要手动将降级选项置为true）
            log.info("使用了多表联查的SQL、或是手动将降级选项置为true的SQL，不会进行优化，而是转而使用默认的SQL后面追加LIMIT子句的方式");
            return invokeSuperMethod(sql, page, pageKey);
        }
        Matcher containsGroupByMatcher = CONTAINS_GROUP_BY_PATTERN.matcher(sql);
        Matcher containsOrderByMatcher = CONTAINS_ORDER_BY_PATTERN.matcher(sql);
        if (containsGroupByMatcher.find() || containsOrderByMatcher.find()) {
            //todo GROUP_BY和ORDER_BY语句没有适配，暂时先降级
            return invokeSuperMethod(sql, page, pageKey);
        }

        log.info("\n原始SQL：\n{}", sql);
        List<String> keyNames = PageHelperUtils.getKeyNames();
        if (CollectionUtils.isEmpty(keyNames)) {
            //没有添加@KeyNamesStrategy注解，也将表主键名设置为”id“
            PageHelperUtils.setKeyNames(new String[]{"id"});
            keyNames = PageHelperUtils.getKeyNames();
        }

        String fromTable = null;
        String fields = null;
        String afterClause = null;
        boolean isSucceeded = false;

        Matcher m = PATTERN.matcher(sql);

        boolean isDistinctContains = false;
        if (m.find()) {
            isSucceeded = true;

            //SELECT后面FROM前面的查找字段
            fields = m.group(1);

            if (isOnlyContainsPrimaryKey(fields)) {
                //如果字段中只包含主键的话，则不需要优化（因为要查的字段都在B+树上，不需要回表进行查询）。改用默认的SQL后面追加LIMIT子句的方式
                log.info("要查询的字段中只包含主键，不需要优化，转而使用默认的SQL后面追加LIMIT子句的方式");
                return invokeSuperMethod(sql, page, pageKey);
            }

            if (fields != null) {
                //查看SQL中是否含有DISTINCT
                Matcher containsDistinctMatcher = CONTAINS_DISTINCT_PATTERN.matcher(sql);
                if (containsDistinctMatcher.find()) {
                    isDistinctContains = true;
                }

                StringBuilder stringBuilder = new StringBuilder();
                for (String keyName : keyNames) {
                    String regex = "[\\s]*[^\\S]+((`)?" + keyName + "(`)?)[^\\S]+[\\s]*";
                    Pattern containsPattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
                    String[] fieldArray = fields.split(",");
                    List<String> fieldList = new ArrayList<>(fieldArray.length);
                    for (String field : fieldArray) {
                        stringBuilder.delete(0, stringBuilder.length());
                        field = stringBuilder.append(" ").append(field).append(" ").toString();
                        Matcher matcher = containsPattern.matcher(field);
                        if (matcher.find()) {
                            String keyNameAndBackQuoteIfContains = matcher.group(1).toUpperCase();
                            //只替换第一个是为了解决表主键起别名的情况
                            fieldList.add(field.toUpperCase().replaceFirst(keyNameAndBackQuoteIfContains, "pageHelperAlias1." + keyNameAndBackQuoteIfContains));
                        } else {
                            fieldList.add(field);
                        }
                    }
                    if (!fieldList.isEmpty()) {
                        fields = String.join(",", fieldList);
                    }
                }
            }

            //FROM+后面的子句
            afterClause = m.group(2);
            if (page.getStartRow() == 0) {
                afterClause = afterClause + "\n LIMIT ? ";
            } else {
                afterClause = afterClause + "\n LIMIT ?, ? ";
            }

            //FROM+表名
            fromTable = m.group(3);
        }
        if (!isSucceeded) {
            throw new ParseException("解析失败！需要排查SQL！");
        }

        String returnSql = "SELECT " + fields + " " + fromTable + " pageHelperAlias1 \n" +
                " INNER JOIN ( SELECT " + (isDistinctContains ? "DISTINCT " : "") + getKeyNames(keyNames) + " " + afterClause + " ) pageHelperAlias2"
                + joinKeyNames(keyNames);
        log.info("\n拼接后的分页SQL：\n{}", returnSql);

        PageHelperUtils.remove();
        return returnSql;
    }

    private String invokeSuperMethod(String sql, Page<?> page, CacheKey pageKey) {
        PageHelperUtils.remove();
        return super.getPageSql(sql, page, pageKey);
    }

    /**
     * KeyNames转换成String格式（逗号拼接）
     */
    private String getKeyNames(List<String> keyNames) {
        if (CollectionUtils.isEmpty(keyNames)) {
            return null;
        }

        StringBuilder stringBuilder = new StringBuilder();
        for (String keyName : keyNames) {
            stringBuilder.append(keyName.trim()).append(", ");
        }
        stringBuilder.deleteCharAt(stringBuilder.length() - 2);
        return stringBuilder.toString();
    }

    /**
     * KeyNames转换成SQL JOIN的关联格式
     */
    private String joinKeyNames(List<String> keyNames) {
        if (CollectionUtils.isEmpty(keyNames)) {
            return null;
        }

        StringBuilder stringBuilder = new StringBuilder(" ON ");
        for (int i = 0; i < keyNames.size(); i++) {
            keyNames.set(i, keyNames.get(i).trim());
            stringBuilder.append("pageHelperAlias1.").append(keyNames.get(i)).append(" = pageHelperAlias2.").append(keyNames.get(i));
            if (i != keyNames.size() - 1) {
                stringBuilder.append(" AND ");
            }
        }
        return stringBuilder.toString();
    }

    /**
     * 判断字段中是否只包含主键
     */
    private boolean isOnlyContainsPrimaryKey(String fields) {
        if (StringUtils.isBlank(fields)) {
            return false;
        }

        List<String> keyNames = PageHelperUtils.getKeyNames();
        if (CollectionUtils.isEmpty(keyNames)) {
            return false;
        }
        //考虑keyNames有可能有重复主键名的情况 e.g.@KeyNamesStrategy(keyNames = {"order_id", "order_id"})
        List<String> uniqueKeyNames = unique(keyNames);
        String[] fieldArray = fields.split(",");
        List<String> uniqueFieldArray = unique(fieldArray);
        int length = 0;
        for (String field : uniqueFieldArray) {
            if (!uniqueKeyNames.contains(field)) {
                return false;
            }
            length++;
        }
        return length == uniqueKeyNames.size();
    }

    private String removeAliasAndBackQuoteIfContains(String field) {
        if (StringUtils.isBlank(field)) {
            return StringUtils.EMPTY;
        }

        Matcher matcher = CONTAINS_ALIAS_PATTERN.matcher(field);
        if (matcher.find()) {
            String keyNameAndBackQuoteIfContains = matcher.group(1);
            if (keyNameAndBackQuoteIfContains.startsWith("`") && keyNameAndBackQuoteIfContains.endsWith("`")) {
                return keyNameAndBackQuoteIfContains.substring(1, keyNameAndBackQuoteIfContains.length() - 1);
            }
            return keyNameAndBackQuoteIfContains;
        }
        return field;
    }

    private List<String> unique(List<String> list) {
        if (CollectionUtils.isEmpty(list)) {
            return Collections.emptyList();
        }

        return list.stream().map(this::removeAliasAndBackQuoteIfContains).distinct().collect(Collectors.toList());
    }

    private List<String> unique(String[] array) {
        if (ArrayUtils.isEmpty(array)) {
            return Collections.emptyList();
        }

        List<String> list = Arrays.stream(array).collect(Collectors.toList());
        return unique(list);
    }
}

下面演示一下执行效果：

原始SQL：
SELECT
         
        id,
		name,
		sex,
		address
	 
        FROM user
拼接后的分页SQL：
SELECT pageHelperAlias1.id,
		name,
		sex,
		address FROM user pageHelperAlias1 
 INNER JOIN ( SELECT id  FROM user
 LIMIT ?, ?  ) pageHelperAlias2 ON pageHelperAlias1.id = pageHelperAlias2.id
==>  Preparing: SELECT pageHelperAlias1.id, name, sex, address FROM user pageHelperAlias1 INNER JOIN ( SELECT id FROM user LIMIT ?, ? ) pageHelperAlias2 ON pageHelperAlias1.id = pageHelperAlias2.id
==> Parameters: 10(Long), 10(Integer)
<==      Total: 3

上面是后台打印的日志，因为我这里的user表的主键是id字段，所以可以看到是能成功分页出来的。而默认的PageHelper类的MySQL分页是简单通过后面追加limit子句的方式来实现的。

当然我这里的假设是表的主键名一定是“id”。而如果表的主键名不是“id”的话，比如“order_id”，我这里也做了支持。我使用了Spring AOP（自定义注解和环绕通知），只需要在manager层上加上我封装的自定义注解@KeyNamesStrategy就行了：

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.hys.pagehelper.annotation.KeyNamesStrategy;
import com.hys.pagehelper.dao.UserDAO;
import com.hys.pagehelper.entity.Pager;
import com.hys.pagehelper.entity.UserDO;
import com.hys.pagehelper.util.PageHelperUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 * 用户Manager
 *
 * @author Robert Hou
 * @since 2020年11月28日 10:59
 **/
@Component
public class UserManager {

    @Autowired
    private UserDAO userDAO;

    @KeyNamesStrategy(keyNames = "name")
    public Pager<UserDO> list(int pageNum, int pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        Page<UserDO> list = userDAO.list();
        return PageHelperUtils.pageTransform(list);
    }
}

我这里是假设user表的主键名是“name”，而不再是“id”，再来看一下后台打印的日志：

原始SQL：
SELECT
         
        id,
		name,
		sex,
		address
	 
        FROM user
拼接后的分页SQL：
SELECT id,
		pageHelperAlias1.name,
		sex,
		address FROM user pageHelperAlias1 
 INNER JOIN ( SELECT name  FROM user
 LIMIT ?, ?  ) pageHelperAlias2 ON pageHelperAlias1.name = pageHelperAlias2.name
==>  Preparing: SELECT id, pageHelperAlias1.name, sex, address FROM user pageHelperAlias1 INNER JOIN ( SELECT name FROM user LIMIT ?, ? ) pageHelperAlias2 ON pageHelperAlias1.name = pageHelperAlias2.name
==> Parameters: 10(Long), 10(Integer)
<==      Total: 3

可以看到也是能成功分页出来的。哪怕是联合主键，比如user表中的“id”和“name”两个字段联合做主键，也可以这么写：

@KeyNamesStrategy(keyNames = {"id", "name"})

再来看一下后台打印的日志：

原始SQL：
SELECT
         
        id,
		name,
		sex,
		address
	 
        FROM user
拼接后的分页SQL：
SELECT pageHelperAlias1.id,
		pageHelperAlias1.name,
		sex,
		address FROM user pageHelperAlias1 
 INNER JOIN ( SELECT id, name  FROM user
 LIMIT ?, ?  ) pageHelperAlias2 ON pageHelperAlias1.id = pageHelperAlias2.id AND pageHelperAlias1.name = pageHelperAlias2.name
==>  Preparing: SELECT pageHelperAlias1.id, pageHelperAlias1.name, sex, address FROM user pageHelperAlias1 INNER JOIN ( SELECT id, name FROM user LIMIT ?, ? ) pageHelperAlias2 ON pageHelperAlias1.id = pageHelperAlias2.id AND pageHelperAlias1.name = pageHelperAlias2.name
==> Parameters: 10(Long), 10(Integer)
<==      Total: 3

可以看到也是能成功分页出来的。相关的代码我已经上传到GitHub上了，地址：https://github.com/ACoolMonkey/PageHelper

介于测试的数据量不大，可能会有bug。同时目前仅支持MySQL单表的分页操作，对于多表连接的分页需求，我这里暂时是没有自定义实现的，会退化成默认的追加limit子句的方式来实现（如果一定要实现，是否可以考虑使用视图View来实现？）。而且对于笛卡尔积那种没有写“JOIN”直接进行连接的SQL，我这里执行可能会报错，这点等以后有时间了再去研究吧，其实对于笛卡尔积来说都可以把SQL改为“JOIN”的方式。如果不想改动的话，我这里也提供一个降级选项：isRelegated。默认值为false，如果设置为true的话，这条SQL就会完全走默认的PageHelper分页的逻辑，而不会走我自定义的分页逻辑（性能要低一些，但是起码能保证不出错）：

@KeyNamesStrategy(isRelegated = true)

原创不易，未得准许，请勿转载，翻版必究

天瑕

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
7
评论
MyBatis分页插件PageHelper自定义分页逻辑实现

同时目前仅支持MySQL单表的分页操作，对于多表连接的分页需求，我这里暂时是没有自定义实现的，会退化成默认的追加limit子句的方式来实现（如果一定要实现，是否可以考虑使用视图View来实现？在看了看PageHelper源码中相关的实现后发现，PageHelper的分页逻辑是写在了AbstractHelperDialect类中，而不同的数据库分页实现是通过继承实现抽象方法的方式来实现的。上面是后台打印的日志，因为我这里的user表的主键是id字段，所以可以看到是能成功分页出来的。，感兴趣的可以查看。
复制链接

扫一扫

专栏目录