mybatis-plus分页插件之count优化

package com.example.demo.conf;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan(basePackages = "com.example.demo.mapper")
public class MybatisPlusConfig {

    //配置分页插件注入容器
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}

 @PostMapping("/selectPage")
    public IPage<SplitExperimentType> selectAll(){
        Page page = new Page();
        page.setCurrent(1L);
        page.setSize(2L);
        IPage<SplitExperimentType> list = service.selectAll(page);
        return list;
    }

 @Override
    public IPage<SplitExperimentType> selectAll(Page page) {
        return baseMapper.selectAll(page);
    }

IPage<SplitExperimentType> selectAll(IPage page);

<select id="selectAll" resultType="com.example.demo.model.SplitExperimentType">
    select * from split_experiment_type
</select>

第一步：select count(*) from (select * from split_experiment_type);

第二步：select * from split_experiment_type limit xxx

package com.baomidou.mybatisplus.extension.plugins.inner;


public class PaginationInnerInterceptor implements InnerInterceptor {
   
    public boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        //获取page对象
        IPage<?> page = (IPage)ParameterUtils.findPage(parameter).orElse((Object)null);
        
        if (page != null && page.getSize() >= 0L && page.searchCount()) {
            //根据page对象的里的countId来查询是否配置了自定义count的方法，countId就是自定义统计sql的mapper里的方法名
            MappedStatement countMs = this.buildCountMappedStatement(ms, page.countId());
            BoundSql countSql;
            //如根据countId能获取MappedStatement，即存在自定义的方法执行，就执行自定义的mapper方法来统计count
            if (countMs != null) {
                
                countSql = countMs.getBoundSql(parameter);
            } 
            //如根据countId不能获取MappedStatement，就自己组装count统计sql
            else {
                countMs = this.buildAutoCountMappedStatement(ms);
                String countSqlStr = this.autoCountSql(page.optimizeCountSql(), boundSql.getSql());
                MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);
                countSql = new BoundSql(countMs.getConfiguration(), countSqlStr, mpBoundSql.parameterMappings(), parameter);
                PluginUtils.setAdditionalParameter(countSql, mpBoundSql.additionalParameters());
            }

            CacheKey cacheKey = executor.createCacheKey(countMs, parameter, rowBounds, countSql);
            List<Object> result = executor.query(countMs, parameter, rowBounds, resultHandler, cacheKey, countSql);
            long total = 0L;
            if (CollectionUtils.isNotEmpty(result)) {
                Object o = result.get(0);
                if (o != null) {
                    total = Long.parseLong(o.toString());
                }
            }

            page.setTotal(total);
            return this.continuePage(page);
        } else {
            return true;
        }
    }

  ......
   
}

 @Override
    public IPage<SplitExperimentType> selectAll(Page page) {
        //指定自定义统计sql的方法名
        page.setCountId("selectAll_COUNT");
        return baseMapper.selectAll(page);
    }

IPage<SplitExperimentType> selectAll(IPage page);
Long selectAll_COUNT(IPage page);

<select id="selectAll" resultType="com.example.demo.model.SplitExperimentType">
        select * from split_experiment_type
</select>

<select id="selectAll_COUNT" resultType="java.lang.Long">
        SELECT COUNT(1) FROM split_experiment_type
</select>

第一步：SELECT COUNT(1) FROM split_experiment_type

第二步：select * from split_experiment_type limit xxx