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

About the Author

author image
mkyong
Founder of Mkyong.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

Leave a Reply

avatar
newest oldest most voted
Karsten
Guest
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:

  <property name="hibernate.c3p0.timeout">3000</property>
  <property name="hibernate.c3p0.idle_test_period">300</property>
Augri
Guest
Augri

very helpful tip. Its sad that i stuck for an hour trying to figure it out ;)

sandan
Guest
sandan

Can you provide the query that is used to get the no. of Database connections ready, that you have mentioned at the end.

Guest
Guest
Guest

http://stackoverflow.com/a/3731978/1278129
hibernate.c3p0.idle_test_periodsmust not be higher than hibernate.c3p0.timeout or connections closed by the database will not be properly detected.

Richard
Guest
Richard

Very informative site. Enjoy your examples.

Rafa
Guest
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.

trackback
ActiveMQ | libraries.io
trackback
C3P0 | libraries.io
Ünhan Inay
Guest
Ü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

Ünhan Inay
Guest
Ünhan Inay

Well, i have found this documentation http://www.mchange.com/projects/c3p0/#using_combopooleddatasource but there’s nothing about configuration with JNDi. As my understandings JNDI is the preferred choice to configure that resource. But are there any complications with this choice?

Jamshid
Guest
Jamshid

Hello, What do you think about BoneCP ?

giri
Guest
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
Guest
Astha

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

giri
Guest
giri

r u find solution for this?

Dan
Guest
Dan

SHOW PROCESSLIST;

satyanarayana
Guest
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
Guest
david

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

trackback
Hibernate – Could not find C3P0ConnectionProvider

[…] File : pom.xml <project …>   <repositories> <repository> <id>JBoss repository</id> <url>http://repository.jboss.org/nexus/content/groups/public/</url&gt; </repository> </repositories>   <dependencies>   <!– Hibernate c3p0 connection pool –> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-c3p0</artifactId> <version>3.6.3.Final</version> </dependency>   </dependencies> </project> Note See this detail guide – “How to configure c3p0 connection pool in Hibernate“. […]

trackback
Hibernate Tutorials | J2EE Web Development Tutorials

[…] How to configure the C3P0 connection pool in Hibernate The connection pool is used to increase the system performance by avoid the hit to database too often. The C3PO id the default Hibernate connection pool, here’s a guide to configuration it. […]

trackback
Hibernate Error – Initial SessionFactory creation failed.java.lang.NoClassDefFoundError: com/mchange/v2/c3p0/DataSources | Hibernate

[…] How to configure the C3P0 connection pool in Hibernate […]