Configure Spring's Hibernate SessionFactory with Connection Pooling

Ever seen the following exception and had no clue how to solve it?

0000-00-00 00:00:00,000 ERROR org.hibernate.transaction.JDBCTransaction - JDBC begin failed
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was37277 seconds ago.The last packet sent successfully to the server was 37277 seconds  ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the serv er configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

The simplest explanation is that Hibernate was trying to use a connection that was closed (timed out) by the database (MySQL in the example above). You google the exception and find endless solutions when most of them are telling you you need a connection pool. Sounds easy, right? Simply add some Hibernate properties to your session factory bean and the problem solved.... Not really.

When using Spring ORM to create our session factory, we usually use some LocalSessionFactoryBean implementation which configure Hiberante and initialize it properly. What it doesn't tell us is that Spring is actually overriding connection settings - specifically the connection provider. The following was taken from the actual session factory initialization code in LocalSessionsFactoryBean:

if(dataSource != null) {
  Class providerClass = LocalDataSourceConnectionProvider.class;
  if(isUseTransactionAwareDataSource()||dataSource instanceof TransactionAwareDataSourceProxy) {
    providerClass = TransactionAwareDataSourceConnectionProvider.class;
  } else if(config.getProperty(Environment.TRANSACTION_MANAGER_STRATEGY) != null) {
    providerClass = LocalJtaDataSourceConnectionProvider.class;
  }
  // Set Spring-provided DataSource as Hibernate ConnectionProvider.
  config.setProperty(Environment.CONNECTION_PROVIDER, providerClass.getName());
}

So how do we configure our connection to use some connection pooling? Instead of configuring our session factory, we configure the data source to use a pooled data source. By using c3p0's ComboPooledDataSource we're able to wrap our current data source with a connection pool by configuring few simple properties. The following is an example how to declare our data source bean (values shouldn't be hard-coded, visible here for example purpose):

<bean id="dataSource" 
    class="com.mchange.v2.c3p0.ComboPooledDataSource" 
    p:driverClass="com.mysql.jdbc.Driver"
    p:jdbcUrl="jdbc:mysql://localhost:3306/db"
    p:user="myuser" p:password="secret" 
    p:minPoolSize="5" p:maxPoolSize="20" 
    p:maxIdleTime="7200"
    p:idleConnectionTestPeriod="300" 
    p:preferredTestQuery="select 1" />

Another property you might wish to add is p:testConnectionOnCheckIn="true" but you may have to pay a price - performance wise. This property tells our connection provider to re-validate the connection before performing any database operations. If you think your connections will be violently closed - use it.

That's it, we're done. Our data source is wrapped with a connection pool and Spring session factory is guaranty to have an available connection - a live one. I recommend having some performance tuning to figure out the optimal settings for your application.