jpa custom connection pool

Question

I've successfully integrated hibernate in my web app. I was happy with my persistence.xml configuration

<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">
    <persistence-unit name="PU">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />
            <property name="hibernate.hbm2ddl.auto" value="validate" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLiteDialect" />
            <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.format_sql" value="true" />
            <property name="hibernate.connection.url" value="jdbc:sqlite:/tmp/database.db" />
            <property name="hibernate.connection.driver_class" value="org.sqlite.JDBC" />
        </properties>
    </persistence-unit>
</persistence>

Then I decided to use HikariCp connection pooler after reading this

The built-in connection pool is not intended for production environments

With this example I managed to make it work partially with the new persistence.xml

<persistence-unit name="PU">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />
            <property name="hibernate.hbm2ddl.auto" value="validate" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLiteDialect" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.format_sql" value="true" />
            <property name="hibernate.connection.provider_class" value="com.zaxxer.hikari.hibernate.HikariConnectionProvider" />
            <property name="hibernate.hikari.minimumPoolSize" value="20" />
            <!-- <property name="hibernate.hikari.maximumPoolSize" value="100" /> -->
            <property name="hibernate.hikari.idleTimeout" value="30000" />
            <property name="hibernate.hikari.dataSourceClassName" value="org.sqlite.SQLiteDataSource" />
            <property name="hibernate.hikari.dataSource.url" value="jdbc:sqlite:/tmp/database.db" />
            <!-- <property name="hibernate.hikari.dataSource.user" value="" />
            <property name="hibernate.hikari.dataSource.password" value="" /> -->
        </properties>
    </persistence-unit>

But I get error if I try to set minimumPoolSize, maximumPoolSize, user and password. If comment them out everything works great.

org.hibernate.HibernateException: java.lang.RuntimeException: java.beans.IntrospectionException: Method not found: setMinimumPoolSize

How can I configure jpa to use hibernate with hikaricp pool? I prefer not to scatter hibernate-specific stuff in my code as I want to keep ORM layer abstract. I found a lot of confusing materials and got more questions than aswers. How are persistence.xml, hibernate.properties and hibernate.cfg.xml related to each other? What is JNDI and how to use it? And what is this bean configuration?

hibernate.* files are nothing to do with JPA, and should not be used if wanting to stay portable. Any connection pool can either use internal JPA implementation code (like you try), or you can just set up a javax.sql.DataSource which is configured to provide pooling (and then that can be used with ANY JPA implementation) — Neil Stockton
If you are dealing with JPA then worry only about the persistence.xml file for the configuration. if you are putting something in the hibernate.* files then you are configuring Hibernate specific settings which is not what you want. You'll use JNDI for the data source part, if youi have an enterprise server(Jboss, weblogic, websphere) you'll get to create a datasource in the server admin console(including the pool configuration) and assign a JNDI name to it. Instead of configuring the connections, connection pools etc in the persistence.xml you just give the jndi name in the same file. — Zeus
Is setting up javax.sql.DataSource same as configuring JNDI? Should I configure a specific Catalina file on every server I deploy my web-app in this case? — gkiko

gkiko gkiko · Accepted Answer · 2015-03-05T14:25:12

Sorry for the original question. After more research I found the solution. This is working persistence.xml. I think user and password can't be set in sqlite. minimumPoolSize -> minimumIdle

<properties>
    <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />
    <property name="hibernate.hbm2ddl.auto" value="validate" />
    <property name="hibernate.dialect" value="org.hibernate.dialect.SQLiteDialect" />
    <property name="hibernate.show_sql" value="false" />
    <property name="hibernate.format_sql" value="true" />
    <property name="hibernate.connection.provider_class" value="com.zaxxer.hikari.hibernate.HikariConnectionProvider" />
    <property name="hibernate.hikari.minimumIdle" value="20" />
    <property name="hibernate.hikari.maximumPoolSize" value="100" />
    <property name="hibernate.hikari.idleTimeout" value="30000" />
    <property name="hibernate.hikari.dataSourceClassName" value="org.sqlite.SQLiteDataSource" />
    <property name="hibernate.hikari.dataSource.url" value="jdbc:sqlite:/tmp/database.db" />
</properties>

As @neil and @zeus suggested here is another configuration using JNDI

<properties>
    <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />
    <property name="hibernate.hbm2ddl.auto" value="validate" />
    <property name="hibernate.dialect" value="org.hibernate.dialect.SQLiteDialect" />
    <property name="hibernate.show_sql" value="true" />
    <property name="hibernate.format_sql" value="true" />
    <property name="hibernate.connection.datasource" value="java:comp/env/jdbc/SQLiteHikari"/>
</properties>

src->main->webapp->META-INF->context.xml

<Context antiJARLocking="true" path="/nbs">
    <Resource name="jdbc/SQLiteHikari" 
        auth="Container"
        factory="com.zaxxer.hikari.HikariJNDIFactory"
        type="javax.sql.DataSource"
        minimumIdle="20"
        maximumPoolSize="100"
        connectionTimeout="300000"
        dataSourceClassName="org.sqlite.SQLiteDataSource"
        dataSource.url="jdbc:sqlite:/tmp/database.db" />
</Context>

jpa custom connection pool

2 Answers