How to configure C3P0 Connection pool in Hibernate ?

hibernate.cfg.xml configuration

Example

<hibernate-configuration>

  <session-factory>

    [OTHER PROPERTIES ...]

    <property name="hibernate.c3p0.min_size">5</property>

    <property name="hibernate.c3p0.max_size">20</property>

    <property name="hibernate.c3p0.initial_pool_size">5</property>

    <property name="hibernate.c3p0.min_pool_size">1</property>

    <property name="hibernate.c3p0.max_pool_size">50</property>

    <property name="hibernate.c3p0.timeout">300</property>

    <property name="hibernate.c3p0.max_statements">50</property>

    <property name="hibernate.c3p0.idle_test_period">60</property>

    <property name="hibernate.c3p0.maxIdleTime">1800</property>

    <property name="hibernate.c3p0.acquire_increment">1</property>

  </session-factory>

</hibernate-configuration>

C3P0 Properties

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.initial_pool_size

Initial pool size

C3P0 default: 3

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

hibernate.c3p0.acquire_increment

Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted.

Hibernate default: 1

Configuration in c3p0.properties

This file should be at classpath.

propertyCycle

Maximum time in seconds before user configuration constraints are enforced.

c3p0 enforces configuration constraints continually, and ignores this parameter. It is included for JDBC3 completeness.

C3P0 default: 300

testConnectionOnCheckout

Don't use it, this feature is very expensive.

If set to true, an operation will be performed at every connection checkout to verify that the connection is valid.

A better choice is to verify connections periodically using c3p0.idleConnectionTestPeriod.

C3P0 default: false

autoCommitOnClose

The JDBC spec is unfortunately silent on what should happen to unresolved, pending transactions on Connection close.

C3P0's default policy is to rollback any uncommitted, pending work.

Setting autoCommitOnClose to true causes uncommitted pending work to be committed, rather than rolled back on Connection close.

C3P0 default: false

forceIgnoreUnresolvedTransactions

Setting this to true may lead to subtle and bizarre bugs. This is a terrible setting, leave it alone unless absolutely necessary.

It is here to work around broken databases / JDBC drivers that do not properly support transactions, but that allow Connections' autoCommit flags to be set to false regardless.

If you are using a database that supports transactions "partially" (this is oxymoronic, as the whole point of transactions is to perform operations reliably and completely, but nevertheless, such databases exist), if you feel comfortable ignoring the fact that Connections with autoCommit == false may be in the middle of transactions and may hold locks and other resources, you may turn off c3p0's wise default behavior, which is to protect itself, as well as the usability and consistency of the database, by either rolling back (default) or committing (see c3p0.autoCommitOnClose above) unresolved transactions.

This should only be set to true when you are sure you are using a database that allows Connections' autoCommit flag to go to false, but that it offers no other meaningful support of transactions. Otherwise setting this to true is just a bad idea.

Strongly disrecommended. 

C3P0 default: false

numHelperThreads

c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks.

Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously.

C3P0 default: 3

factoryClassLocation

DataSources that will be bound by JNDI and use that API's Referenceable interface to store themselves may specify a URL from which the class capable of dereferencing a them may be loaded.

If (as is usually the case) the c3p0 libraries will be locally available to the JNDI service, leave this set to null.

C3P0 default: null

c3p0 JAR

At last, you should put jar file "c3p0-0.9.1.2.jar" at lib folder of application.