实战Spring boot(二) : PageHelper分页插件使用

我不是女神ヾ 2022-05-14 10:38 396阅读 0赞

**1、引入依赖 pagehlper4.x版本**

<dependency>
                    <groupId>com.github.pagehelper</groupId>
                    <artifactId>pagehelper</artifactId>
                    <version>4.1.0</version>
                </dependency>

**2、增加配置文件**

@Configuration
                public class MyBatisConfig { 
                    @Bean
                    public PageHelper pageHelper(){
                        PageHelper pageHelper = new PageHelper();
                        Properties p = new Properties();
                        p.setProperty("offsetAsPageNum","true");
                        p.setProperty("rowBoundsWithCount","true");
                        p.setProperty("reasonable","true");
                        pageHelper.setProperties(p);
                        return pageHelper;
                    }
                }

**3、包装**  
PageHelper.startPage(page, size);

PageInfo<VideoOrder> pageInfo = new PageInfo<>(list);

**4、基本原理**  
sqlsessionFactory -> sqlSession-> executor -> mybatis sql statement  
通过mybatis plugin 增加拦截器，然后拼装分页  
org.apache.ibatis.plugin.Interceptor

**5.pagehelper版本升级到5.x**

众所周知,插件的小版本更迭都是修修补补,不会做源码上的重构,大版本更迭往往伴随者代码重构,规则修改,例如MySql8.0和5.0版本存在天壤之别,废了一大票第三方连接工具,在pagehelpr5.0版本中,官方用**PageInterceptor**类代替了**PageHelper**类

pageHelper是如何在mybatis中工作呢，,解读源码我们可知,是通过mybatis的pulgin实现了Interceptor接口，从而获得要执行的sql语句实现分页技术，而我们的PageHelper5.0.0版本中的这个类，并没有出现implements Interceptor，我们再来看下pagehelper这个包下的其他类，我们发现，有个类很像我们要的，我们进去一看，果然是它：

//
    // Source code recreated from a .class file by IntelliJ IDEA
    // (powered by Fernflower decompiler)
    //
    
    package com.github.pagehelper;
    
    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;
    
    @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 PageInterceptor 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 PageInterceptor() {
        }
    
        public Object intercept(Invocation invocation) throws Throwable {
            try {
                Object[] args = invocation.getArgs();
                MappedStatement ms = (MappedStatement)args[0];
                Object parameter = args[1];
                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 = 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);
                        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);
            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);
            }
        }
    }

综上所述,pagehelper5.0只需要将我们的分页源码改成 PageInterceptor 类型的就好了.

@Configuration
    public class MyBatisConfig { 
        @Bean
        public PageInterceptor pageHelper() {
            PageInterceptor pageHelper = new PageInterceptor();
            Properties p = new Properties();
    
            // 设置为true时，会将RowBounds第一个参数offset当成pageNum页码使用
            p.setProperty("offsetAsPageNum", "true");
    
            //设置为true时，使用RowBounds分页会进行count查询
            p.setProperty("rowBoundsWithCount", "true");
            p.setProperty("reasonable", "true");
            pageHelper.setProperties(p);
            return pageHelper;
        }