Opening a connection to a database is generally much more expensive than executing an SQL statement. A connection pool is used to minimize the number of connections opened between application and database. It serves as a librarian, checking out connections to application code as needed. Much like a library, your application code needs to be strict about returning connections to the pool when complete, for if it does not do so, your application will run out of available connections.

Starving a Pool

When using connection pooling, it is important to remember that a chunk of bad code that neglects to return connections can starve the rest of the application, causing it to eventually run out of connections and hang (potentially failing nowhere near the actual problem). To test for this, set the maximum connections in your pool to a small number (as low as 1), and use tools like p6spy and IronTrack SQL (described above) to look for statements that fail to close.

Hibernate, being an Object->Relational DB Mapper for Java, needs access to JDBC connections. Out of the box, Hibernate is fairly self contained when it comes to connection control. By default, Hibernate ships with the ability to obtain a data source implementation ( javax.sql.DataSource ) from JNDI by setting the properties appropriately:

hibernate.connection.datasource = java:/comp/env/jdbc/test

Alternatively, if JNDI isn't an option, you can use a Hibernate-internal connection pool implementation (C3PO), and simply give driver/url information for Hibernate to create and pool its own connections:

hibernate.connection.driver_class = org.postgresql.Driver
hibernate.connection.url = jdbc: postgresql://localhost/mydatabase
hibernate.connection.username = myuser
hibernate.connection.password = secret
hibernate.c3p0.min_size=5
hibernate.c3p0.max_size=20
hibernate.c3p0.timeout=1800
hibernate.c3p0.max_statements=50

Configuration

Here is a sample of C3P0 configuration. This is an extract of hibernate.cfg.xml:

<!-- configuration pool via c3p0--> 
<property name="c3p0.acquire_increment">1</property> 
<property name="c3p0.idle_test_period">100</property> <!-- seconds --> 
<property name="c3p0.max_size">100</property> 
<property name="c3p0.max_statements">0</property> 
<property name="c3p0.min_size">10</property> 
<property name="c3p0.timeout">100</property> <!-- seconds --> 
<!-- DEPRECATED very expensive property name="c3p0.validate>-->