前言
项目中(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; } }
问题解决