Spring Boot configuration for Tomcat’s pooling data source

In one of projects I was just working on in my professional career I’ve faced the problem of a Spring Boot configuration for a Tomcat’s database connection pool. The main issue was that after some time the pool was giving database connections that were not valid so we were trying different pool configuration parameters – but this is not relevant here. What is relevant is that all re-configuring approaches had no effect.

Is Spring Boot properly configuring the data source?

At some point I stopped believing that the Spring Boot is applying data source parameters from configuration file which was application.yml having following part related to the data source:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/some_db
    username: root
    password: root
    driver-class-name: "com.mysql.jdbc.Driver"
    tomcat:
      removeAbandoned: true
      removeAbandonedTimeout: 120
      logAbandoned: true
      maxActive: 50
      maxIdle: 5
      maxWait: 1000
      validationQuery: "select 1"
      testOnBorrow: true
      testOnConnect: true
      testWhileIdle: true

I’ve realized that I cannot easily verify what are actual data source parameters being applied by the Spring Boot. At last I’ve added a diagnostic REST endpoint showing what are actual parameters of the data source configured by Spring Boot. It was looking like this:

import org.apache.commons.beanutils.BeanUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

@RestController
public class InfoController {
    private static final Logger LOGGER = LoggerFactory.getLogger(InfoController.class);
    private static final List<String> TOMCAT_DB_CONN_POOL_PROPERTIES = Arrays.asList(
            "driverClassName", "maxActive", "maxAge", "maxIdle", "maxWait", "minEvictableIdleTimeMillis",
            "minIdle", "name", "removeAbandonedTimeout", "timeBetweenEvictionRunsMillis", "validationInterval",
            "validationQuery", "validationQueryTimeout", "removeAbandoned", "testOnBorrow", "testWhileIdle",
            "testOnConnect", "poolSweeperEnabled", "logAbandoned", "jmxEnabled", "url", "dataSourceJNDI",
            "initialSize", "suspectTimeout", "username", "useDisposableConnectionFacade");

    @Autowired
    private DataSource dataSource;

    @RequestMapping(path = "/data-source-info", method = RequestMethod.GET, produces = "application/json")
    public Object getInfo() {
        Map<String, Object> dataSourceDetails = new TreeMap<>();
        dataSourceDetails.put("data-source-class", dataSource.getClass().getName());
        addObjPropertiesUnderKey(dataSource, "data-source-config", dataSourceDetails);
        return dataSourceDetails;
    }

    static void addObjPropertiesUnderKey(Object obj, String key, Map<String, Object> map) {
        try {
            Map<String, Object> properties = new TreeMap<>();
            TOMCAT_DB_CONN_POOL_PROPERTIES.stream().forEach(property -> addPropertyIfNotNull(obj, property, properties));
            if (!properties.isEmpty()) {
                map.put(key, properties);
            }
        } catch (Exception e) {
            LOGGER.warn("Cannot fetch properties of object: {}.", obj, e);
        }
    }

    static void addPropertyIfNotNull(Object obj, String property, Map<String, Object> map) {
        try {
            String value = BeanUtils.getProperty(obj, property);
            if (value != null) {
                map.put(property, value);
            }
        } catch (Exception e) {
            LOGGER.warn("Cannot get property '{}' from  object of class {}. {}", property, obj.getClass(), e.getMessage());
        }
    }
}

The above solution uses dynamic property access with BeanUtils to not bind the application code to the actual data source implementation class (org.apache.tomcat.jdbc.pool.DataSource in case of Tomcat db-connection pool). And of course the set of property names is specific for this data source class.

The first look at the output produced by this REST controller confirmed my suspicion. Only first 4 parameters were properly applied (url, user, password and driver class). The remaining parameters (all with prefix spring.datasource.tomcat.) were ignored by the Spring Boot!

Spring Boot versions and the parameters naming

It turned out the data source parameters definition given in project’s application.yml file was conforming to the current version of the Spring Boot – which was 1.5.8 at the time of writing this article. But the Spring Boot version used in the project was much older: 1.3.8.

Please note the difference between the section “Connection to a production database” for Spring Boot 1.5.8 and the same section for Spring Boot 1.3.8!

For Spring Boot 1.3.8 we have:

And for Spring Boot 1.5.8 we have:

Now it got clear that parameters naming with “tomcat” was not valid for the Spring Boot version used in the project. The naming scheme was changed in Spring Boot somewhere between versions 1.3.x and 1.5.x! The correct form of this part of application.yml for Spring Boot 1.3.8 is:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/some_db
    username: root
    password: root
    driver-class-name: "com.mysql.jdbc.Driver"
    removeAbandoned: true
    removeAbandonedTimeout: 120
    logAbandoned: true
    maxActive: 50
    maxIdle: 5
    maxWait: 1000
    validationQuery: "select 1"
    testOnBorrow: true
    testOnConnect: true
    testWhileIdle: true

After this change the actual data source parameters dump provided by my diagnostic endpoint confirmed that now values from the configuration file are used.

Very tricky!

Conclusions

1. One should always refer to the documentation in version corresponding to the version of a framework being used.
2. It’s really hard to solve a problem without a diagnostic tool (like the InfoController class presented above) so don’t hesitate to invest time into preparing one. In a result it will save your time.