How to setup multiple connection pools when multiple datasources are used in Spring Boot?

Question

I have a Spring Boot application that connects to two separate databases. All works fine (I followed the steps in the docs and a tutorial), although in order to customize the Tomcat JDBC connection pool settings, I had to manually configure it (because by defining multiple data sources, the Boot auto-configuration is ignored, and Spring Boot does not read the tomcat-specific properties anymore from application.properties).

When I use a debugger during the configuration of the two DataSources, I see that both org.apache.tomcat.jdbc.pool.DataSource instances have the same connection pool in the DataSource.PoolProperties["name"] entry. See below screenshots in the debugger, each dataSource() method is configured in a separate configuration class. Notice that the same Connection Pool is defined.

However, from what I see using jConsole + tomcat JMX, there is only one connection pool, which has the primary database details configured (URL, credentials, see below).

Because of the multiple layers of abstraction inside Spring, it is difficult for me to debug this. I have the Eclipse Class Decompiler plugin, which I normally use to see the Spring logic, but in this case, the initialization code for the data sources happens when the beans are registered, not when they are actually used by Spring Boot to set the data sources up.

Bottom line, can you help me understand:

1. why there is only one connection pool
2. how can I use two connection pools, one for each data source
3. where in the Spring code to look at for more details of how this works

For the 2nd question, there is a somewhat related question, but with no answer. There is another question which is a false positive, and another one which is related to Spring, not Spring Boot, so please don't report this as dupe.

Answer 1

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.

1. Don't use the DataSourceBuilder, and instead create a org.apache.tomcat.jdbc.pool.DataSource. This will create both the pool, and configure the connections.

   If you need Hikari or Dbcp2 replace the contents of the method createPooledDataSource() below, with the Hikari or Dbcp2 config sections from the original Spring source DataSourceConfiguration.java. The displayed contents of createPooledDataSource() below were stolen from the Tomcat.dataSource() method in the linked file.

2. Add a tomcat configuration section under each of your your datasource configurations in your application.yml
3. Ensure your each of your configuration beans is using the config-name-here.datasource.tomcat (note the .tomcat) property specified in the application.yml and not the config-name-here.datasource without .tomcat
4. Add beans to provide configurations of DataSourceProperties fore each of the datasources
5. 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;
    }
}