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.
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>
- hibernate.c3p0.min_size – Minimum number of JDBC connections in the pool. Hibernate default: 1
- hibernate.c3p0.max_size – Maximum number of JDBC connections in the pool. Hibernate default: 100
- hibernate.c3p0.timeout – When an idle connection is removed from the pool (in second). Hibernate default: 0, never expire.
- hibernate.c3p0.max_statements – Number of prepared statements will be cached. Increase performance. Hibernate default: 0 , caching is disable.
- 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.
For detail about hibernate-c3p0 configuration settings, please read this article.
Run it, output
Done, run it and see the following output :
During the connection initialize process, 5 database connections are created in connection pool, ready reuse for your web application.
Download it – Hibernate-C3P0-Connection-Pool-Example.zip (8KB)
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:
very helpful tip. Its sad that i stuck for an hour trying to figure it out 😉
Please update the configuration with latest spring boot application.
Can you provide the query that is used to get the no. of Database connections ready, that you have mentioned at the end.
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.
Very informative site. Enjoy your examples.
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.
Hi,
do you know how to configure multiple datasources using connection pool with
? Spring is not relevant in our case! Maybe u have an extraordinarily link ressource for me :))
Sincerely Ünhan
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?
Hello, What do you think about BoneCP ?
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
Could you please tell me the sql command for mysql to check how many connections have been created by connection poo
r u find solution for this?
SHOW PROCESSLIST;
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
Do you really care that much as to what the default value is? LOL