Improved data source for Spring

Why and what about C3p0

c3p0 is an easy-to-use library for making traditional JDBC drivers "enterprise-ready" by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2. In particular, c3p0 provides several useful services:

Classes which adapt traditional DriverManager-based JDBC drivers to the newer javax.sql.DataSource scheme for acquiring database Connections.

Transparent pooling of Connection and PreparedStatements behind DataSources which can "wrap" around traditional drivers or arbitrary unpooled DataSources.

The library tries hard to get the details right:

c3p0 DataSources are both Referenceable and Serializable, and are thus suitable for binding to a wide-variety of JNDI-based naming services.

Statement and ResultSets are carefully cleaned up when pooled Connections and Statements are checked in, to prevent resource- exhaustion when clients use the lazy but common resource-management strategy of only cleaning up their Connections....

The library adopts the approach defined by the JDBC 2 and 3 specification (even where these conflict with the library author's preferences). DataSources are written in the JavaBean style, offering all the required and most of the optional properties (as well as some non-standard ones), and no-arg constructors. All JDBC-defined internal interfaces are implemented (ConnectionPoolDataSource, PooledConnection, ConnectionEvent-generating Connections, etc.) You can mix c3p0 classes with compliant third-party implementations (although not all c3p0 features will work with external implementations).

c3p0 now fully supports the JDBC4 specification.

c3p0 hopes to provide DataSource implementations more than suitable for use by high-volume "J2EE enterprise applications".

Change to maven
                  <dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.2.1</version>
</dependency>


Change to Spring config where data source is created

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<property name="jdbcUrl" value="${LIBRARY_JDBC_CONNECTION_STRING}" />
<property name="user" value="${LIBRARY_DATASOURCE_USER_NAME}" />
<property name="password" value="${LIBRARY_DATASOURCE_USER_PASSWORD}" />

<property name="idleConnectionTestPeriod" value="${C3P0_POOL_IDLE_CONNECTION_TEST_PERIOD}"/> 
<property name="preferredTestQuery" value="select 1"/>

<!-- performance improvements and configuration so that you don't get above error from: http://javatech.org/2007/11/c3p0-connectionpool-configuration-rules-of-thumb/ -->
<property name="acquireIncrement" value="${C3P0_POOL_ACQUIRE_INCREMENT}"/>
<property name="maxIdleTime" value="${C3P0_POOL_MAX_IDLE_TIME}"/>
<property name="maxIdleTimeExcessConnections" value="${C3P0_POOL_MAX_IDLE_TIME_EXCESS_CONNECTIONS}"/>
<property name="maxPoolSize" value="${C3P0_POOL_MAX_POOL_SIZE}"/>
<property name="minPoolSize" value="${C3P0_POOL_MIN_POOL_SIZE}"/>
<property name="numHelperThreads" value="${C3P0_POOL_NUM_HELPER_THREADS}"/>
<property name="unreturnedConnectionTimeout" value="${C3P0_POOL_UNRETURNED_CONNECTION_TIMEOUT}"/>  
</bean>

Example  values

C3P0_POOL_ACQUIRE_INCREMENT : 1 
C3P0_POOL_IDLE_CONNECTION_TEST_PERIOD : 300 
C3P0_POOL_MAX_IDLE_TIME : 3600 
C3P0_POOL_MAX_IDLE_TIME_EXCESS_CONNECTIONS : 2 
C3P0_POOL_MAX_POOL_SIZE : 4 
C3P0_POOL_MIN_POOL_SIZE : 2 
C3P0_POOL_NUM_HELPER_THREADS :2 
C3P0_POOL_UNRETURNED_CONNECTION_TIMEOUT : 3600

References
http://www.mchange.com/projects/c3p0/