Main Tutorials

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 Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
16 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Karsten
11 years ago

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
8 years ago
Reply to  Karsten

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

Dhanabal
5 years ago

Please update the configuration with latest spring boot application.

sandan
7 years ago

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

Guest
8 years ago

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
10 years ago

Very informative site. Enjoy your examples.

Rafa
10 years ago

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.

Ünhan Inay
11 years ago

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
11 years ago
Reply to  Ü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
11 years ago

Hello, What do you think about BoneCP ?

giri
11 years ago

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
11 years ago

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

giri
11 years ago
Reply to  Astha

r u find solution for this?

Dan
11 years ago
Reply to  giri

SHOW PROCESSLIST;

satyanarayana
11 years ago

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
11 years ago
Reply to  satyanarayana

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