How to configure the C3P0 connection pool in Hibernate

Connection Pool
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.

See wiki connection pool explanation

Hibernate comes with internal connection pool, but not suitable for production use. In this tutorial, we show you how to integrate third party connection pool – C3P0, with Hibernate.

1. Get hibernate-c3p0.jar

To integrate c3p0 with Hibernate, you need hibernate-c3p0.jar, get it from JBoss repository.

File : pom.xml

<project ...>
 
	<repositories>
		<repository>
			<id>JBoss repository</id>
			<url>http://repository.jboss.org/nexus/content/groups/public/</url>
		</repository>
	</repositories>
 
	<dependencies>
 
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>
			<version>3.6.3.Final</version>
		</dependency>
 
		<!-- Hibernate c3p0 connection pool -->
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-c3p0</artifactId>
			<version>3.6.3.Final</version>
		</dependency>
 
	</dependencies>
</project>

2. Configure c3p0 propertise

To configure c3p0, puts the c3p0 configuration details in “hibernate.cfg.xml“, like this :

File : hibernate.cfg.xml

<?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.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
  <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:MKYONG</property>
  <property name="hibernate.connection.username">mkyong</property>
  <property name="hibernate.connection.password">password</property>
  <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
  <property name="hibernate.default_schema">MKYONG</property>
  <property name="show_sql">true</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>
 
  <mapping class="com.mkyong.user.DBUser"></mapping>
</session-factory>
</hibernate-configuration>
  1. hibernate.c3p0.min_size – Minimum number of JDBC connections in the pool. Hibernate default: 1
  2. hibernate.c3p0.max_size – Maximum number of JDBC connections in the pool. Hibernate default: 100
  3. hibernate.c3p0.timeout – When an idle connection is removed from the pool (in second). Hibernate default: 0, never expire.
  4. hibernate.c3p0.max_statements – Number of prepared statements will be cached. Increase performance. Hibernate default: 0 , caching is disable.
  5. hibernate.c3p0.idle_test_period – idle time in seconds before a connection is automatically validated. Hibernate default: 0
Note
For detail about hibernate-c3p0 configuration settings, please read this article.

Run it, output

Done, run it and see the following output :

c3p0 connection pool in hibernate

During the connection initialize process, 5 database connections are created in connection pool, ready reuse for your web application.

Reference

  1. http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#d0e1748
  2. http://www.mchange.com/projects/c3p0/index.html#appendix_d
Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

  • Pingback: Blue Coaster33()

  • Rafa

    Hi!

    In my case I’m working in a Java Swing application. Integratin C3P0 could be productive for the features of my application (it’s a small ERP using eclipse+mysql+windowbuilder+spring)

    Thanks in advance.

  • Pingback: ActiveMQ | libraries.io()

  • Pingback: C3P0 | libraries.io()

  • Karsten

    Your page is a top search result of google.
    Possible people -like me- will copy your example code with the swapped values for timeout and idle_test_period.

    Correct values need a timeout greater then the idle_test_period:

      &lt;property name=&quot;hibernate.c3p0.timeout&quot;&gt;3000&lt;/property&gt;
      &lt;property name=&quot;hibernate.c3p0.idle_test_period&quot;&gt;300&lt;/property&gt;
  • Ünhan Inay

    Hi,

    do you know how to configure multiple datasources using connection pool with

    com.mchange.v2.c3p0.ComboPooledDataSource

    ? Spring is not relevant in our case! Maybe u have an extraordinarily link ressource for me :))

    Sincerely Ünhan

  • Jamshid

    Hello, What do you think about BoneCP ?

  • giri

    sir,
    you are using Toad software to know how many connections pools are created, with out Toad software how
    to check how many connections pools are created.

    pls tell how to find how many connections pools are created for my application.
    THANK’S FOR YOUR VALUBLE INFORMATION TO US

  • Astha

    Could you please tell me the sql command for mysql to check how many connections have been created by connection poo

    • giri

      r u find solution for this?

      • Dan

        SHOW PROCESSLIST;

  • http://pampanasatyanarayana.wordpress.com satyanarayana

    Dear Yong,

    hibernate.c3p0.min_size – Minimum number of JDBC connections in the pool. Hibernate default: 1
    But when i tested in my local system for minimum size(hibernate.c3p0.min_size),
    Minimum number of JDBC connections in the pool. Hibernate default: 3

    Please confirm me which is correct default :1 or default: 3 connections

    Thanks for posting/sharing valuable information with us

    • david

      Do you really care that much as to what the default value is? LOL

  • Pingback: Hibernate – Could not find C3P0ConnectionProvider()

  • Pingback: Hibernate Tutorials | J2EE Web Development Tutorials()

  • Pingback: Hibernate Error – Initial SessionFactory creation failed.java.lang.NoClassDefFoundError: com/mchange/v2/c3p0/DataSources | Hibernate()