How to setup multiple connection pools when multiple datasources are used in Spring Boot?
Here's the approach I had to take in order to get separate pools for each datasource. The following is an implementation of the points that @user3007501 made above.
- Don't use the
DataSourceBuilder
, and instead create aorg.apache.tomcat.jdbc.pool.DataSource
. This will create both the pool, and configure the connections.If you need
Hikari
orDbcp2
replace the contents of the methodcreatePooledDataSource()
below, with theHikari
orDbcp2
config sections from the original Spring source DataSourceConfiguration.java. The displayed contents ofcreatePooledDataSource()
below were stolen from theTomcat.dataSource()
method in the linked file. - Add a
tomcat
configuration section under each of your yourdatasource
configurations in yourapplication.yml
- Ensure your each of your configuration beans is using the
config-name-here.datasource.tomcat
(note the.tomcat
) property specified in theapplication.yml
and not theconfig-name-here.datasource
without.tomcat
- Add beans to provide configurations of
DataSourceProperties
fore each of the datasources - use
@Qualifier("name of bean from previous step")
on your tomcat polling datasource
application.yml
# Primary Datasource
spring:
datasource:
username: your-username-for-ds-1
password: your-password-for-ds-1
driver-class-name: net.sourceforge.jtds.jdbc.Driver
tomcat:
validation-query: select 1
test-on-borrow: true
myotherdatasource:
datasource:
username: your-username-for-ds-2
password: your-password-for-ds-2
driver-class-name: net.sourceforge.jtds.jdbc.Driver
# HERE: make sure you have a tomcat config for your second datasource like below
tomcat:
validation-query: select 1
test-on-borrow: true
MyCustomDatasourceConfig.java <- your custom config file
The
createPooledDataSource()
was taken from DataSourceConfiguration.java in the Spring project source.
import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DatabaseDriver;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.beans.factory.annotation.Qualifier;
@Configuration
public class MyCustomDatasourceConfig {
@Bean(name = "My-First-Data")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.tomcat")
// *** NOTE the inclusion of the .tomcat above
public DataSource primaryDataSource(DataSourceProperties properties) {
return createPooledDataSource(properties);
}
@Bean()
@Primary
@ConfigurationProperties(prefix = "spring.datasource")
public DataSourceProperties dataSourcePropsPrimary() {
return new DataSourceProperties();
}
@Bean(name = "My-Second-Data-Source")
@ConfigurationProperties(prefix = "myotherdatasource.datasource.tomcat")
// *** NOTE the inclusion of the .tomcat above
public DataSource datasourceOtherConfig(@Qualifier("secondary_ds_prop") DataSourceProperties properties) {
return createPooledDataSource(properties);
}
@Bean(name = "secondary_ds_prop")
@ConfigurationProperties(prefix = "myotherdatasource.datasource")
public DataSourceProperties dataSourcePropsSecondary() {
return new DataSourceProperties();
}
private DataSource createPooledDataSource(DataSourceProperties properties) {
// Using fully qualified path to the tomcat datasource just to be explicit for the sake of this example
DataSource dataSource = (org.apache.tomcat.jdbc.pool.DataSource)
properties.initializeDataSourceBuilder()
.type(org.apache.tomcat.jdbc.pool.DataSource.class).build();
DatabaseDriver databaseDriver = DatabaseDriver.fromJdbcUrl(properties.determineUrl());
String validationQuery = databaseDriver.getValidationQuery();
if (validationQuery != null) {
dataSource.setTestOnBorrow(true);
dataSource.setValidationQuery(validationQuery);
}
return dataSource;
}
}
- Generally,
DataSource
interface is implemented by pooling libraries to be compatible with frameworks and JavaEE code as universal JDBC connection source which actually works with DB driver.
SpringBoot autoconfiguration has DataSource initializers for popular pooling libraries. Full list you can find in Spring sources.
That means all you need to leverage pooling in your project is to add pooling library likeHikari
as dependency and configurespring.datasource.*
params. Spring will create and configure single poolingDataSource
which can be autowired in your code. - Another story if you need to create more than one
DataSource
. SpringBoot autoconfiguration heavily use@ConditionalOnMissingBean
annotation to determine cases when default behaviour can be applied. Spring can't create two default datasources because it's ambiguous which one should be used.
You can find this inspring-boot-autoconfugire
module: Spring startsDataSource
initialisation logic only if there's no bean of this type in context.
To use several pools you have to define your ownBean
for each pooled DB connection. Spring will notice yourDataSource
and won't create pool internally. Here's an example. - You can find more details about
DataSource
autoconfiguration here