Configuring c3p0 connection provider in Hibernate

When you run your application with default Hibernate configurations you may have seen following line in your console.
>> Using Hibernate built-in connection pool (not for production use!)

What does this mean?

Following is an extract from Hibernate 3.3 documentation:
Hibernate's own connection pooling algorithm is, however, quite rudimentary. It is intended to help you get started and is not intended for use in a production system, or even for performance testing. You should use a third party pool for best performance and stability. 
(Reference: http://docs.jboss.org/hibernate/orm/3.3/reference/.../...)

The same can be found in different words in other Hibernate documentations too. In 4.1:
Hibernate's internal connection pooling algorithm is rudimentary, and is provided for development and testing purposes. Use a third-party pool for best performance and stability.
(Reference:http://docs.jboss.org/hibernate/orm/4.1/devguide/../../..)

So according to the documentation Hibernate does not recommend using its built-in connection provider. The same documentation recommends the c3p0 connection provider to use.

These are the quick steps to add c3p0 as your connection provider

Adding required jars
When you are using c3p0, you need to add these two jars to your project.
c3p0-x.x.x.x.jar
mchange-commons-java-x.x.x.x.jar

However when you have configured c3p0, Hibernate uses its C3P0ConnectionProvider for connection pooling .
So it additionally needs following jar which contains the above class.
hibernate-c3p0-x.x.x.Final.jar

If you are using Oracle as your database you may need to add following library also.
c3p0-oracle-thin-extras-x.x.x.x.jar
However if you are not using CLOB or BLOB data types, you may not need to add them.

Now we have added all the required libraries.

But how do we tell Hibernate to use c3p0 connection provider instead of its inbuilt connection provider?

Hibernate documentation says:
To use a third-party pool, replace the hibernate.connection.pool_size property with settings specific to your connection pool of choice. This disables Hibernate's internal connection pool.

According to this line Hibernate will automatically disables its  internal connection pool if you properly configured the c3p0 (or any third-party pool).

Adding c3p0 properties to hibernate.cfg.xml
Open hibernate.cfg.xml file and add following lines. You can change values according to your requirements.

<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.max_size">200</property>
<property name="hibernate.c3p0.max_statements">100</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.validationQuery">SELECT 1</property>

Now start your application and you will now not see the line "Using Hibernate built-in connection pool (not for production use!)" in your console. Instead you will see a line like this.
>> Instantiating explicit connection provider:org.hibernate.service.jdbc.connections.
   internal.C3P0ConnectionProvider

You have successfully configured c3p0 connection provider.

According to Hibernate documentation above four parameters are the most important. However there are more other parameters that you can use.
All of them can be found in c3p0 official website.
http://www.mchange.com/projects/c3p0/#hibernate-specific

Usage of above main parameters are listed below.
hibernate.c3p0.min_size - Minimum number of JDBC connections in the pool. (Hibernate default: 1)
hibernate.c3p0.max_size - Max number of JDBC connections in the pool. (Hibernate default: 100)
hibernate.c3p0.max_statements - The size of c3p0's global PreparedStatement cache. (Hibernate default: 0. no caching)
hibernate.c3p0.timeout - Seconds a Connection can remain pooled but unused before being discarded. (Hibernate default:0 never expires)