Druid Connection Pool Integration

Druid is a robust JDBC connection pool developed by Alibaba, offering prodcution-grade stability and built-in monitoring capabilities. It includes a filter-chain plugin system, efficient pool management, and SQL parsing utilities.

Add the dependency to your project:

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

Configure two separate data sources in application.properties:

# Primary Database
primary.jdbc.url=jdbc:mysql://localhost:3306/springBoot?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
primary.jdbc.user=root
primary.jdbc.pass=123456
primary.jdbc.driver=com.mysql.jdbc.Driver

# Secondary Database
secondary.jdbc.url=jdbc:mysql://localhost:3306/springBoot_test?useUnicode=true&characterEncoding=utf8
secondary.jdbc.user=root
secondary.jdbc.pass=123456
secondary.jdbc.driver=com.mysql.jdbc.Driver

# Pool Settings
druid.pool.initSize=5
druid.pool.minIdle=5
druid.pool.maxActive=20
druid.pool.maxWait=60000
druid.pool.timeBetweenEviction=60000
druid.pool.minEvictableIdle=300000
druid.pool.validationQuery=SELECT 1 FROM DUAL
druid.pool.testWhileIdle=true
druid.pool.testOnBorrow=false
druid.pool.testOnReturn=false
druid.pool.preparedStatementCache=true
druid.pool.maxPreparedStatementPerConn=20
druid.pool.filterList=stat,wall,log4j
druid.pool.connectionProps=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

Primary Data Source Configuration

@Configuration
@MapperScan(
    basePackages = PrimaryDbConfig.BASE_PKG,
    sqlSessionFactoryRef = "primarySessionFactory"
)
public class PrimaryDbConfig {

    static final String BASE_PKG = "com.example.mapper.primary";
    static final String MAPPER_PATH = "classpath:mapper/primary/*.xml";

    @Value("${primary.jdbc.url}")
    private String dbUrl;
    @Value("${primary.jdbc.user}")
    private String dbUser;
    @Value("${primary.jdbc.pass}")
    private String dbPass;
    @Value("${primary.jdbc.driver}")
    private String driverClass;

    @Value("${druid.pool.initSize}")
    private int initSize;
    @Value("${druid.pool.minIdle}")
    private int minIdle;
    @Value("${druid.pool.maxActive}")
    private int maxActive;
    @Value("${druid.pool.maxWait}")
    private int maxWait;
    @Value("${druid.pool.timeBetweenEviction}")
    private int timeBetweenEviction;
    @Value("${druid.pool.minEvictableIdle}")
    private int minEvictableIdle;
    @Value("${druid.pool.validationQuery}")
    private String validationQuery;
    @Value("${druid.pool.testWhileIdle}")
    private boolean testWhileIdle;
    @Value("${druid.pool.testOnBorrow}")
    private boolean testOnBorrow;
    @Value("${druid.pool.testOnReturn}")
    private boolean testOnReturn;
    @Value("${druid.pool.preparedStatementCache}")
    private boolean prepStmtCache;
    @Value("${druid.pool.maxPreparedStatementPerConn}")
    private int maxPrepStmt;
    @Value("${druid.pool.filterList}")
    private String filters;
    @Value("${druid.pool.connectionProps}")
    private String connProps;

    @Bean(name = "primaryDataSource")
    @Primary
    public DataSource primaryDataSource() {
        DruidDataSource ds = new DruidDataSource();
        ds.setUrl(dbUrl);
        ds.setUsername(dbUser);
        ds.setPassword(dbPass);
        ds.setDriverClassName(driverClass);
        ds.setInitialSize(initSize);
        ds.setMinIdle(minIdle);
        ds.setMaxActive(maxActive);
        ds.setMaxWait(maxWait);
        ds.setTimeBetweenEvictionRunsMillis(timeBetweenEviction);
        ds.setMinEvictableIdleTimeMillis(minEvictableIdle);
        ds.setValidationQuery(validationQuery);
        ds.setTestWhileIdle(testWhileIdle);
        ds.setTestOnBorrow(testOnBorrow);
        ds.setTestOnReturn(testOnReturn);
        ds.setPoolPreparedStatements(prepStmtCache);
        ds.setMaxPoolPreparedStatementPerConnectionSize(maxPrepStmt);
        try {
            ds.setFilters(filters);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        ds.setConnectionProperties(connProps);
        return ds;
    }

    @Bean(name = "primaryTxManager")
    @Primary
    public PlatformTransactionManager primaryTxManager() {
        return new DataSourceTransactionManager(primaryDataSource());
    }

    @Bean(name = "primarySessionFactory")
    @Primary
    public SqlSessionFactory primarySessionFactory(
            @Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations(
            new PathMatchingResourcePatternResolver().getResources(MAPPER_PATH)
        );
        return factoryBean.getObject();
    }
}

Secondary Data Source Configuration

@Configuration
@MapperScan(
    basePackages = SecondaryDbConfig.BASE_PKG,
    sqlSessionFactoryRef = "secondarySessionFactory"
)
public class SecondaryDbConfig {

    static final String BASE_PKG = "com.example.mapper.secondary";
    static final String MAPPER_PATH = "classpath:mapper/secondary/*.xml";

    @Value("${secondary.jdbc.url}")
    private String dbUrl;
    @Value("${secondary.jdbc.user}")
    private String dbUser;
    @Value("${secondary.jdbc.pass}")
    private String dbPass;
    @Value("${secondary.jdbc.driver}")
    private String driverClass;

    @Bean(name = "secondaryDataSource")
    public DataSource secondaryDataSource() {
        DruidDataSource ds = new DruidDataSource();
        ds.setUrl(dbUrl);
        ds.setUsername(dbUser);
        ds.setPassword(dbPass);
        ds.setDriverClassName(driverClass);
        return ds;
    }

    @Bean(name = "secondaryTxManager")
    public PlatformTransactionManager secondaryTxManager() {
        return new DataSourceTransactionManager(secondaryDataSource());
    }

    @Bean(name = "secondarySessionFactory")
    public SqlSessionFactory secondarySessionFactory(
            @Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations(
            new PathMatchingResourcePatternResolver().getResources(MAPPER_PATH)
        );
        return factoryBean.getObject();
    }
}

Druid Monitoring Setup

Enable the web monitoring interface by registering the StatViewServlet and WebStatFilter:

@Configuration
public class DruidMonitorConfig {

    @Bean
    public ServletRegistrationBean<StatViewServlet> statView() {
        ServletRegistrationBean<StatViewServlet> bean =
            new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        bean.addInitParameter("allow", "127.0.0.1");
        bean.addInitParameter("deny", "192.168.1.100");
        bean.addInitParameter("loginUsername", "admin");
        bean.addInitParameter("loginPassword", "admin123");
        bean.addInitParameter("resetEnable", "false");
        return bean;
    }

    @Bean
    public FilterRegistrationBean<WebStatFilter> statFilter() {
        FilterRegistrationBean<WebStatFilter> bean =
            new FilterRegistrationBean<>(new WebStatFilter());
        bean.addUrlPatterns("/*");
        bean.addInitParameter("exclusions", "*.js,*.css,*.png,*.jpg,/druid/*");
        return bean;
    }
}

Access the dashboard at http://localhost:8080/druid.

Database Schema

Primary DB (springBoot):

CREATE TABLE user_record (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(50),
  user_age INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Secondary DB (springBoot_test):

CREATE TABLE student_record (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(50),
  student_age INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Integrating PageHelper for Pagination

Add the Spring Boot starter for PageHelper:

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

To configure it for multiple data sources, inject the PageInterceptor into the SqlSessionFactory bean:

@Bean(name = "primarySessionFactory")
@Primary
public SqlSessionFactory primarySessionFactory(
        @Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
    factoryBean.setDataSource(dataSource);
    factoryBean.setMapperLocations(
        new PathMatchingResourcePatternResolver().getResources(MAPPER_PATH)
    );

    PageInterceptor 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);

    factoryBean.setPlugins(new Interceptor[]{interceptor});
    return factoryBean.getObject();
}

Usage Example

public List<User> fetchUsers(int pageNum, int pageSize) {
    Page<Object> page = PageHelper.startPage(pageNum, pageSize);
    List<User> users = userMapper.selectAll();
    log.info("Total records: {}, Returned: {}", page.getTotal(), users.size());
    return users;
}

When querying the API, the plugin automatically appends LIMIT and executes a count query in the background.