Hibernate Too Many Connections With Cache

Question

I'm using hibernate to try and retrieve a cached query.

@Transactional
public interface ProductDAO extends JpaRepository<Product, Long> {
    @QueryHints({ @QueryHint(name = "org.hibernate.cacheable", value = "true") })
    Product findByCode(String code);
}

I'm load testing and I'm doing this in a large loop of 1000 iterations.

for (int i = 0; i < 500; i++) {
            URL myURL = new URL("http://localhost:8080/test");
            URLConnection myURLConnection = myURL.openConnection();
            myURLConnection.connect();
            myURLConnection.getContent();
        }

I've checked with showsql and I can see only 1 SQL statement is generated for my first hit to the DB after which it is cached.

Yet I still get the following error even though no SQL is being shown:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections

My Hibernate properties:

#hibernate properties
hibernate.dialect = ${hibernate.dialect}
hibernate.show_sql = false
hibernate.hbm2ddl.auto = ${hibernate.hbm2ddl}
hibernate.c3p0.min_size = 10
hibernate.c3p0.max_size = 100
hibernate.c3p0.timeout = 300
hibernate.c3p0.max_statements = 50
hibernate.c3p0.acquire_increment = 5
hibernate.c3p0.idle_test_period = 3000

hibernate.cache.use_second_level_cache=true
hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory
hibernate.cache.use_query_cache=true

Database config:

<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="url" value="${db.url}" />
    <property name="driverClassName" value="${db.driverClassName}" />
    <property name="username" value="${db.username}" />
    <property name="password" value="${db.password}" />
</bean>

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="entityManagerFactory">
    <property name="dataSource" ref="dataSource" />

    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="showSql" value="false" />
            <property name="generateDdl" value="true" />
            <property name="databasePlatform" value="${hibernate.dialect}" />
        </bean>
    </property>

    <property name="jpaProperties" ref="hibernateProperties" />

    <property name="packagesToScan">
        <array>
            <value>com.exammple.model</value>
        </array>
    </property>
</bean>

<bean id="hibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
    <property name="location" value="classpath:/spring/hibernate.properties" />
</bean>

<bean id="sessionFactory" factory-bean="entityManagerFactory" factory-method="getSessionFactory" />

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="dataSource" ref="dataSource" />
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

<tx:annotation-driven transaction-manager="transactionManager" />

<jpa:repositories base-package="com.example.dal" entity-manager-factory-ref="entityManagerFactory"
    transaction-manager-ref="transactionManager" repository-impl-postfix="CustomImpl" />

Answer 1

The problem is your configuration you aren't using a connection pool.

You are configuring a DriverManagerDataSource which isn't a proper connection pool. You are injecting this bean into the LocalContainerEntityManagerFactoryBean which renders your hibernate.connection and hibernate.c3p0 properties useless, they aren't used.

Solution is quite easy drop the hibernate.c3p0 and hibernate.connection properties and replace the DriverManagerDataSource with a proper pool implementation. I would recommend HikariCP over C3P0 but that is personal preference.

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
    <property name="poolName" value="springHikariCP" />
    <property name="connectionTestQuery" value="SELECT 1" />
    <property name="dataSourceClassName" value="${db.driverClassName}" />
    <property name="dataSourceProperties">
        <props>
            <prop key="url">${db.url}</prop>
            <prop key="user">${db.username}</prop>
            <prop key="password">${jdb.password}</prop>
        </props>
    </property>
</bean>

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <constructor-arg ref="hikariConfig" />
</bean>