mybatis-plus分页无效, total=0问题(springmvc)

前言

项目中(springmvc)分页采用mybatis-plus, 头一回用, 就遇到了如题问题

pom.xml

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus</artifactId>
    <version>3.2.0</version>
</dependency>

mapper继承了BaseMapper

@Repository
public interface UserMapper extends BaseMapper<UserPo>{
　　// 这里代码没用, 查询采用的BaseMapper中的 IPage<T> selectPage(IPage<T> page, @Param("ew") Wrapper<T> queryWrapper);
}

service层

    @Override
    public SysUserVo list(UserQo qo) {
        QueryWrapper<UserPo> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name", qo.getKeyword())
                .or().
                like("code", qo.getKeyword());
        Page<UserPo> page = new Page<>(qo.getPageNo(), qo.getPageSize());
        IPage<UserPo> userPoIPage = userMapper.selectPage(page, queryWrapper);
        List<UserPo> records = userPoIPage.getRecords();
        long total = userPoIPage.getTotal();// 总记录数
        long pages = userPoIPage.getPages();// 查询结果数
        long pageNo = userPoIPage.getCurrent();// 当前页
        long pageSize = userPoIPage.getSize();// 每页条数
        List<UserVo> userVos = Lists.newArrayList();
        for (UserPo userPo : records) {
            Integer userId = userPo.getId();
            List<RolePo> roles = userMapper.selectRoles(userId);
            UserVo userVo = UserVo.builder().code(userPo.getCode())
                    .name(userPo.getName())
                    .rolePo(roles)
                    .status(userPo.getStatus())
                    .build();
            userVos.add(userVo);
        }
        SysUserVo sysUserVo = SysUserVo.builder()
                .list(userVos)
                .total(total)
                .build();
        return sysUserVo;
    }

结果分页并没有生效, 于是添加拦截器

package com.cebbank.api.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@MapperScan("com.cebbank.api.mapper")
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        paginationInterceptor.setOverflow(true);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        paginationInterceptor.setLimit(100);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        paginationInterceptor.setDialectType("mysql");
        return paginationInterceptor;
    }
}

还是没生效

最后找到解决方案, 在数据源配置中显式添加分页插件

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setDefaultEnumTypeHandler(EnumOrdinalTypeHandler.class);
        sqlSessionFactoryBean.setConfiguration(configuration);
//        sqlSessionFactoryBean.setMapperLocations(resolveMapperLocations());
        sqlSessionFactoryBean.setPlugins(new Interceptor[]{new PaginationInterceptor()});

        return sqlSessionFactoryBean.getObject();
    }

完整配置

package com.cebbank.api.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.EnumOrdinalTypeHandler;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


/**
 * @author linyong
 * @date 2020/7/30 16:38
 * @description 数据源配置
 **/
@Configuration
@EnableAspectJAutoProxy
@MapperScan("com.cebbank.api.mapper")
public class DBConfig {

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.initialSize:5}")
    private Integer initialSize;

    @Value("${spring.datasource.maxActive:50}")
    private Integer maxActive;

    @Value("${spring.datasource.maxWait:60000}")
    private Integer maxWait;

    @Value("${spring.datasource.minIdle:5}")
    private Integer minIdle;

    @Value("${spring.datasource.testWhileIdle:true}")
    private Boolean testWhileIdle;

    @Value("${spring.datasource.testOnBorrow:true}")
    private Boolean testOnBorrow;

    @Value("${spring.datasource.testOnReturn:true}")
    private Boolean testOnReturn;

    @Value("${spring.datasource.validationQuery:select 1}")
    private String validationQuery;

//    @Value("${mybatis.mapper-locations}")
//    private String mapperLocations;


    @Bean
    public DataSource dataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(url);
        druidDataSource.setDriverClassName(driverClassName);
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setMaxWait(maxWait);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setTestWhileIdle(testWhileIdle);
        druidDataSource.setTestOnBorrow(testOnBorrow);
        druidDataSource.setTestOnReturn(testOnReturn);
        druidDataSource.setValidationQuery(validationQuery);
        return druidDataSource;
    }

//    private Resource[] resolveMapperLocations() {
//        ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
//        String[] arr = mapperLocations.split(",");
//        List<String> locations = Arrays.asList(arr);
//        List<Resource> resources = new ArrayList();
//        if (locations != null) {
//            for (String mapperLocation : locations) {
//                try {
//                    Resource[] mappers = resourceResolver.getResources(mapperLocation);
//                    resources.addAll(Arrays.asList(mappers));
//                } catch (IOException e) {
//                    // ignore
//                }
//            }
//        }
//        return resources.toArray(new Resource[resources.size()]);
//    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setDefaultEnumTypeHandler(EnumOrdinalTypeHandler.class);
        sqlSessionFactoryBean.setConfiguration(configuration);
//        sqlSessionFactoryBean.setMapperLocations(resolveMapperLocations());
        sqlSessionFactoryBean.setPlugins(new Interceptor[]{new PaginationInterceptor()});

        return sqlSessionFactoryBean.getObject();
    }


    @Bean
    public DataSourceTransactionManager transactionManager(){
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager();
        transactionManager.setDataSource(dataSource());
        return transactionManager;
    }

}

问题解决

 

 

感谢

【MP插件】- mybatis-plus分页无效、total、pages为0的问题

mybatis plus分页total=0、不计算总数的终极解决方案！！！

SpringMVC+MyBatis分页插件简单实现