Guide: c3p0 – JDBC3 Connection and Statement Pooling

After I uploaded my JavaEE course project to Openshift hosting service as you
could do using the Guide: Introduction to Openshift hosting Apache Tomcat, I thought
that my work with this project is done. But strangely for me I started to encounter
database connectivity issues. I done a little research and realized that the reason for
those problems is the default MySQL configuration for closing inactive connections to
the database. MySQL automatically closes any inactive connections to the database
after the default 8 hours. Unfortunately Hibernate ins’t aware of that and still thinks
that the connection is open when trying access the database to perform a query.
Of course Hibernate will fail to perform that task and we will be given the following exception:

[org.hibernate.exception.JDBCConnectionException: could not execute query] with root cause java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

To fix this issue as it’s done in production environments it is important to implement
some sort of connection pool mechanism that will always keep open shared connections
to the database for all application users. Here I’m going to show you how to implement the
c3p0 – JDBC3 Connection and Statement Pooling mechanism in your project with
Hibernate already implemented:

1. So clearly the first thing that needs to be done is to download the c3p0 project files.
Use the following link to download the latest version from Source-forge.

2. Extract the files from the zip archive and copy the c3p0-0.9.2-pre7.jar
and mchange-commons-java-0.2.3.2.jar to the lib folder of your project.

3. Edit your hibernate.cfg.xml and add the following infomation under the
<session-factory> hive:

<!-- important to specify c3p0 as your connection provider -->
<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

<!-- configuration for connection pool via c3p0 -->
<property name="c3p0.min_size">3</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.max_statements">50</property>
<property name="c3p0.timeout">14400</property>
<property name="c3p0.idle_test_period">3600</property>

It is really important to specify the “connection.provider_class” property as:
org.hibernate.connection.C3P0ConnectionProvider or hibernate will continue using it’s
own default mechanism. Other properties may be customized to your liking.
Eventually your hibernate configuration file should look like this one:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
                                         "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
	<session-factory>
        <!-- default hibernate connection settings -->
		<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
		<property name="connection.url">jdbc:mysql://localhost:3306/database</property>
		<property name="connection.username">user</property>
		<property name="connection.password">password</property>
		<!-- for MySQL users specify the following dialect -->
		<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
		<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
		<property name="show_sql">true</property>
		<property name="hbm2ddl.auto">update</property>
		<!-- inmportant to specify c3p0 as your connection provider -->
		<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
		<!-- configuration for connection pool via c3p0 -->
		<property name="c3p0.min_size">3</property>
		<property name="c3p0.max_size">20</property>
		<property name="c3p0.acquire_increment">1</property>
		<property name="c3p0.max_statements">50</property>
		<property name="c3p0.timeout">14400</property>
		<property name="c3p0.idle_test_period">3600</property>
		<!-- end of configuration -->
		<mapping class="com.eadesign.hibernate.CouponDetails"/>
		<mapping class="com.eadesign.hibernate.Company"/>
		<mapping class="com.eadesign.dto.User"/>
		<mapping class="com.eadesign.dto.Administrator"/>
		<mapping class="com.eadesign.hibernate.Feedback"/>	
	</session-factory>
</hibernate-configuratio

And that’s it, now you have connection pool in your project.

more information can be found in the c3p0 manual.
As this Appendix C: Hibernate-specific notes:

Appendix C-Hibernate of c3p0

December 19, 2012Emil Adjiev No Comments »
FILED UNDER :Uncategorized

Leave a comment