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

Tags: , , , ,



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. Primary DataSource CP configuration Secondary DataSource CP configuration

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). jConsole inspecting the Tomcat JDBC JMX info about the Connection Pool

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

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


Source: stackoverflow