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

User · Answer

I was facing issues and the problem was a whitespace at the end of spring datasource type   com zaxxer hikari HikariDataSource

User · Answer

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     Spring Boot Starter JPA  Web  amp  Security - optional   Gradle build too  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    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  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   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  amp  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

User · Answer

The code below can be used for a static datasource initialization   public class MyDataSource       private static final String DB USERNAME  spring datasource username       private static final String DB PASSWORD  spring datasource password       private static final String DB URL   spring datasource url       private static final String DB DRIVER CLASS  spring datasource driver-class-name        private static Properties properties   null      private static HikariDataSource dataSource       static           try               properties   new Properties                properties load new FileInputStream  src main resources application properties                  dataSource   new HikariDataSource                dataSource setDriverClassName properties getProperty DB DRIVER CLASS                 dataSource setJdbcUrl properties getProperty DB URL                dataSource setUsername properties getProperty DB USERNAME                dataSource setPassword properties getProperty DB PASSWORD                 dataSource setMinimumIdle 100               dataSource setMaximumPoolSize 2000               dataSource setAutoCommit false               dataSource setLoginTimeout 3              catch  IOException   SQLException e                  Throwable  e  printStackTrace                         public static DataSource getDataSource            return dataSource             public static Connection getConnection   throws SQLException          return getDataSource   getConnection

User · Answer

This will help anyone who wants to configure hikaricp for their application with spring auto configuration  For my project  im using spring boot 2 with hikaricp as the JDBC connection pool and mysql as the database  One thing I didn t see in other answers was the data-source-properties which can be used to set various properties that are not available at the spring datasource hikari   path  This is equivalent to using the HikariConfig class  To configure the datasource and hikaricp connection pool for mysql specific properties I used the spring auto configure annotation and the following properties in the application yml file   Place  EnableAutoConfiguration on one of your configuration bean files   application yml file can look like this   spring    datasource      url   jdbc mysql   127 0 0 1 3306 DATABASE autoReconnect true amp useSSL false      username  user name     password  password     hikari        maximum-pool-size  20       data-source-properties          cachePrepStmts  true         prepStmtCacheSize  250         prepStmtCacheSqlLimit  2048         useServerPrepStmts  true         useLocalSessionState  true         rewriteBatchedStatements  true         cacheResultSetMetadata  true         cacheServerConfiguration  true         elideSetAutoCommits  true         maintainTimeStats  false

User · Answer

According to the documentation it is changed   https   docs spring io spring-boot docs current reference html boot-features-sql html  Example    spring      datasource          url   jdbc mysql   localhost db useSSL false          username  root         password  pass         driver  com mysql jdbc Driver         hikari              minIdle  10             idle-timeout  10000             maximumPoolSize  30   These are the following configuration changes we can do on hikari  please add update according to your need   autoCommit connectionTimeout idleTimeout maxLifetime connectionTestQuery connectionInitSql validationTimeout maximumPoolSize poolName allowPoolSuspension readOnly transactionIsolation leakDetectionThreshold

User · Answer

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     Just add HikariCP to dependencies 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

User · Answer

you can t use dataSourceClassName approach in application properties configurations as said by  Andy Wilkinson  if you want to have dataSourceClassName anyway you can use Java Config as    Configuration  ComponentScan class DataSourceConfig      Value    spring datasource username    private String user    Value    spring datasource password    private String password    Value    spring datasource url    private String dataSourceUrl    Value    spring datasource dataSourceClassName    private String dataSourceClassName    Value    spring datasource poolName    private String poolName    Value    spring datasource connectionTimeout    private int connectionTimeout    Value    spring datasource maxLifetime    private int maxLifetime    Value    spring datasource maximumPoolSize    private int maximumPoolSize    Value    spring datasource minimumIdle    private int minimumIdle    Value    spring datasource idleTimeout    private int idleTimeout    Bean public DataSource primaryDataSource         Properties dsProps   new Properties        dsProps put  url   dataSourceUrl       dsProps put  user   user       dsProps put  password   password       dsProps put  prepStmtCacheSize  250       dsProps put  prepStmtCacheSqlLimit  2048       dsProps put  cachePrepStmts  Boolean TRUE       dsProps put  useServerPrepStmts  Boolean TRUE        Properties configProps   new Properties           configProps put  dataSourceClassName   dataSourceClassName          configProps put  poolName  poolName          configProps put  maximumPoolSize  maximumPoolSize          configProps put  minimumIdle  minimumIdle          configProps put  minimumIdle  minimumIdle          configProps put  connectionTimeout   connectionTimeout          configProps put  idleTimeout   idleTimeout          configProps put  dataSourceProperties   dsProps       HikariConfig hc   new HikariConfig configProps      HikariDataSource ds   new HikariDataSource hc      return ds              reason you cannot use dataSourceClassName because it will throw and exception  Caused by  java lang IllegalStateException  both driverClassName and dataSourceClassName are specified  one or the other should be used    which mean spring boot infers from spring datasource url property the Driver and at the same time setting the dataSourceClassName creates this exception  To make it right your application properties should look something like this for HikariCP datasource     hikariCP    spring jpa databasePlatform org hibernate dialect MySQLDialect   spring datasource url jdbc mysql   localhost 3306 exampledb   spring datasource username root   spring datasource password    spring datasource poolName SpringBootHikariCP   spring datasource maximumPoolSize 5   spring datasource minimumIdle 3   spring datasource maxLifetime 2000000   spring datasource connectionTimeout 30000   spring datasource idleTimeout 30000   spring datasource pool-prepared-statements true   spring datasource max-open-prepared-statements 250   Note  Please check if there is any tomcat-jdbc jar or commons-dbcp jar in your classpath added most of the times by transitive dependency  If these are present in classpath Spring Boot will configure the Datasource using default connection pool which is tomcat  HikariCP will only be used to create the Datasource if there is no other provider in classpath  there is a fallback sequence from tomcat -  to HikariCP -  to Commons DBCP

User · Answer

My SetUp   Spring Boot v1 5 10 Hikari v 3 2 x  for evaluation   To really understand the configuration of Hikari Data Source  I recommend to disable Spring Boot s Auto-Configuration for Data Source   Add following to application properties -     spring autoconfigure exclude org springframework boot autoconfigure jdbc DataSourceAutoConfiguration   This will disable Spring Boot s capability to configure the DataSource on its own   Now is the chance for you to define your own Custom Configuration to create HikariDataSource bean and populate it with the desired properties      NOTE     public class HikariDataSource extends HikariConfig   You need to    populate HikariConfig Object using desired Hikari Properties initialize HikariDataSource object with HikariConfig object passed as an argument to constructor       I believe in defining my own Custom Configuration class      Configuration   to create the data source on my own and populate   it with the data source properties defined in a separate file  than   traditional  application properties    In this manner I can define   my own sessionFactory Bean using Hibernate recommended      LocalSessionFactoryBean  class and populate it with your Hikari Data Source   and other Hiberante-JPA based properties    Summary of Spring Boot based Hikari DataSource Properties -     spring datasource hikari allow-pool-suspension true   spring datasource hikari auto-commit false   spring datasource hikari catalog    spring datasource hikari connection-init-sql    spring datasource hikari connection-test-query    spring datasource hikari connection-timeout 100   spring datasource hikari data-source-class-name    spring datasource hikari data-source-j-n-d-i    spring datasource hikari driver-class-name     spring datasource hikari idle-timeout 50   spring datasource hikari initialization-fail-fast true   spring datasource hikari isolate-internal-queries true   spring datasource hikari jdbc-url    spring datasource hikari leak-detection-threshold    spring datasource hikari login-timeout 60   spring datasource hikari max-lifetime    spring datasource hikari maximum-pool-size 500   spring datasource hikari minimum-idle 30   spring datasource hikari password     spring datasource hikari pool-name    spring datasource hikari read-only true   spring datasource hikari register-mbeans true   spring datasource hikari transaction-isolation    spring datasource hikari username    spring datasource hikari validation-timeout

User · Answer

With the later spring-boot releases switching to Hikari can be done entirely in configuration  I m using 1 5 6 RELEASE and this approach works   build gradle   compile  com zaxxer HikariCP 2 7 3    application YAML  spring    datasource      type  com zaxxer hikari HikariDataSource     hikari        idleTimeout  60000       minimumIdle  2       maximumPoolSize  20       connectionTimeout  30000       poolName  MyPoolName       connectionTestQuery  SELECT 1   Change connectionTestQuery to suit your underlying DB  That s it  no code required

User · Answer

Here is the good news  HikariCP is the default connection pool now with Spring Boot 2 0 0   Spring Boot 2 0 0 Release Notes     The default database pooling technology in Spring Boot 2 0 has been switched from Tomcat Pool to HikariCP  We   ve found that Hakari offers superior performance  and many of our users prefer it over Tomcat Pool

User · Answer

So it turns out that almost all the default settings for HikariCP work for me except the number of DB connections  I set that property in my application properties    spring datasource maximumPoolSize 20   And Andy Wilkinson is correct as far as I can tell in that you can t use the dataSourceClassName configuration approach for HikariCP with Spring Boot

User · Answer

I m using Spring Boot 2 0 4 RELEASE  Hikari is default connection pool and  hikari is no longer necessary   application properties  spring datasource driverClassName com mysql jdbc Driver spring datasource jdbcUrl jdbc mysql   localhost 3306 myDB    spring datasource username xxx spring datasource password xxx spring datasource poolname myPool   application yml  spring      datasource          driverClassName  com mysql jdbc Driver         jdbcUrl  jdbc mysql   localhost 3306 myDB            username  xxx         password  xxx         poolName  myPool   And configuration does not need to extend HikariConfig  and DataSourceBuilder can be used as it was before    Configuration public class DataSourceConfiguration         Bean name  myDataSource        ConfigurationProperties  spring datasource       public DataSource myDataSource             return DataSourceBuilder create   build

User · Answer

This works for my boot application in case it helps  This class tells you what properties the config object is looking for    https   github com brettwooldridge HikariCP blob 2 3 x hikaricp-common src main java com zaxxer hikari AbstractHikariConfig java  I think multiple datasources could be supporting by adding datasource whatever to the property keys in the source config file  Cheers    Configuration class DataSourceConfig        Value    spring datasource username       private String user       Value    spring datasource password       private String password       Value    spring datasource url       private String dataSourceUrl       Value    spring datasource dataSourceClassName       private String dataSourceClassName       Value    spring datasource connectionTimeout       private int connectionTimeout       Value    spring datasource maxLifetime       private int maxLifetime       Bean    public DataSource primaryDataSource           Properties dsProps    url  dataSourceUrl  user  user  password  password        Properties configProps                 connectionTestQuery   select 1 from dual               connectionTimeout  connectionTimeout              dataSourceClassName  dataSourceClassName              dataSourceProperties  dsProps              maxLifetime  maxLifetime                   A default max pool size of 10 seems reasonable for now  so no need to configure for now        HikariConfig hc   new HikariConfig configProps        HikariDataSource ds   new HikariDataSource hc        ds

User · Answer

You could simply make use of application yml application properties only  There is no need to explicitly create any DataSource Bean  You need to exclude tomcat-jdbc as mentioned by ydemartino   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    As you won t create DataSource bean  you have to explicitly specify using Hikari through spring datasource type with value com zaxxer hikari HikariDataSource in application yml   application properties  spring      datasource          hikari              connection-test-query  SELECT 1 FROM DUAL             minimum-idle  1             maximum-pool-size  5             pool-name  yourPoolName             auto-commit  false         driver-class-name  com mysql jdbc Driver         url  jdbc mysql   localhost 3306 myDb         username  login         password  password         type  com zaxxer hikari HikariDataSource   In your application yml   application properties  you could configure Hikari specific parameters such as pool size etc in spring datasource hikari

User · Answer

You don t need redundant code for putting property values to variables  You can set properties with a properties file directly   Put hikari properties file in the classpath   driverClassName com mysql jdbc Driver jdbcUrl jdbc mysql   localhost 3306 myDb connectionTestQuery SELECT 1 maximumPoolSize 20 username     password       And make a datasource bean like this    Bean destroyMethod    close   public DataSource dataSource   throws SQLException       HikariConfig config   new HikariConfig   hikari properties        HikariDataSource dataSource   new HikariDataSource config        return dataSource

User · Answer

Now with HikcariCp as default connection pooling with new version of spring boot It can be directly done as shown below   Configuration public class PurchaseOrderDbConfig             Bean      ConfigurationProperties prefix    quot com sysco purchaseorder datasoure quot       public DataSource dataSource             return DataSourceBuilder create   build              application yml com    sysco      purchaseorder        datasoure          driverClassName  com mysql jdbc Driver         jdbcUrl  jdbc mysql   localhost 3306 purchaseorder         username  root         password  root123         idleTimeout  600000  If you will print the value of idle timeout value ApplicationContext context SpringApplication run ApiBluePrint class  args      HikariDataSource dataSource  HikariDataSource  context getBean DataSource class      System out println dataSource getIdleTimeout      you will get value as 600000 where as default value is 300000 if you dont define any custom value

User · Answer

You can use the dataSourceClassName approach  here is an example with MySQL   Tested with spring boot 1 3 and 1 4   First you need to exclude tomcat-jdbc from the classpath as it will be picked in favor of hikaricp   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    application properties  spring datasource dataSourceClassName com mysql jdbc jdbc2 optional MysqlDataSource spring datasource dataSourceProperties serverName localhost spring datasource dataSourceProperties portNumber 3311 spring datasource dataSourceProperties databaseName mydb spring datasource username root spring datasource password root   Then just add   Bean  ConfigurationProperties prefix    spring datasource   public DataSource dataSource         return DataSourceBuilder create   build        I created a test project here  https   github com ydemartino spring-boot-hikaricp

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

Examples related to java

Examples related to spring

Examples related to spring-boot

Examples related to hikaricp