How do I configure HikariCP in my Spring Boot app in my application.properties files?

Question

I'm trying to set up HikariCP in my Spring Boot (1.2.0.M1) app so I can test using it in place of Tomcat DBCP. I'd like to configure the connection pool in my application.properties file like I was doing with Tomcat, but I can't figure out how I should be doing it. All examples I've found show either JavaConfig style, or using a separate HikariCP properties file. Can someone help me figure out the property names to configure it in application.properties? I'd like to also switch from using the driverClassName approach to the DataSourceClassName approach since it looks cleaner and is recommended. Is this also possible in my application.properties file(s)?

Here's what I had for Tomcat DBCP (just some basic config, not fully flushed out)

spring.datasource.validation-query=SELECT 1 spring.datasource.max-active=10 spring.datasource.max-idle=8 spring.datasource.min-idle=8 spring.datasource.initial-size=5 spring.datasource.test-on-borrow=true spring.datasource.test-on-return=true 

And I'm currently using driverClassName and jdbc url to set up the connection:

spring.datasource.url=jdbc:mysql://localhost:3306/myDb spring.datasource.driverClassName=com.mysql.jdbc.Driver 

Answer 1

@Configuration @ConfigurationProperties(prefix = "params.datasource") public class JpaConfig extends HikariConfig {      @Bean     public DataSource dataSource() throws SQLException {         return new HikariDataSource(this);     }  } 

application.yml

params:   datasource:     driverClassName: com.mysql.jdbc.Driver     jdbcUrl: jdbc:mysql://localhost:3306/myDb     username: login     password: password     maximumPoolSize: 5 

---

UPDATED! Since version Spring Boot 1.3.0 :

1. Just add HikariCP to dependencies
2. Configure application.yml

application.yml

spring:   datasource:     type: com.zaxxer.hikari.HikariDataSource     url: jdbc:h2:mem:TEST     driver-class-name: org.h2.Driver     username: username     password: password     hikari:       idle-timeout: 10000 

---

UPDATED! Since version Spring Boot 2.0.0 :

The default connection pool has changed from Tomcat to Hikari :)

Answer 2

I came across HikariCP and I was amazed by the benchmarks and I wanted to try it instead of my default choice C3P0 and to my surprise I struggled to get the configurations right probably because the configurations differ based on what combination of tech stack you are using.

I have setup Spring Boot project with JPA, Web, Security starters (Using Spring Initializer) to use PostgreSQL as a database with HikariCP as connection pooling.
I have used Gradle as build tool and I would like to share what worked for me for the following assumptions:

1. Spring Boot Starter JPA (Web & Security - optional)
2. Gradle build too
3. PostgreSQL running and setup with a database (i.e. schema, user, db)

You need the following build.gradle if you are using Gradle or equivalent pom.xml if you are using maven

buildscript {     ext {         springBootVersion = '1.5.8.RELEASE'     }     repositories {         mavenCentral()     }     dependencies {         classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")     } }  apply plugin: 'java' apply plugin: 'eclipse' apply plugin: 'org.springframework.boot' apply plugin: 'war'  group = 'com' version = '1.0' sourceCompatibility = 1.8  repositories {     mavenCentral() }  dependencies {     compile('org.springframework.boot:spring-boot-starter-aop')      // Exclude the tomcat-jdbc since it's used as default for connection pooling     // This can also be achieved by setting the spring.datasource.type to HikariCP      // datasource see application.properties below     compile('org.springframework.boot:spring-boot-starter-data-jpa') {         exclude group: 'org.apache.tomcat', module: 'tomcat-jdbc'     }     compile('org.springframework.boot:spring-boot-starter-security')     compile('org.springframework.boot:spring-boot-starter-web')     runtime('org.postgresql:postgresql')     testCompile('org.springframework.boot:spring-boot-starter-test')     testCompile('org.springframework.security:spring-security-test')      // Download HikariCP but, exclude hibernate-core to avoid version conflicts     compile('com.zaxxer:HikariCP:2.5.1') {         exclude group: 'org.hibernate', module: 'hibernate-core'     }      // Need this in order to get the HikariCPConnectionProvider     compile('org.hibernate:hibernate-hikaricp:5.2.11.Final') {         exclude group: 'com.zaxxer', module: 'HikariCP'         exclude group: 'org.hibernate', module: 'hibernate-core'     } } 

There are a bunch of excludes in the above build.gradle and that's because

1. First exclude, instructs gradle that exclude the jdbc-tomcat connection pool when downloading the spring-boot-starter-data-jpa dependencies. This can be achieved by setting up the spring.datasource.type=com.zaxxer.hikari.HikariDataSource also but, I don't want an extra dependency if I don't need it
2. Second exclude, instructs gradle to exclude hibernate-core when downloading com.zaxxer dependency and that's because hibernate-core is already downloaded by Spring Boot and we don't want to end up with different versions.
3. Third exclude, instructs gradle to exclude hibernate-core when downloading hibernate-hikaricp module which is needed in order to make HikariCP use org.hibernate.hikaricp.internal.HikariCPConnectionProvider as connection provider instead of deprecated com.zaxxer.hikari.hibernate.HikariConnectionProvider

Once I figured out the build.gradle and what to keep and what to not, I was ready to copy/paste a datasource configuration into my application.properties and expected everything to work with flying colors but, not really and I stumbled upon the following issues

• Spring boot failing to find out database details (i.e. url, driver) hence, not able to setup jpa and hibernate (because I didn't name the property key values right)
• HikariCP falling back to com.zaxxer.hikari.hibernate.HikariConnectionProvider
• After instructing Spring to use new connection-provider for when auto-configuring hibernate/jpa then HikariCP failed because it was looking for some key/value in the application.properties and was complaining about dataSource, dataSourceClassName, jdbcUrl. I had to debug into HikariConfig, HikariConfigurationUtil, HikariCPConnectionProvider and found out that HikariCP could not find the properties from application.properties because it was named differently.

Anyway, this is where I had to rely on trial and error and make sure that HikariCP is able to pick the properties (i.e. data source that's db details, as well as pooling properties) as well as Sping Boot behave as expected and I ended up with the following application.properties file.

server.contextPath=/ debug=true  # Spring data source needed for Spring boot to behave # Pre Spring Boot v2.0.0.M6 without below Spring Boot defaults to tomcat-jdbc connection pool included  # in spring-boot-starter-jdbc and as compiled dependency under spring-boot-starter-data-jpa spring.datasource.type=com.zaxxer.hikari.HikariDataSource spring.datasource.url=jdbc:postgresql://localhost:5432/somedb spring.datasource.username=dbuser spring.datasource.password=dbpassword  # Hikari will use the above plus the following to setup connection pooling spring.datasource.hikari.minimumIdle=5 spring.datasource.hikari.maximumPoolSize=20 spring.datasource.hikari.idleTimeout=30000 spring.datasource.hikari.poolName=SpringBootJPAHikariCP spring.datasource.hikari.maxLifetime=2000000 spring.datasource.hikari.connectionTimeout=30000  # Without below HikariCP uses deprecated com.zaxxer.hikari.hibernate.HikariConnectionProvider # Surprisingly enough below ConnectionProvider is in hibernate-hikaricp dependency and not hibernate-core # So you need to pull that dependency but, make sure to exclude it's transitive dependencies or you will end up  # with different versions of hibernate-core  spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider  # JPA specific configs spring.jpa.properties.hibernate.show_sql=true spring.jpa.properties.hibernate.format_sql=true spring.jpa.properties.hibernate.use_sql=true spring.jpa.properties.hibernate.id.new_generator_mappings=false spring.jpa.properties.hibernate.default_schema=dbschema spring.jpa.properties.hibernate.search.autoregister_listeners=false spring.jpa.properties.hibernate.bytecode.use_reflection_optimizer=false  # Enable logging to verify that HikariCP is used, the second entry is specific to HikariCP logging.level.org.hibernate.SQL=DEBUG logging.level.com.zaxxer.hikari.HikariConfig=DEBUG logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE  

As shown above the configurations are divided into categories based on following naming patterns

• spring.datasource.x (Spring auto-configure will pick these, so will HikariCP)
• spring.datasource.hikari.x (HikariCP picks these to setup the pool, make a note of the camelCase field names)
• spring.jpa.hibernate.connection.provider_class (Instructs Spring to use new HibernateConnectionProvider)
• spring.jpa.properties.hibernate.x (Used by Spring to auto-configure JPA, make a note of the field names with underscores)

It's hard to come across a tutorial or post or some resource that shows how the above properties file is used and how the properties should be named. Well, there you have it.

Throwing the above application.properties with build.gradle (or at least similar) into a Spring Boot JPA project version (1.5.8) should work like a charm and connect to your pre-configured database (i.e. in my case it's PostgreSQL that both HikariCP & Spring figure out from the spring.datasource.url on which database driver to use).

I did not see the need to create a DataSource bean and that's because Spring Boot is capable of doing everything for me just by looking into application.properties and that's neat.

The article in HikariCP's github wiki shows how to setup Spring Boot with JPA but, lacks explanation and details.

The above two file is also availble as a public gist https://gist.github.com/rhamedy/b3cb936061cc03acdfe21358b86a5bc6