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

User · Answer

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

User · Answer

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         lt dependency gt           lt groupId gt org springframework boot lt  groupId gt           lt artifactId gt spring-boot-starter-jdbc lt  artifactId gt           lt exclusions gt               lt exclusion gt                   lt groupId gt org apache tomcat lt  groupId gt                   lt artifactId gt tomcat-jdbc lt  artifactId gt               lt  exclusion gt           lt  exclusions gt       lt  dependency gt               lt dependency gt           lt groupId gt com zaxxer lt  groupId gt           lt artifactId gt HikariCP lt  artifactId gt       lt  dependency gt

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to java

Examples related to spring

Examples related to jdbc

Examples related to spring-boot

Examples related to tomcat-jdbc