Mastering SpringBoot: Multi-DataSource, Druid Pool, and PageHelper Pagination

Preface

This article explains how to integrate SpringBoot with MyBatis , Druid , and PageHelper to achieve multi‑data‑source support and pagination.

Introducing and Using Druid

Druid is a high‑performance, extensible database connection pool developed by Alibaba, widely used in production environments for its monitoring capabilities.

Key features include a filter‑chain plugin system, efficient DruidDataSource, and SQL parsing.

Filter‑Chain plugin architecture

Manageable DruidDataSource SQLParser

Main functions:

Efficient, powerful, extensible connection pool

Database access performance monitoring

Database password encryption

SQL execution logging

JDBC extension

Add the Maven dependency:

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.8</version>
</dependency>

Configure Druid in application.properties (or application.yml) for two data sources (master and cluster). Example configuration:

## Default data source
master.datasource.url=jdbc:mysql://localhost:3306/springBoot?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
master.datasource.username=root
master.datasource.password=123456
master.datasource.driverClassName=com.mysql.jdbc.Driver

## Second data source
cluster.datasource.url=jdbc:mysql://localhost:3306/springBoot_test?useUnicode=true&characterEncoding=utf8
cluster.datasource.username=root
cluster.datasource.password=123456
cluster.datasource.driverClassName=com.mysql.jdbc.Driver

# Connection pool settings
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.filters=stat,wall,log4j
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

Create the Java configuration class for the default data source ( MasterDataSourceConfig.java) and annotate it with @Configuration, @MapperScan, and @Primary. The class defines beans for the data source, transaction manager, and SqlSessionFactory, injecting the properties defined above.

Similarly, create ClusterDataSourceConfig.java for the second data source (without @Primary).

Register Druid’s monitoring servlet and filter:

@Configuration
public class DruidConfiguration {
    @Bean
    public ServletRegistrationBean druidStatViewServle() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        bean.addInitParameter("allow", "127.0.0.1");
        bean.addInitParameter("deny", "127.0.0.2");
        bean.addInitParameter("loginUsername", "pancm");
        bean.addInitParameter("loginPassword", "123456");
        bean.addInitParameter("resetEnable", "false");
        return bean;
    }
    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean(new WebStatFilter());
        bean.addUrlPatterns("/*");
        bean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return bean;
    }
}

Access the monitoring UI at http://127.0.0.1:8084/druid/index.html using the configured credentials.

Multi-DataSource Configuration

Create two MySQL databases ( springBoot and springBoot_test) with identical table structures ( t_user and t_student).

-- springBoot database
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

-- springBoot_test database
CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

After configuring both data sources, you can insert data via the provided REST APIs:

POST http://localhost:8084/api/user
{ "name": "张三", "age": 25 }
POST http://localhost:8084/api/student
{ "name": "学生A", "age": 16 }

Querying the APIs returns the inserted records, confirming that the multi‑data‑source setup works correctly.

PageHelper Pagination Implementation

PageHelper is a MyBatis pagination plugin. Add the Maven dependency:

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.3</version>
</dependency>

Configure it in application.properties (or application.yml), or register it as a bean, or add it to mybatis.xml. Example property configuration:

pagehelper:
  helperDialect: mysql
  offsetAsPageNum: true
  rowBoundsWithCount: true
  reasonable: false

When using multiple data sources, add the pagination interceptor to the corresponding SqlSessionFactory (e.g., masterSqlSessionFactory).

@Bean(name = "masterSqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource) throws Exception {
    SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    sessionFactory.setDataSource(masterDataSource);
    sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MasterDataSourceConfig.MAPPER_LOCATION));
    // pagination plugin
    Interceptor interceptor = new PageInterceptor();
    Properties props = new Properties();
    props.setProperty("helperDialect", "mysql");
    props.setProperty("offsetAsPageNum", "true");
    props.setProperty("rowBoundsWithCount", "true");
    props.setProperty("reasonable", "false");
    interceptor.setProperties(props);
    sessionFactory.setPlugins(new Interceptor[]{interceptor});
    return sessionFactory.getObject();
}

Use pagination in code with:

Page<?> page = PageHelper.startPage(pageNum, pageSize);
List<T> list = mapper.findByListEntity(entity);
long total = page.getTotal();

Sample API calls demonstrate that only the requested page of data is returned, and the console logs show the generated count and limit queries.

Conclusion

The article covered the essential steps to integrate SpringBoot with MyBatis, configure Druid as a monitoring‑enabled connection pool, set up multiple data sources, and apply PageHelper for pagination. For more advanced scenarios, refer to the official documentation of Druid and PageHelper.