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;
    }
}

Answer 2

1. 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 like Hikari as dependency and configure spring.datasource.* params. Spring will create and configure single pooling DataSource which can be autowired in your code.
2. 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 in spring-boot-autoconfugire module: Spring starts DataSource initialisation logic only if there's no bean of this type in context.
   To use several pools you have to define your own Bean for each pooled DB connection. Spring will notice your DataSource and won't create pool internally. Here's an example.
3. You can find more details about DataSource autoconfiguration here

Answer 3

I'm answering with what I did back then. If you find a better solution or Spring will allow multiple Connection Pools, please post an answer and I'll choose yours instead.

Because Spring will configure, given the code I posted in the question, only one connection pool (setting the validationQuery and validationInterval on the tomcat CP), I added a scheduled method to keep alive my second data source.

@Scheduled(fixedRate=INTERVAL_IN_MS)
public void scheduledTestDatabaseConnection() {
    try {
        testDatabaseConnection();
        LOGGER.trace("Tested EJBCA DB connection with success");
    }
    catch (Exception e) {
        LOGGER.error("Got an error when refreshing the EJBCA DB connection '{}'", e.getMessage());
    }
}

In the above example, testDatabaseConnection() calls a method on the Spring Data Repository

@Query("SELECT 1 FROM MyTable")
public int testConnection();

Answer 4

I have used the external tomcat datasource and configured it.

1) created 3 datasource in /conf/server.xml

    <Resource auth="Container" driverClassName="oracle.jdbc.OracleDriver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxTotal="20" maxIdle="10" maxWaitMillis="-1" name="jdbc/firstDS" password="xxxxx" type="javax.sql.DataSource" url="<url1>" username="user1"/>

    <Resource auth="Container" driverClassName="oracle.jdbc.OracleDriver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxTotal="20" maxIdle="10" maxWaitMillis="-1" name="jdbc/secondDS" password="xxxxx" type="javax.sql.DataSource" url="<url2>" username="user2"/>

    <Resource auth="Container" driverClassName="oracle.jdbc.OracleDriver" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxTotal="20" maxIdle="10" maxWaitMillis="-1" name="jdbc/thirdDS" password="xxxxx" type="javax.sql.DataSource" url="<url3>" username="user3"/>

2) Declare the same datasource in /conf/context.xml

    <ResourceLink auth="Container" name="jdbc/firstDS" global="jdbc/firstDS" type="javax.sql.DataSource" />
    <ResourceLink auth="Container" name="jdbc/secondDS" global="jdbc/secondDS" type="javax.sql.DataSource" />
    <ResourceLink auth="Container" name="jdbc/thirdDS" global="jdbc/thirdDS" type="javax.sql.DataSource" />

3) Define the datasource jndi-name in the springboot properties file

spring.datasource.jndi-name=java:comp/env/jdbc/firstDS
second.datasource.jndi-name=java:comp/env/jdbc/secondDS
third.datasource.jndi-name=java:comp/env/jdbc/thirdDS

4) Define the spring boot DB configuration for all the 3 database and make sure you declare atleast 1 datasource as primary. firstDBConfig.java,secondDBConfig.java,thirdDBConfig.java - Just change the jndi-name property and create 3 config class

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactory",
    transactionManagerRef = "transactionManager",   basePackages = {"com.first.application.repo"})
public class FirstDbConfig {

    @Autowired
    private Environment env;

  @Primary
  @Bean(name = "dataSource")
  public DataSource dataSource() throws NamingException {
      return (DataSource) new JndiTemplate().lookup(env.getProperty("spring.datasource.jndi-name"));
  }

  @Primary
  @Bean(name = "entityManagerFactory")
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(
      EntityManagerFactoryBuilder builder, @Qualifier("dataSource") DataSource dataSource) {
    return builder.dataSource(dataSource).packages("com.first.application.domain").persistenceUnit("eamPU")
        .build();
  }


  @Primary
  @Bean(name = "transactionManager")
  public PlatformTransactionManager transactionManager(
      @Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }

5) Write the DAO and entities under the defined base package.All the 3 Datasource class should be placed on separate package hierarchy.

Answer 5

All the above solutions appears a bit complex. With SpringBoot 2.0 and above we have hikariCP as the default connection management library.

Just define your data sources in your application.yml as mentioned below,

datasource-read:
    hikari:
      jdbc-url: someUrl
      username: someUser
      password: somePwd
      maximumPoolSize: 3
      
datasource-write:
    hikari:
      jdbc-url: someUrl
      username: someUser
      password: somePwd
      maximumPoolSize: 3
      

Define your bean configs like this,

@Bean(name = "readDataSource")
  @ConfigurationProperties(prefix = "spring.datasource-read.hikari")
  public DataSource readDataSource() {
    return DataSourceBuilder.create().build();
  }

  @Primary
  @Bean(name = "readJdbcTemplate")
  public NamedParameterJdbcTemplate readJdbcTemplate(
    @Qualifier("readDataSource") DataSource readDataSource) {
    return new NamedParameterJdbcTemplate(readDataSource);
  }