CODE EXCEPTIONS

Connection Pooling
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 to look for statements that fail to close.

This problem can be avoided by always using a finally block to close your connection.
 
Hibernate supports a variety of connection pooling mechanisms. If you are using an application server, you may wish to use the built-in pool (typically a connection is obtaining using JNDI). If you can’t or don’t wish to use your application server’s built-in connection pool, Hibernate supports several other connection pools, as shown below

c3p0    http://sourceforge.net/projects/c3p0    Distributed with Hibernate
Apache    DBCP http://jakarta.apache.org/commons/dbcp/   Apache Pool
Proxool   http://proxool.sourceforge.net/    JDBC Pooling Wrapper

STATEMENT CACHE
Certain connection pools, drivers, databases, and other portions of the system may provide an additional cache system, known as a statement cache. This cache stores a partially compiled version of a statement in order to increase performance. By reusing the parsed or precompiled statement, the application is able to trade an increase in memory usage for a boost in performance.

You should consider using a statement cache if one is available, but keep in mind that a statement cache is not the same thing as the other forms of caching.
 
The choice of a connection pool is up to you, but be sure to remember that a connection pool is necessary for every production use.

If you wish to use c3p0, the version distributed with Hibernate 2.1.2 (0.8.3) is out of date (and GPL is a problem if you wish to distribute a non-GPL application). If you wish to distribute an application that makes use of c3p0, make sure to download the latest (LGPL) release, c3p0-0.8.4-test1 or later.

Because Hibernate ships with c3p0, configuration is a simple matter of adding a few Hibernate configuration properties to your hibernate.properties (or hibernate.cfg.xml) file.

hibernate.connection.driver_class=com.mysql.jdbc.Driver
hibernate.connection.url=jdbc:mysql://localhost/hibernate
hibernate.connection.username=root
hibernate.connection.password=
hibernate.dialect=net.sf.hibernate.dialect.MySQLDialect
hibernate.show_sql=false

hibernate.c3p0.max_size=1
hibernate.c3p0.min_size=0
hibernate.c3p0.timeout=5000
hibernate.c3p0.max_statements=100
hibernate.c3p0.idle_test_period=300
hibernate.c3p0.acquire_increment=2
hibernate.c3p0.validate=false

The properties shown in above listing are as described below

c3p0 Configuration Options                                Property Meaning                                         Property Example

Maximum number of database connections          hibernate.c3p0.max_size                                 15

to open                                                                                

Initial number of database connections                   hibernate.c3p0.min_size                                     3

Maximum idle time for a connection (in sec)         hibernate.c3p0.timeout                                       5000

Maximum size of c3p0 statement cache                   hibernate.c3p0.max_statements                      0

(0 to turn off)

Number of connections in a clump acquired           hibernate.c3p0.acquire_increment                  3
when pool is exhausted
 
Idle time before a c3p0 pooled connection               hibernate.c3p0.idle_test_period                      300
is validated (in seconds)

If you prefer to use Apache DBCP, make sure that the Apache DBCP library is on your class path, and add the properties to your hibernate.properties file, as shown in below Table

Apache DBCP Configuration Options                         Property Meaning Property                   Example

Maximum number of checked-out                                                hibernate.dbcp.maxActive                           8

database connections  

Maximum number of idle database connections                     hibernate.dbcp.maxIdle                                8
for connection pool 

Maximum idle time for connections in connection                hibernate.dbcp.maxWait                               -1
pool (expressed in ms). 

Set to -1 to turn off 
  
Action to take in case of an exhausted                                           hibernate.dbcp.whenExhaustedAction     1
DBCP connection pool. Set to 0 to fail,
1 to block until a connection is made
available, or 2 to grow) 

Validate connection when borrowing connection                    hibernate.dbcp.testOnBorrow                        true | false
from pool (defaults to true) 

Validate connection when returning connection                      hibernate.dbcp.testOnReturn                         true | false
 to pool (optional, true, or false)
 
Query to execute for connection
hibernate.dbcp.testOn Borrow or
hibernate.dbcp.testOnReturn)                                                         hibernate.dbcp.validationQuery                       Valid SQL

Maximum number of checked-out statements                          hibernate.dbcp.ps.maxActive                             8

Maximum number of idle statements                                            hibernate.dbcp.ps.maxIdle                                   8

Maximum idle time for statements (in ms)                                 hibernate.dbcp.ps.maxWait                      1000 * 60 * 30

Action to take in case of an exhausted statement pool.
Set to 0 to fail, 1 to block until a statement is
made available, or 2 to grow)                                                            hibernate.dbcp.ps.whenExhaustedAction              1

Finally, if you wish to use Proxool as your connection pool provider, you will need to specify hibernate.properties values as shown in below Table. Unlike c3p0 and DBCP, you will need to include additional configuration options as described athttp://proxool.sourceforge.net/configure.html.

Proxool Configuration Options                                   Property Meaning                                      Property Example

Configure Proxool provider using an XML file                    hibernate.proxool.xml                                      /path/to/file.xml

Configure the Proxool provider using a
properties file .properties                                                            hibernate.proxool.properties                          /path/to/proxool

Configure the Proxool provider from an
existing pool                                                                                       hibernate.proxool.existing_pool                   true | false

Proxool pool alias to use (required for
hibernate.proxool .existing_pool, #
hibernate.proxool .properties,
hibernate .proxool.xml)                                                         hibernate.proxool.pool_alias                                 Proxool  conf