How to set up datasource with Spring for HikariCP

Question

Hi I m trying to use HikariCP with Spring for connection pool  I m using jdbcTempLate and JdbcdaoSupport   This is my spring configuration file for datasource    lt bean id  dataSource  class  com zaxxer hikari HikariDataSource  gt       lt property name  dataSourceClassName  value  oracle jdbc driver OracleDriver   gt       lt property name  dataSource url  value  jdbc oracle thin  localhost 1521 XE   gt       lt property name  dataSource user  value  username   gt       lt property name  dataSource password  value  password   gt   lt  bean gt    But unfortunately the following error message is generating   Cannot resolve reference to bean  dataSource  while setting bean property  dataSource   nested exception is org springframework beans factory BeanCreationException  Error creating bean with name  dataSource  defined in ServletContext resource   WEB-INF dispatcher-servlet xml   Instantiation of bean failed  nested exception is org springframework beans BeanInstantiationException  Could not instantiate bean class  com zaxxer hikari HikariDataSource   No default constructor found  nested exception is java lang NoSuchMethodException  com zaxxer hikari HikariDataSource  lt init gt      Can anyone please tell me how to solve this issue

User · Answer

This last error is caused by the library SLF4J not being found   HikariCP has two dependencies  slf4j and javassist   BTW  HikariDataSource does have a default constructor and does not need HikariConfig  see this link   So that was never the problem

User · Answer

You can create a datasource bean in servlet context as    lt beans bean id  dataSource      class  com zaxxer hikari HikariDataSource  destroy-method  close  gt       lt beans property name  dataSourceClassName          value  com mysql jdbc jdbc2 optional MysqlDataSource    gt       lt beans property name  maximumPoolSize  value  5    gt       lt beans property name  maxLifetime  value  30000    gt       lt beans property name  idleTimeout  value  30000    gt       lt beans property name  dataSourceProperties  gt           lt beans props gt               lt beans prop key  url  gt jdbc mysql   localhost 3306 exampledb lt  beans prop gt               lt beans prop key  user  gt root lt  beans prop gt               lt beans prop key  password  gt  lt  beans prop gt               lt beans prop key  prepStmtCacheSize  gt 250 lt  beans prop gt               lt beans prop key  prepStmtCacheSqlLimit  gt 2048 lt  beans prop gt               lt beans prop key  cachePrepStmts  gt true lt  beans prop gt               lt beans prop key  useServerPrepStmts  gt true lt  beans prop gt           lt  beans props gt       lt  beans property gt   lt  beans bean gt

User · Answer

May this also can help using configuration file like java class way    Configuration  PropertySource  classpath application properties   public class DataSourceConfig        Autowired     JdbcConfigProperties jdbc         Bean name    hikariDataSource       public DataSource hikariDataSource             HikariConfig config   new HikariConfig            HikariDataSource dataSource           config setJdbcUrl jdbc getUrl             config setUsername jdbc getUser             config setPassword jdbc getPassword                optional  Property setting depends on database vendor         config addDataSourceProperty  cachePrepStmts    true            config addDataSourceProperty  prepStmtCacheSize    250            config addDataSourceProperty  prepStmtCacheSqlLimit    2048            dataSource   new HikariDataSource config            return dataSource            How to use it    Component public class Car implements Runnable       private static final Logger logger   LoggerFactory getLogger AptSommering class          Autowired      Qualifier  hikariDataSource       private DataSource hikariDataSource

User · Answer

Using XML configuration  your data source should look something like this        lt bean id  hikariConfig  class  com zaxxer hikari HikariConfig  gt           lt property name  dataSourceProperties   gt           lt props gt               lt prop key  dataSource url  gt jdbc oracle thin  localhost 1521 XE lt  prop gt               lt prop key  dataSource user  gt username lt  prop gt               lt prop key  dataSource password  gt password lt  prop gt           lt  props gt         lt  property gt           lt property name  dataSourceClassName                     value  oracle jdbc driver OracleDriver    gt         lt  bean gt          lt bean id  dataSource  class  com zaxxer hikari HikariDataSource  gt               lt constructor-arg ref  hikariConfig    gt         lt  bean gt      Or you could skip the HikariConfig bean altogether and use an approach like the one mentioned here

User · Answer

I found it in http   www baeldung com hikaricp and it works   Your pom xml   lt dependency gt               lt groupId gt com zaxxer lt  groupId gt               lt artifactId gt HikariCP lt  artifactId gt               lt version gt 2 6 3 lt  version gt           lt  dependency gt    Your data xml   lt bean id  hikariConfig  class  com zaxxer hikari HikariConfig  gt       lt property name  driverClassName  value    jdbc driverClassName    gt       lt property name  jdbcUrl  value    jdbc databaseurl    gt       lt property name  username  value    jdbc username    gt       lt property name  password  value    jdbc password    gt   lt  bean gt   lt bean id  dataSource  class  com zaxxer hikari HikariDataSource  destroy-method  close  gt       lt constructor-arg ref  hikariConfig    gt   lt  bean gt   lt bean id  jdbcTemplate  class  org springframework jdbc core JdbcTemplate        p dataSource-ref  dataSource    gt    Your jdbc properties  jdbc driverClassName org postgresql Driver jdbc dialect org hibernate dialect PostgreSQL94Dialect jdbc databaseurl jdbc postgresql   localhost 5432 dev db jdbc username dev jdbc password dev

User · Answer

you need to write this structure on your bean configuration  this is your datasource     lt bean id  hikariConfig  class  com zaxxer hikari HikariConfig  gt       lt property name  poolName  value  springHikariCP    gt       lt property name  connectionTestQuery  value  SELECT 1    gt       lt property name  dataSourceClassName  value    hibernate dataSourceClassName     gt       lt property name  maximumPoolSize  value    hibernate hikari maximumPoolSize     gt       lt property name  idleTimeout  value    hibernate hikari idleTimeout     gt        lt property name  dataSourceProperties  gt           lt props gt               lt prop key  url  gt   dataSource url  lt  prop gt               lt prop key  user  gt   dataSource username  lt  prop gt               lt prop key  password  gt   dataSource password  lt  prop gt           lt  props gt       lt  property gt   lt  bean gt    lt  -- HikariCP configuration -- gt   lt bean id  dataSource  class  com zaxxer hikari HikariDataSource  destroy-method  close  gt       lt constructor-arg ref  hikariConfig    gt   lt  bean gt    This is my example and it is working  You just need to put your properties on hibernate properties and set it before    lt bean class  org springframework beans factory config PropertyPlaceholderConfigurer  gt       lt property name  locations  gt           lt list gt               lt value gt classpath hibernate properties lt  value gt           lt  list gt       lt  property gt   lt  bean gt    Obs   the versions are      log4j  1 2 16      springframework  3 1 4 RELEASE      HikariCP  1 4 0  Properties file  hibernate properties    hibernate dataSourceClassName oracle jdbc pool OracleDataSource hibernate hikari maximumPoolSize 10 hibernate hikari idleTimeout 30000 dataSource url jdbc oracle thin  localhost 1521 xe dataSource username admin dataSource password

User · Answer

my test java config  for MySql    Bean destroyMethod    close   public DataSource dataSource        HikariConfig hikariConfig   new HikariConfig        hikariConfig setDriverClassName  com mysql jdbc Driver        hikariConfig setJdbcUrl  jdbc mysql   localhost 3306 spring-test         hikariConfig setUsername  root        hikariConfig setPassword  admin         hikariConfig setMaximumPoolSize 5       hikariConfig setConnectionTestQuery  SELECT 1        hikariConfig setPoolName  springHikariCP         hikariConfig addDataSourceProperty  dataSource cachePrepStmts    true        hikariConfig addDataSourceProperty  dataSource prepStmtCacheSize    250        hikariConfig addDataSourceProperty  dataSource prepStmtCacheSqlLimit    2048        hikariConfig addDataSourceProperty  dataSource useServerPrepStmts    true         HikariDataSource dataSource   new HikariDataSource hikariConfig        return dataSource

User · Answer

for DB2  please try below configuration    lt bean id  hikariConfig  class  com zaxxer hikari HikariConfig  gt       lt property name  poolName  value  springHikariCP    gt       lt property name  dataSourceClassName  value  com ibm db2 jcc DB2SimpleDataSource   gt        lt property name  maximumPoolSize  value    db maxTotal     gt       lt property name  dataSourceProperties  gt           lt props gt               lt prop key  driverType  gt 4 lt  prop gt               lt prop key  serverName  gt 192 168 xxx xxx lt  prop gt               lt prop key  databaseName  gt dbname lt  prop gt               lt prop key  portNumber  gt 50000 lt  prop gt               lt prop key  user  gt db2inst1 lt  prop gt               lt prop key  password  gt password lt  prop gt           lt  props gt       lt  property gt        lt property name  jdbcUrl  value    db url     gt       lt property name  username  value    db username     gt       lt property name  password  value    db password     gt   lt  bean gt   lt bean id  dataSource  class  com zaxxer hikari HikariDataSource  destroy-method  close  gt       lt constructor-arg ref  hikariConfig    gt   lt  bean gt

User · Answer

I have recently migrated from C3P0 to HikariCP in a Spring and Hibernate based project and it was not as easy as I had imagined and here I am sharing my findings    For Spring Boot see my answer here  I have the following setup    Spring 4 3 8   Hiberante 4 3 8  Gradle 2 x  PostgreSQL 9 5    Some of the below configs are similar to some of the answers above but  there are differences    Gradle stuff  In order to pull in the right jars  I needed to pull in the following jars    latest driver because  brettw  see https   github com pgjdbc pgjdbc pull 849 compile  org postgresql postgresql 42 2 0  compile  com zaxxer HikariCP 2 7 6           they are pulled in separately elsewhere     exclude group   org hibernate   module   hibernate-core        Recommended to use HikariCPConnectionProvider by Hibernate in 4 3 6      compile  org hibernate hibernate-hikaricp 4 3 8 Final               they are pulled in separately elsewhere  to avoid version conflicts         exclude group   org hibernate   module   hibernate-core          exclude group   com zaxxer   module   HikariCP        Needed for HikariCP logging if you use log4j compile  org slf4j slf4j-simple 1 7 25     compile  org slf4j slf4j-log4j12 1 7 25           log4j pulled in separately  exclude to avoid version conflict     exclude group   log4j   module   log4j      Spring Hibernate based configs  In order to get Spring  amp  Hibernate to make use of Hikari Connection pool  you need to define the HikariDataSource and feed it into sessionFactory bean as shown below     lt  -- HikariCP Database bean -- gt   lt bean id  dataSource  class  com zaxxer hikari HikariDataSource  destroy-method  close  gt       lt constructor-arg ref  hikariConfig    gt   lt  bean gt    lt  -- HikariConfig config that is fed to above dataSource -- gt   lt bean id  hikariConfig  class  com zaxxer hikari HikariConfig  gt           lt property name  poolName  value  SpringHikariPool    gt           lt property name  dataSourceClassName  value  org postgresql ds PGSimpleDataSource    gt           lt property name  maximumPoolSize  value  20    gt           lt property name  idleTimeout  value  30000    gt            lt property name  dataSourceProperties  gt               lt props gt                   lt prop key  serverName  gt localhost lt  prop gt                   lt prop key  portNumber  gt 5432 lt  prop gt                   lt prop key  databaseName  gt dbname lt  prop gt                   lt prop key  user  gt dbuser lt  prop gt                   lt prop key  password  gt dbpassword lt  prop gt               lt  props gt           lt  property gt   lt  bean gt    lt bean class  org springframework orm hibernate4 LocalSessionFactoryBean  id  sessionFactory  gt           lt  -- Your Hikari dataSource below -- gt           lt property name  dataSource  ref  dataSource   gt           lt  -- your other configs go here -- gt           lt property name  hibernateProperties  gt               lt props gt                   lt prop key  hibernate connection provider class  gt org hibernate hikaricp internal HikariCPConnectionProvider lt  prop gt                   lt  -- Remaining props goes here -- gt               lt  props gt           lt  property gt    lt  bean gt    Once the above are setup then  you need to add an entry to your log4j or logback and set the level to DEBUG to see Hikari Connection Pool start up    Log4j1 2   lt  -- Keep additivity false to avoid duplicate lines -- gt   lt logger additivity  false  name  com zaxxer hikari  gt       lt level value  debug   gt       lt  -- Your appenders goes here -- gt   lt  logger gt    Logback  Via application properties in Spring Boot   debug true logging level com zaxxer hikari HikariConfig DEBUG    Using logback xml   lt logger name  com zaxxer hikari  level  DEBUG  additivity  false  gt       lt appender-ref ref  STDOUT    gt   lt  logger gt    With the above you should be all good to go  Obviously you need to customize the HikariCP pool configs in order to get the performance that it promises

[spring] How to set up datasource with Spring for HikariCP?

Examples related to spring

Examples related to jdbc

Examples related to spring-jdbc

Examples related to jdbctemplate

Examples related to hikaricp