Configuring Hibernate with HikariCP

Question

Because of problems with c3p0 connection pool, I want to see the alternatives and decide which one might be more usable in my case. HikariCP looks very promising, but there is no documentation how to use it with Hibernate.

So far I am using c3p0 as follows:

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="persistenceUnit"/>
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.dialect">${database.dialect}</prop>
            <prop key="hibernate.hbm2ddl.auto">${database.structure}</prop>
            <prop key="hibernate.connection.url">${database.connection}</prop>
            <prop key="hibernate.connection.username">${database.username}</prop>
            <prop key="hibernate.connection.password">${database.password}</prop>
            <prop key="hibernate.connection.driver_class">${database.driver}</prop>
            <prop key="hibernate.connection.shutdown">true</prop>
            <prop key="hibernate.connection.writedelay">0</prop>
            <prop key="hibernate.connection.characterEncoding">UTF-8</prop>
            <prop key="hibernate.connection.charSet">UTF-8</prop>
            <prop key="hibernate.show_sql">${database.show_sql}</prop>
            <prop key="hibernate.format_sql">false</prop>
            <prop key="hibernate.ejb.metamodel.generation">disabled</prop>
            <!-- Use the C3P0 connection pool provider -->
            <prop key="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>
            <prop key="hibernate.c3p0.min_size">5</prop>
            <prop key="hibernate.c3p0.max_size">30</prop>
            <prop key="hibernate.c3p0.timeout">300</prop>
            <prop key="hibernate.c3p0.max_statements">50</prop>
            <prop key="hibernate.c3p0.idle_test_period">600</prop>
        </props>
    </property>

Can someone point me how to configure HikariCP in such way?

Eft

so, wrt c3p0, you have a bad misconfoguration in the bit above. global max_statements of 50 is way too small for the size of your pool; you're forcing the statement cache to churn through statements. set max_statements to 0 to turn statement caching off if you want behavior comparable to HikariCP. you don't specify what problems you are currently having with c3p0, but you might also consider a more reliable testing regime. – Ecto

I've experimented with the values above, but in all cases I keep getting randomly (roughly once a day) "Apparent deadlocks". On the other hand, the max_statement value I've tried only to increase, but never to disable. I can certainly try that. However, this question is about configuring Hikari with Hibernate. – Spermophyte

oh, i know. and Hikari looks very interesting, very much worth trying out. if you want help c3p0 debugging, let me know. – Ecto

Answer 1

HikariCP, as of version 1.2.6, now supports Hibernate 4.x explicitly with a ConnectionProvider. See the new wiki documentation for details.

Tappet

Perfect, exactly what I was looking for! – Spermophyte

This is no longer the suggested way to use HikariCP in Hibernate - please see my answer which includes details. – Soliz

great library my dude – Solis

Answer 2

You can use the org.hibernate.hikaricp.internal.HikariCPConnectionProvider which is shipped by hibernate-hikaricp package.

You can install it as Maven dependency (please don't forget to update the version number):

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-hikaricp</artifactId>
    <version>5.2.10.Final</version>
</dependency>

And configure it in the hibernate.properties file, like this:

hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider

Please note: As of Hibernate 4.3.6 you should no longer use com.zaxxer.hikari.hibernate.HikariConnectionProvider (see: https://github.com/brettwooldridge/HikariCP/wiki/Hibernate4)

Soliz

Author of HikariCP here. I can confirm that this is now the correct answer. – Tappet

@Soliz I think you've meant: As of Hibernate 4.3.6 don't use com.zaxxer.hikari.hibernate.HikariConnectionProvider any more ;) – Francium

Answer 3

UPDATE: See uwolfer's answer below, it is now the official way to use HikariCP with Hibernate.

I'm one of the authors of HikariCP. I don't claim to be a Spring guy, and I weened off of Hibernate a few years back, but this link might be helpful:

http://www.luckyryan.com/2013/02/20/spring-mvc-with-basic-persistence-spring-data-jpa-hibernate/

In the XML configuration section on that page, where their example uses BoneCP as the mainDataSource, simply try replacing that section with configuration for HikariCP instead.

In your example above, you appear to be configuring Hibernate through Spring and defining the DataSource inside of the Hibernate config, which is fine. But an alternative (presented on that page) is to configure the DataSource separately through Spring and then directing Hibernate to use it.

Regarding statement caching, HikariCP does not do it because we believe that is best left to the vendors' JDBC driver/DataSource. Almost every major DB vendors' JDBC DataSource provides statement caching, and it can be configured through HikariCP by specifying DataSource properties. Refer to the HikariCP github page for how to set properties on the underlying (vendor) DataSource.

Tappet

Actually, I've already seen this document, but I don't like their solution as it is quite complex in comparison to the c3p0 solution above. I don't like the fact, that they actually need to write some code instead of just configuring it via XML. Is that even possible with HikariCP? If so, are you planning to write some tutorials for using it? I think you would get much more people using it if it was more straightforward. – Spermophyte

As far as I understood that page, they present two ways of accomplishing the same task: xml and programatically. The programatic approach is entirely optional, there should be no Java code required. I find the separation of the DataSource and Hibernate config compelling. Not to mince words, I also find the Hibernate ConnectionProvider semantic an abomination and were I working at JBoss the engineer would be fired. Basically it introduces a new interface whose only contribution is supportsAggressiveRelease which could easily be expressed as a config property. – Tappet

Okay, I will go through it once more. However, it would be great to have pure Hikari tutorials. – Spermophyte

I don't disagree Re: tutorials, I started one for this very topic over on the github wiki. Though at this point it is largely borrowed from the link above, I hope to improve it over time. – Tappet

i tried the switch from BoneCP to HikariCP (just like @Tappet said0 but i'm getting [com.zaxxer.hikari.HikariDataSource]: No default constructor found; nested exception is java.lang.NoSuchMethodException: com.zaxxer.hikari.HikariDataSource.<init>() any ideas? – Theotokos

See this page for how to use HikariCP with Hibernate. – Tappet

I'm getting the same problem as @Theotokos when I try to implement this – Iolanthe

Can you come over to the google group and post your Spring configuration? We have several users using Spring/Hibernate successfully, it sounds like a simple configuration issue. – Tappet

Thanks for information. I configured my spring project with a configured HikariDataSource and my EntityManagerFactoryBean is using this data source. And I use hibernate just as JPA provider (using Spring's HibernateJpaVendorAdapter). So, basically all connections are used from hikari data source. It is working and I can see the connection pool. At this point, do I need do something else like telling Hibernate explicitly to use Hikari? – Hendricks

Your EntityManager is Hibernate, correct? It sounds like it is working, so what is the question? If it is not working, I suggest posting a full question here. You will probably get more answers. – Tappet