How to Configure C3P0 Connection Pool in Hibernate?

Hibernate supports various types of connection pooling mechanisms such as C3P0, Apache DBCP, Proxool  but i am going to show you how to integrate  Connection Pool – C3P0, with Hibernate.

1.Download C3P0 jar file from here  or get this jar from JBoss repository and add c3p0-0.9.1.2.jar  file in your  CLASSPATH.

2. Configure c3p0 properties :

You can configure c3p0 properties in hibernate.cfg.xml, like this:

<?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>
        <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/testpooling?autoReconnect=true</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">root</property>
        <property name="hibernate.current_session_context_class">thread</property>
        <property name="hibernate.cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

         <!-- start connection pool (C3P0) settings -->
        <property name="hibernate.connection_provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.min_size">2</property>
        <property name="hibernate.c3p0.max_size">100</property>
        <property name="hibernate.c3p0.max_statements">50</property>
        <property name="hibernate.c3p0.timeout">1800</property>
        <property name="hibernate.c3p0.idle_test_period">300</property>
         <!-- end connection pool (C3P0) settings -->

        <property name="hibernate.show_sql">true</property>
        <mapping resource="com/jkit/connectionpool/beans/ProfileTable.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

Lets understand  c3p0 configuration properties used in  hibernate.cfg.xml.

1. hibernate.connection_provider_class - used to set the class that implements Hibernate's Connection Provider interface.

2. hibernate.c3p0.min_size - used to set the minimum number of JDBC connections  in the pool.

3. hibernate.c3p0.max_size - used to set the maximum number of JDBC connections in the pool.

4. hibernate.c3p0.max_statements - used to set the max number of prepared statement to be cached.

5. hibernate.c3p0.timeout - used to set the timeout period after which an idle connection is removed from the pool. 

6. hibernate.c3p0.idle_test_period - used to set  the idle time in seconds before a connection is automatically validated.

 3. Verification it is working   - 

   If everything works correctly, you'll see the following logs:

Jan 20, 2013 5:08:18 PM org.hibernate.connection.C3P0ConnectionProvider configure
INFO: C3P0 using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost:3306/testpooling?autoReconnect=true
Jan 20, 2013 5:08:18 PM org.hibernate.connection.C3P0ConnectionProvider configure
INFO: Connection properties: {user=root, password=****, provider_class=org.hibernate.connection.C3P0ConnectionProvider}
Jan 20, 2013 5:08:18 PM org.hibernate.connection.C3P0ConnectionProvider configure
INFO: autocommit mode: false
Jan 20, 2013 5:08:18 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
Jan 20, 2013 5:08:18 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:05:05; debug? true; trace: 10]
Jan 20, 2013 5:08:18 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@c282b235 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@156939be [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2rvxty8socj2da1n3mbbj|1f9aeda, idleConnectionTestPeriod -> 300, initialPoolSize -> 2, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 50, maxStatementsPerConnection -> 0, minPoolSize -> 2, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@18eecda9 [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 2rvxty8socj2da1n3mbbj|32087b, jdbcUrl -> jdbc:mysql://localhost:3306/testpooling?autoReconnect=true, properties -> {user=******, password=******, provider_class=org.hibernate.connection.C3P0ConnectionProvider} ], preferredTestQuery -> null, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> 2rvxty8socj2da1n3mbbj|de04cd, numHelperThreads -> 3 ]
Jan 20, 2013 5:08:19 PM org.hibernate.cfg.SettingsFactory buildSettings

from the above logs you can see your connection pooling has been  initialized  according to properties whatever you set in hibernate.cf.xml.

Thank you for reading this Tutorial. Please leave your comments.

Share on Google Plus

About JK STACK

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.