依赖包版本
com.baomidou:mybatis-plus-boot-starter:jar:3.5.5
com.baomidou:mybatis-plus-extension:jar:3.5.5
com.github.pagehelper:pagehelper-spring-boot-starter:jar:2.0.0
com.github.jsqlparser:jsqlparser:jar:4.5
com.github.pagehelper:sqlparser4.5:jar:6.1.0
参考文档: 官方分页插件 Mybatis-Plus分页不生效的一些原因总结_mybatisplus 分页不生效-CSDN博客 mybatisplus github.pagehelper的 jsqlparser冲突 - CSDN文库 MybatisPlus 分页查询不生效,拦截器无效的解决方案_mybatisplus拦截器不生效-CSDN博客 升级到最新版本后出现PageHelperAutoConfiguration Bad return type
现象
使用 `getMapper().selectPage(pagePlus, queryWrapper)` 查询时,没有分页,即SQL里面没有limit,同时也没有查询总条数,即 total为0;
public Page<T> selectPage(T entity, Page<T> page) { com.baomidou.mybatisplus.extension.plugins.pagination.Page<T> pagePlus = new com.baomidou.mybatisplus.extension.plugins.pagination.Page<>(page.getPageNum(), page.getPageSize()); QueryWrapper<T> queryWrapper = new QueryWrapper<>(entity); queryWrapper.orderBy(StringUtils.isNotEmpty(page.getOrderBy()) , page.getIsAsc().equals(SqlKeyword.ASC.getSqlSegment()), page.getOrderBy()); com.baomidou.mybatisplus.extension.plugins.pagination.Page<T> tPage = getMapper().selectPage(pagePlus, queryWrapper); if (ObjectUtil.isNotNull(tPage)) { return new Page<>(tPage.getRecords(), tPage.getTotal()); } return Page.empty(); }
解决方法
- 把mybatis-plus相关依赖包的版本由3.5.7 改为3.5.5 ,(com.baomidou:mybatis-plus-boot-starter:jar:3.5.5)
- 修改mybatis 配置类,设置拦截器的方法不对.
old(有问题/错误):
sqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor);
解决之后:
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBean.getObject(); org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration(); if (mybatisPlusInterceptor != null) { configuration.addInterceptor(mybatisPlusInterceptor); }
- 去掉application.properties中的 `pagehelper.helperDialect=mysql` --解决 Invocation of init method failed; nested exception is java.lang.VerifyError: Bad return type
参考:
升级到最新版本后出现PageHelperAutoConfiguration Bad return type
完整代码:
public MybatisPlusInterceptor mybatisPlusInterceptor(PaginationInnerInterceptor paginationInnerInterceptor) { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(paginationInnerInterceptor/*new PaginationInnerInterceptor(DbType.MYSQL)*/); // 根据你的数据库类型选择 return interceptor; } public PaginationInnerInterceptor getPaginationInnerInterceptor() { return new PaginationInnerInterceptor(DbType.MYSQL); } name = "sqlSessionFactory") ( public SqlSessionFactory sqlSessionFactory(/*@Autowired*/ DataSource dataSource /*@Value("classpath:mybatis-config.xml") Resource configLocation, @Value("classpath:sqlmapper/*Mapper.xml") Resource[] mapperLocations*/, MybatisPlusInterceptor mybatisPlusInterceptor) throws Exception { MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource); PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); //mapperLocation配置 Resource[] resourcesWms = resolver.getResources("classpath*:/mapper/*Mapper.xml"); Resource[] resourcesGform = resolver.getResources("classpath*:com//jobbook/mapper/xml/*Mapper.xml"); Resource[] resourcesruoyiSys = resolver.getResources("classpath*:mapper/bbb/*Mapper.xml"); Resource[] resourcesruoyiFlow = resolver.getResources("classpath*:mapper/ccc/*Mapper.xml"); List<Resource> resourceList = Lists.newArrayList(resources); // resourceList.addAll(Arrays.asList(resourcesWms)); resourceList.addAll(Arrays.asList(resourcesGform)); resourceList.addAll(Arrays.asList(resourcesruoyiSys)); resourceList.addAll(Arrays.asList(resourcesruoyiFlow)); sqlSessionFactoryBean.setMapperLocations(resourceList.toArray(new Resource[]{})); SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBean.getObject(); org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration(); if (mybatisPlusInterceptor != null) { configuration.addInterceptor(mybatisPlusInterceptor); } // sqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor); // 取得类型转换注册器 TypeHandlerRegistry typeHandlerRegistry = sqlSessionFactory.getConfiguration().getTypeHandlerRegistry(); // 注册默认枚举转换器 typeHandlerRegistry.setDefaultEnumTypeHandler(org.apache.ibatis.type.EnumTypeHandler.class); return sqlSessionFactoryBean.getObject(); }
分页SQL示例
[com.industri.jobbook.aop.FlowDAOAop:process:31] - FlowDAOAop before: selectPage, args: [{"nodeList":[],"userList":[]},{"isAsc":"desc","orderBy":"create_time","pageNum":0,"pageSize":1,"total":0}] 2024-09-17 12:28:35.222 INFO [traceId: ]-[http-nio-8080-exec-1] [com.alibaba.druid.pool.DruidDataSource:init:928] - {dataSource-1} inited 2024-09-17 12:28:40.909 DEBUG [traceId: ]-[http-nio-8080-exec-1] [org.apache.ibatis.logging.jdbc.BaseJdbcLogger:debug:135] - ==> Preparing: SELECT COUNT(*) AS total FROM flow_definition WHERE del_flag = '0' 2024-09-17 12:28:40.933 DEBUG [traceId: ]-[http-nio-8080-exec-1] [org.apache.ibatis.logging.jdbc.BaseJdbcLogger:debug:135] - ==> Parameters: 2024-09-17 12:28:41.482 DEBUG [traceId: ]-[http-nio-8080-exec-1] [org.apache.ibatis.logging.jdbc.BaseJdbcLogger:debug:135] - <== Total: 1 2024-09-17 12:28:41.486 DEBUG [traceId: ]-[http-nio-8080-exec-1] [org.apache.ibatis.logging.jdbc.BaseJdbcLogger:debug:135] - ==> Preparing: SELECT id,create_time,update_time,tenant_id,del_flag,flow_code,flow_name,version,is_publish,form_custom,form_path,activity_status,listener_type,listener_path,ext FROM flow_definition WHERE del_flag='0' ORDER BY create_time DESC LIMIT ? 2024-09-17 12:28:41.487 DEBUG [traceId: ]-[http-nio-8080-exec-1] [org.apache.ibatis.logging.jdbc.BaseJdbcLogger:debug:135] - ==> Parameters: 1(Long) 2024-09-17 12:28:42.017 DEBUG [traceId: ]-[http-nio-8080-exec-1] [org.apache.ibatis.logging.jdbc.BaseJdbcLogger:debug:135] - <== Total: 1