background

When SpringBoot integrates MyBatisPlus pages, invalid pages can occur if the paging plug-in is incorrectly configured, which is common with custom data sources.

There are two kinds of pages in MyBatis, one is to use MyBatisPlus pagination, the other is to use PageHelper pagination plug-in. Either way, you need to set up the paging plug-in configuration for the data source.

If you use SpringBoot’s own data source, you do not need to manually set the paging plug-in. You only need to provide the paging plug-in configuration class. If it is a custom data source, you must configure a paging plug-in for it.

Paging plug-in configuration

First, add a paging plug-in configuration class, MyBatisPlusConfig, to your project with simple code:

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor; import org.mybatis.spring.annotation.MapperScan; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; @Configuration public class MybatisPlusConfig { @Bean public PaginationInterceptor paginationInterceptor(){ return new PaginationInterceptor(); }}Copy the code

Use the default SpringBoot data source

Add the spring. DataSource configuration directly:

spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/db? allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&&serverTimezone=UTC username: root password: 12345 type: com.alibaba.druid.pool.DruidDataSourceCopy the code

Note that the key of the database connection address is the URL, and the paging plug-in of MyBatisPlus takes effect automatically.

Use custom data sources

When you customize the data source, you need to write the data source class and specify the data source configuration information through the @ConfigurationProperties annotation. In this case, you need to set the MyBatisPlus paging plug-in separately for the data source, otherwise the paging query will not be valid.

@Configuration public class MysqlDataSourceConfig { @Autowired private PaginationInterceptor paginationInterceptor; @Primary @Bean(name = "mysqlDataSource") @ConfigurationProperties(prefix = "spring.datasource") public DataSource dataSource() { return DataSourceBuilder.create().build(); } @Primary @Bean(name = "mysqlSqlSessionFactory") public SqlSessionFactory sqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception { final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); sessionFactory.setDataSource(dataSource); // MybatisConfiguration = new MybatisConfiguration(); configuration.setJdbcTypeForNull(JdbcType.NULL); configuration.setMapUnderscoreToCamelCase(true); configuration.setCacheEnabled(false); sessionFactory.setConfiguration(configuration); / / set MyBatis paging plug-in, or paging query will not take effect sessionFactory. SetPlugins (paginationInterceptor); return sessionFactory.getObject(); }}Copy the code

Note that the configuration information of the datasource cannot be the same as that of the default datasource. Otherwise, an exception is reported:

ERROR [com.zaxxer.hikari.HikariConfig] - HikariPool-1 - jdbcUrl is required with driverClassName.
Copy the code

The database connection address information lacks jdbC-URL. Change the URL to JdbC-URL, and the database is started correctly.

The key is the data source’s paging plug-in and key:

  1. sessionFactory.setPlugins(paginationInterceptor);
  2. The data source key isjdbc-url

The revelation of

Question: Theoretically using SpringBoot default data source and custom data source, the configuration information should be the same, also to some articles in the demo of the two configuration information is the same.

So, what causes the difference in key between the two configurations? Guess there may be a SpringBoot version problem.

The answer: when DataSourceBuilder created the data source, SpringBoot2.0 used jdbc-url as the key, while version 1.5 used url. So it’s safe to configure both:

spring: dataSource: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/db? allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&&serverTimezone=UTC url: jdbc:mysql://localhost:3306/db? allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&&serverTimezone=UTC username: root password: 12345 type: com.alibaba.druid.pool.DruidDataSourceCopy the code