Configure Hibernate C3P0 Connection Pooling

Question

I'm stumbled upon a problem while developing a Web Application based on Struts2 with Spring and Hibernate.

When I refresh the site a few times, for example 2-4 times, Hibernate is showing up an exception about Too many connections. I've tried to implement C3P0 Connection pool and have some problems with it

The hibernate.cfg.xml configuration:

<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/axelle</property>
<property name="hibernate.connection.username">axelle</property>
<property name="hibernate.connection.password">dbpassword</property>
<property name="hibernate.current_session_context_class">thread</property>    

<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>

applicationContext.xml

<bean id="propertyConfigurer"
      class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
      p:location="classpath:jdbc.properties"/>

<bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource"
      p:driverClassName="${jdbc.driverClassName}"
      p:url="${jdbc.url}"
      p:username="${jdbc.username}"
      p:password="${jdbc.password}"
      p:connectionProperties="${jdbc.connectionProperties}"/>

<!-- ADD PERSISTENCE SUPPORT HERE (jpa, hibernate, etc) -->

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="configLocation">
        <value>classpath:hibernate.cfg.xml</value>
    </property>
</bean>

<!-- Transaction manager for a single Hibernate SessionFactory (alternative to JTA) -->
<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory">
        <ref local="sessionFactory"/>
    </property>
</bean>

The log output is:

org.hibernate.exception.JDBCConnectionException: Cannot open connection

and:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"

And this is how PROCESSLIST MySQL window looks: http://img844.imageshack.us/img844/3959/be69273cc2.png

I've set max_size of connections to 20, but it seems like it doesn't read the C3P0 configuration from file, cause from the screen we can see that number of connections is higher than 20, or maybe I'm doing something wrong, but where? I really need some help guys, I'll appreciate this, and thanks in advance.

Can you make sure that you are closing all the connections that you are opening. Thats the basic cause of connection leak — Rachit Agrawal
Sorry, but this is the first time when I try to implement a Connection pool, can you advice how I need to close it? but anyway I'm trying to close all the sessions after querying the database with transaction.commit(); session.flush(); session.close();. — Denees
@RomanC I read about DBCP and C3P0 and thought that C3P0 is perfect for me. — Denees

UdayKiran Pulipati UdayKiran Pulipati · Accepted Answer · 2013-06-12T11:22:19

Mention these property in your hibernate.cfg.xml file

    <property name="hibernate.c3p0.acquire_increment">1</property>
    <property name="hibernate.c3p0.idle_test_period">100</property>
    <property name="hibernate.c3p0.max_size">10</property>
    <property name="hibernate.c3p0.max_statements">10</property>
    <property name="hibernate.c3p0.min_size">10</property>
    <property name="hibernate.c3p0.timeout">100</property>

Refer this link for better understanding: Configuration Properties

Configure Hibernate C3P0 Connection Pooling

2 Answers