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.

Answer 1

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

Answer 2

In your applicationContext, instead of using <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" ... try using <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" ... The C3P0PooledDataSource will create a wrapped database connection using the specified DriverClassName, url, username and password