Spring-Boot: How do I set JDBC pool properties like maximum number of connections?

Spring-Boot is a pretty awesome tool, but the documentation is a bit sparse when it comes to more advanced configuration. How can I set properties like the maximum size for my database connection pool?

Spring-Boot supports tomcat-jdbc, HikariCP and Commons DBCP natively are they all configured the same way?

It turns out setting these configuration properties is pretty straight forward, but the official documentation is more general so it might be hard to find when searching specifically for connection pool configuration information.

To set the maximum pool size for tomcat-jdbc, set this property in your .properties or .yml file:

spring.datasource.maxActive=5

You can also use the following if you prefer:

spring.datasource.max-active=5

You can set any connection pool property you want this way. Here is a complete list of properties supported by tomcat-jdbc.

To understand how this works more generally you need to dig into the Spring-Boot code a bit.

Spring-Boot constructs the DataSource like this (see here, line 102):

@ConfigurationProperties(prefix = DataSourceAutoConfiguration.CONFIGURATION_PREFIX)
@Bean
public DataSource dataSource() {
	DataSourceBuilder factory = DataSourceBuilder
			.create(this.properties.getClassLoader())
			.driverClassName(this.properties.getDriverClassName())
			.url(this.properties.getUrl())
			.username(this.properties.getUsername())
			.password(this.properties.getPassword());
	return factory.build();
}

The DataSourceBuilder is responsible for figuring out which pooling library to use, by checking for each of a series of know classes on the classpath. It then constructs the DataSource and returns it to the dataSource() function.

At this point, magic kicks in using @ConfigurationProperties. This annotation tells Spring to look for properties with prefix CONFIGURATION_PREFIX (which is spring.datasource). For each property that starts with that prefix, Spring will try to call the setter on the DataSource with that property.

The Tomcat DataSource is an extension of DataSourceProxy, which has the method setMaxActive().

And that's how your spring.datasource.maxActive=5 gets applied correctly!

What about other connection pools

I haven't tried, but if you are using one of the other Spring-Boot supported connection pools (currently HikariCP or Commons DBCP) you should be able to set the properties the same way, but you'll need to look at the project documentation to know what is available.

At the current version of Spring-Boot (1.4.1.RELEASE) , each pooling datasource implementation has its own prefix for properties.

For instance, if you are using tomcat-jdbc:

spring.datasource.tomcat.max-wait=10000

You can find the explanation out here

spring.datasource.max-wait=10000

this have no effect anymore.

In spring boot 2.x you need to reference provider specific properties.

https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html#boot-features-connect-to-production-database

The default, hikari can be set with spring.datasource.hikari.maximum-pool-size.

Different connections pools have different configs.

For example Tomcat (default) expects:

spring.datasource.ourdb.url=...

and HikariCP will be happy with:

spring.datasource.ourdb.jdbc-url=...

We can satisfy both without boilerplate configuration:

spring.datasource.ourdb.jdbc-url=${spring.datasource.ourdb.url}

---

There is no property to define connection pool provider.

Take a look at source DataSourceBuilder.java

> If Tomcat, HikariCP or Commons DBCP are on the classpath one of them will be selected (in that order with Tomcat first).

... so, we can easily replace connection pool provider using this maven configuration (pom.xml):

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
        <exclusions>
            <exclusion>
                <groupId>org.apache.tomcat</groupId>
                <artifactId>tomcat-jdbc</artifactId>
            </exclusion>
        </exclusions>
    </dependency>		

    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>

Based on your application type/size/load/no. of users ..etc - u can keep following as your production properties

spring.datasource.tomcat.initial-size=50
spring.datasource.tomcat.max-wait=20000
spring.datasource.tomcat.max-active=300
spring.datasource.tomcat.max-idle=150
spring.datasource.tomcat.min-idle=8
spring.datasource.tomcat.default-auto-commit=true