解决Mybatis-Plus操作分页后数据失效问题

Z时代
2024-01-10
分类：综合

业务场景

我们知道在使用PageHelper分页插件时，会对执行PageHelper.startPage(pageNum, pageSize);方法后的第一条查询语句进行分页操作。在开发中总会遇到这样的业务情景，在进行分页查询后，需要对获得的列表数据包装成另一种类型，此时需要对新类型的列表进行分页，然而由于PageInfo<T>因为泛型的原因，导致处理后的列表不能加入到该类中。

如，我在数据库分页后查询到的类为PageInfo<User>，此时改类中的list属性为User，在当前的业务场景下，我需要将User类型替换为UserVo类型。

当我们对list属性进行操作时，会同时导致PageInfo类中的其他属性发生改变，如total等。

解决方法


//从数据库获得的Users分页列表
//当我们打印users时，会发现打印出来的数据除了列表数据还有分页属性
List<User> users = this.list();
//操作users获得的需要的列表
List<UserVo> userVos = this.list2(users);
//创建User的info类，此时page中的属性已生成
PageInfo<User> userPage = new PageInfo<>(users);
//创建UserVo的info类
PageInfo<UserVo> userVoPage= new PageInfo<>(userVos );
//将userPage中除看list外的其他属性复制到userVoPage中
PageInfoUtil pageInfoUtil = new PageInfoUtil();
pageInfo = pageInfoUtil.getPageVo(userPage ,userVoPage);


pageInfoUtil
public class PageInfoUtil {
 public PageInfo getPageVo(PageInfo pageInfoPo,PageInfo pageInfoVo){
  pageInfoVo.setTotal(pageInfoPo.getTotal());
  pageInfoVo.setEndRow(pageInfoPo.getEndRow());
  pageInfoVo.setHasNextPage(pageInfoPo.isHasNextPage());
  pageInfoVo.setHasPreviousPage(pageInfoPo.isHasPreviousPage());
  pageInfoVo.setIsFirstPage(pageInfoPo.isIsFirstPage());
  pageInfoVo.setIsLastPage(pageInfoPo.isIsLastPage());
  pageInfoVo.setNavigateFirstPage(pageInfoPo.getNavigateFirstPage());
  pageInfoVo.setNavigateLastPage(pageInfoPo.getNavigateLastPage());
  pageInfoVo.setNavigatePages(pageInfoPo.getNavigatePages());
  pageInfoVo.setNavigatepageNums(pageInfoPo.getNavigatepageNums());
  pageInfoVo.setNextPage(pageInfoPo.getNextPage());
  pageInfoVo.setPageNum(pageInfoPo.getPageNum());
  pageInfoVo.setPages(pageInfoPo.getPages());
  pageInfoVo.setPageSize(pageInfoPo.getPageSize());
  pageInfoVo.setPrePage(pageInfoPo.getPrePage());
  pageInfoVo.setSize(pageInfoPo.getSize());
  pageInfoVo.setStartRow(pageInfoPo.getStartRow());
  pageInfoVo.setFirstPage(pageInfoPo.getFirstPage());
  pageInfoVo.setLastPage(pageInfoPo.getLastPage());
  return pageInfoVo;
 }
}

End

想使用秀一点的方法，结果能力有限，没有成功，只能使用这种笨方法，如果有更好的方法，欢迎各位指导。

补充知识：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;
 }
}