Hikaricp: Spring Boot JPA Hibernate and HikariCP

[wesoos]

Hello,

I'm trying to use HikariCP in a spring boot app with hibernate. I only want to configure things in my application.properties file. I am getting the following error:

cannot use driverClassName and dataSourceClassName together.

I have the following in my application.properties file. Any ideas?

spring.datasource.url=jdbc:mysql://localhost:3306/database
spring.datasource.username=root
spring.datasource.password=Password1
spring.datasource.dataSourceClassName=com.mysql.jdbc.jdbc2.optional.MysqlDataSource

spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider
spring.jpa.hibernate.hikari.minimumIdle=5
spring.jpa.hibernate.hikari.maximumPoolSize=10
spring.jpa.hibernate.hikari.idleTimeout=30000

spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.EJB3NamingStrategy
spring.jpa.properties.hibernate.show_sql=false
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.properties.hibernate.cache.use_query_cache=true
spring.jpa.properties.hibernate.cache.use_second_level_cache=true
spring.jpa.properties.hibernate.cache.default_cache_concurrency_strategy=READ_WRITE
spring.jpa.properties.hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

[shakuzen]

From the HikariCP README's Popular DataSource Class Names section:

Note: Spring Boot auto-configuration users, you need to use jdbcUrl-based configuration.

That is, the dataSourceClassName will be inferred from the spring.datasource.url and therefore should not be specified when using HikariCP with Spring Boot's auto-configuration.

[wesoos]

Thanks for the reply, however I am getting the following error:

Cannot determine embedded database driver class for database type NONE. If you want an embedded database please put a supported one on the classpath

This is my application.properties

spring.datasource.username=root
spring.datasource.password=Password1
spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider
spring.jpa.hibernate.hikari.minimumIdle=5
spring.jpa.hibernate.hikari.maximumPoolSize=10
spring.jpa.hibernate.hikari.idleTimeout=30000
spring.jpa.hibernate.hikari.jdbcUrl = jdbc:mysql://localhost:3306/db

spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.EJB3NamingStrategy
spring.jpa.properties.hibernate.show_sql=false
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.properties.hibernate.cache.use_query_cache=true
spring.jpa.properties.hibernate.cache.use_second_level_cache=true
spring.jpa.properties.hibernate.cache.default_cache_concurrency_strategy=READ_WRITE
spring.jpa.properties.hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

[brettwooldridge]

@wesoos Does this work?

spring.datasource.url=jdbc:mysql://localhost:3306/database
spring.datasource.username=root
spring.datasource.password=Password1

spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider
spring.jpa.hibernate.hikari.minimumIdle=5
spring.jpa.hibernate.hikari.maximumPoolSize=10
spring.jpa.hibernate.hikari.idleTimeout=30000
...

[wesoos]

@brettwooldridge

I thought I replied to your question before. With that the app starts up fine, no errors, however it only adds one connection to the MySQL database. The connection count only adds one connection, so I’m not sure if it’s actually using Hikari. Actually I don't believe it is, since if I try to use my application after a day of inactivity, it's throwing all kinds of exceptions in regards to the connection being closed.

The only things I see in the startup logs, in regards to Hikari, are the following:

com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Started.
o.s.j.e.a.AnnotationMBeanExporter : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource]

[brettwooldridge]

@wesoos Enable debug logging for the com.zaxxer.hikari package (in slf4j or whatever your logging framework is), you should see more information, such as logs when connections are added or removed, or errors initializing the pool.

[jerori]

Use

spring.datasource.type= com.zaxxer.hikari.HikariDataSource

and let us know..

[wesoos]

Guys,

I'm getting the following now. First below is my configuration, followed by the log output. As you can see I'm setting my minimum pool to 20, but it's only creating 10 connections. Is it not reading those settings? Is the application actually using hikari for database connectivity?

spring.datasource.url=jdbc:mysql://localhost/db
spring.datasource.username=root
spring.datasource.password=pwd
spring.datasource.type= com.zaxxer.hikari.HikariDataSource

spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider
spring.jpa.hibernate.hikari.minimumIdle=20
spring.jpa.hibernate.hikari.maximumPoolSize=30
spring.jpa.hibernate.hikari.idleTimeout=30000

spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.EJB3NamingStrategy
spring.jpa.properties.hibernate.show_sql=false
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.properties.hibernate.cache.use_query_cache=false
spring.jpa.properties.hibernate.cache.use_second_level_cache=false
spring.jpa.properties.hibernate.cache.default_cache_concurrency_strategy=READ_WRITE
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

12:51:02.459 [main] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
12:51:02.464 [main] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
12:51:02.465 [main] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
12:51:02.465 [main] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................null
12:51:02.466 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............null
12:51:02.466 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............null
12:51:02.466 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
12:51:02.466 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................null
12:51:02.466 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............null
12:51:02.466 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................null
12:51:02.467 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=}
12:51:02.467 [main] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName................."com.mysql.jdbc.Driver"
12:51:02.467 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
12:51:02.467 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............null
12:51:02.467 [main] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
12:51:02.467 [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailFast..........true
12:51:02.467 [main] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
12:51:02.467 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbc4ConnectionTest.............false
12:51:02.467 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl........................."jdbc:mysql://localhost/BillboardLMC"
12:51:02.467 [main] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................10
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................null
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........null
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................10
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - password........................
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutorService........null
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................null
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............null
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - username........................"root"
12:51:02.468 [main] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
12:51:02.468 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Started.
12:51:02.839 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@498ebaf8
12:51:02.927 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=1, active=0, idle=1, waiting=0)
12:51:02.939 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@332236a7
12:51:02.957 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@3d80884e
12:51:02.964 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@2302c1fb
12:51:02.971 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@35bf60bc
12:51:02.978 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@35206c85
12:51:02.990 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@77970e8d
12:51:02.995 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@1951c0e6
12:51:03.003 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@61f97d4
12:51:03.009 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection com.mysql.jdbc.JDBC4Connection@2e9ffaf6
12:51:03.009 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - After adding stats (total=10, active=0, idle=10, waiting=0)

[brettwooldridge]

Is spring.jpa.hibernate.hikari even a thing? I can't find any documentation for such properties.

[wesoos]

Brett, I'm going off of these instructions:

https://github.com/brettwooldridge/HikariCP/wiki/Hibernate4

[wesoos]

Guys, I got the following properties to work, kind of. The following creates 2 pools. One connection, in the first pool, and then 20 in the second.

spring.jpa.properties.hibernate.hikari.minimumIdle = 20
spring.jpa.properties.hibernate.hikari.maximumPoolSize = 30
spring.jpa.properties.hibernate.hikari.idleTimeout = 5000
spring.jpa.properties.hibernate.hikari.dataSource.serverName = server
spring.jpa.properties.hibernate.hikari.dataSource.portNumber = 50000
spring.jpa.properties.hibernate.hikari.dataSource.databaseName = db
spring.jpa.properties.hibernate.hikari.dataSource.clientProgramName=appname
spring.jpa.properties.hibernate.hikari.dataSource.currentSchema=schema
spring.jpa.properties.hibernate.hikari.dataSource.user=user
spring.jpa.properties.hibernate.hikari.dataSource.password=pwd
spring.jpa.properties.hibernate.hikari.dataSourceClassName=com.ibm.db2.jcc.DB2SimpleDataSource
spring.jpa.properties.hibernate.hikari.dataSource.driverType=4
spring.jpa.properties.hibernate.connection.provider_class = org.hibernate.hikaricp.internal.HikariCPConnectionProvider

spring.datasource.url=jdbc:db2://server:50000/db
spring.datasource.username=user
spring.datasource.password=pwd

Few things to note. If I leave out "spring.datasource.url", spring will throw an error saying it can't find a datasource. So I leave it in, and it creates the first pool with one connection. Then it creates a second pool correctly with all the hikari properties.

[wesoos]

@brettwooldridge

Any ideas on the above? I don't believe it's the correct behavior, but I can't figure out how to just create one pool with all the hikari properties applied, with an application.properties file in a spring boot application with autoconfiguration.

[wesoos]

I got the following to work finally.

First my application.properties file:

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.minimumIdle = 20
spring.datasource.maximumPoolSize = 30
spring.datasource.idleTimeout = 5000
spring.datasource.dataSourceClassName=com.mysql.jdbc.jdbc2.optional.MysqlDataSource
spring.datasource.dataSourceProperties.databaseName=dbname
spring.datasource.dataSourceProperties.serverName=localhost
spring.datasource.dataSourceProperties.portNumber=3306
spring.datasource.username=root
spring.datasource.password=pwd
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

Also add the following class to you project:

@Configuration
public class MyDataSource {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource(){
        return DataSourceBuilder.create().build();
    }
}

[snicoll]

Is spring.jpa.hibernate.hikari even a thing?

I am surprised as well. In 1.4, the hikari datasource settings are specified via spring.datasource.hikari.*. Maybe that's the source of the confusion?

[jerori]

Hi Stephane,

Hope to be ontime with an answer, don't use spring.* for hikari stuff, use
a simple datasource.* for hikari stuff :
https://github.com/brettwooldridge/HikariCP

Regards!
-jesusRomero

On Tue, Sep 13, 2016 at 11:51 AM, Stéphane Nicoll [email protected]
wrote:

Is spring.jpa.hibernate.hikari even a thing?

I am surprised as well. In 1.4, the hikari datasource settings are
specified via spring.datasource.hikari.*. Maybe that's the source of the
confusion?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/brettwooldridge/HikariCP/issues/604#issuecomment-246728645,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AJ2MKXWRt5ZOsS3NgjTIHLTJKVPCVWAMks5qpsaDgaJpZM4H97c1
.

[1ambda]

I was confused little bit how to set data source related config (e.g prepStmtCacheSize)

This is working config (https://github.com/jhipster/jhipster-sample-app/blob/master/src/main/resources/config/application-prod.yml#L27-L32)

spring:
  datasource:
    initialize: false
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      data-source-properties:
        # https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        useServerPrepStmts: true
        useLocalSessionState: true
        useLocalTransactionState: true
        rewriteBatchedStatements: true
        cacheResultSetMetadata: true
        cacheServerConfiguration: true
        elideSetAutoCommits: true
        maintainTimeStats: false

And spring.jpa.properties.hibernate.hikari.dataSource doesn't work for me. I am using boot starter 1.5.6 (spring 4.3.10)