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

Question

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?

Arch

I find better using a different DataSource provider like BoneCP or C3P0 – Preconceive

-Dspring.datasource.tomcat.initial-size=10 (default is 10) – Zarah

Answer 1

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.

Arch

There is explicit documentation on that one (as it happens): docs.spring.io/spring-boot/docs/current/reference/htmlsingle/…. Also you can start the app and look at /configprops (with Actuator). The problem for documentation is that all the DataSource implementations have slightly different properties (you are just binding to a Java bean). – Bueschel

Thanks for pointing this out @DaveSyer, its not very SEO friendly when searching for connection pool related keywords. I agree about the difference connection pools have different configs, thats why I added a section on that. I'll update the answer. – Arch

See other answers like Daniel's and wildloop's. As of later spring boot versions, many settings require you to set the provider-specific settings. e.g. spring.datasource.tomcat.max-active or spring.datasource.hikari.maximum-pool-size. – Assay

For my case only spring.datasource.maxActive=1 worked. This line spring.datasource.max-active=1 did not work for me. Spring Boot version 2.2.2.RELEASE – Acetum

Answer 2

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.

Bricebriceno

Answer 3

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.

Slink

Answer 4

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>

Nestle

Answer 5

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

Inclement

Adding spring.datasource.tomcat.max-active=5 spring.datasource.tomcat.max-idle=5 was enough for me, thanks! – Byssus

Answer 6

according:

https://docs.spring.io/spring-boot/docs/1.1.2.RELEASE/reference/html/common-application-properties.html

spring.datasource.max-active=100 # Advanced configuration...
spring.datasource.max-idle=8
spring.datasource.min-idle=8

Arpent