Archive for the ‘J2EE – Add-On’S’ Category

Connection Pool

Connection pools are a common way to improve application performance. Rather than opening a separate connection to the database for each request, the connection pool maintains a collection of open database connections that are reused. Application servers often provide their own connection pools using a JNDI DataSource, which Hibernate can take advantage of when configured to use a DataSource.

If you’re running a standalone application or your application server doesn’t support connection pools, Hibernate supports three connection pooling services: C3P0, Apache’s DBCP library, and Proxool. C3P0 is distributed with Hibernate; the other two are available as separate distributions.

Connection pool is good for performance, as it prevents Java application create a connection each time when interact with database and minimizes the cost of opening and closing connections.

It’s a little known fact that the JDBC driver MySQL provides drops connections after being idle for 8 hours. Granted, it can be solved easily by providing a simple parameter on the JDBC url to reconnect when this happens, but it’s often overlooked (and not really recommended, as it doesn’t really promote safe programming). Another thing easily overlooked is the default pool Hibernate uses for its connections. This pool is, as the manual clearly warns, very basic and doesn’t do any clean-up when it comes to idle connections. The result is, after 8 hours of inactivity, your Hibernate application will grind to a halt and only a restart will fix the problem.

C3P0 is an open source JDBC connection pool distributed along with Hibernate in the lib directory. Hibernate will use its org.hibernate.connection.C3P0ConnectionProvider for connection pooling if you sethibernate.c3p0.* properties. If you would like to use Proxool, refer to the and the Hibernate web site for more information.


<?xml version=”1.0″ encoding=”UTF-8″?>
<!DOCTYPE hibernate-configuration PUBLIC “-//Hibernate/Hibernate Configuration DTD 3.0//EN””&gt;
<session-factory name=”hibernateSessionFactory”>
<property name=”hibernate.connection.driver_class”>com.mysql.jdbc.Driver</property>
<property name=”hibernate.connection.password”>gopal</property>
<property name=”hibernate.connection.url”>jdbc:mysql://localhost/gopaldb</property>
<property name=”hibernate.connection.username”>gopal</property>
<property name=”hibernate.default_schema”>hcaldb</property>
<property name=”show_sql”>false</property>
<property name=”hibernate.dialect”>org.hibernate.dialect.MySQLDialect</property>
<!– driver/connection info removed
C3P0 Stuff –>
<property name=”connection.provider_class”>org.hibernate.connection.C3P0ConnectionProvider</property>
<property name=”hibernate.c3p0.max_size”>15</property>
<property name=”hibernate.c3p0.max_statements”>0</property>
<property name=”hibernate.c3p0.min_size”>3</property>
<property name=”hibernate.c3p0.timeout”>25200</property>
<property name=”hibernate.c3p0.autoCommitOnClose”>false</property>
<property name=”hibernate.c3p0.acquireRetryDelay”>1000</property>
<property name=”hibernate.c3p0.acquireRetryAttempts”>60</property>
<property name=”hibernate.c3p0.breakAfterAcquireFailure”>false</property>
<!– determines how many connections at a time c3p0 will try to acquire when the pool is exhausted. –>
<property name=”hibernate.c3p0.acquire_increment”>3</property>
<property name=”hibernate.c3p0.idle_test_period”>14400</property>
<property name=”hibernate.c3p0.preferredTestQuery”>select 1;</property>


<!– Seconds –>
<mapping class=”com.shal.model.Usermaster”/>


Clarity on C3P0 properties:

1)    c3p0.acquireIncrement
Default: 3
Determines how many connections at a time c3p0 will try to acquire
when the pool is exhausted. [See “Basic Pool Configuration”]

2)    c3p0.acquireRetryAttempts=3
Default: 30
Defines how many times c3p0 will try to acquire a new Connection
from the database before giving up. If this value is less than or
equal to zero, c3p0 will keep trying to fetch a Connection
indefinitely. [See “Configuring Recovery From Database Outages”]

3)     c3p0.acquireRetryDelay
Default: 1000
Milliseconds, time c3p0 will wait between acquire attempts.
[See “Configuring Recovery From Database Outages”]

4)     c3p0.idleConnectionTestPeriod=
Default: 0
If this is a number greater than 0, c3p0 will test all idle, pooled
but unchecked-out connections, every this number of seconds.
[See “Configuring Connection Testing”]

5)     c3p0.initialPoolSize=10
Default: 3
Number of Connections a pool will try to acquire upon startup.
Should be between minPoolSize and maxPoolSize. [See “Basic Pool  Configuration”]

6)    c3p0.maxIdleTime=600
Default: 0
Seconds a Connection can remain pooled but unused before being
discarded. Zero means idle connections never expire. [See “Basic  Pool Configuration”]


7)    c3p0.maxPoolSize=128
Default: 15
Maximum number of Connections a pool will maintain at any given
time. [See “Basic Pool Configuration”]

8)    c3p0.minPoolSize=5
Default: 3
Minimum number of Connections a pool will maintain at any
given time. [See “Basic Pool Configuration”]

9)        c3p0.propertyCycle
Default: 300
Maximum time in seconds before user configuration constraints
are enforced. c3p0 enforces configuration constraints continually,
and ignores this parameter. It is included for JDBC 3 completeness.