一、 Cofigure Connection Testing
c3p0 可以配置多种方式来检测Connection，以最小可能性来防止出现旧的和坏掉的Connection。
c3p0 can be configured to test the Connections that it pools in a variety of ways, to minimize the likelihood that your application will see broken or "stale" Connections.
c3p0存储的Connection会因为多种原因坏掉。
1. JDBC driver 因为和数据库保持太久的连接会主动发出time-out。
2. 后台数据库或者网络有时候会搁浅掉连接。
3. 连接会因为资源泄露、driver的bug、或者其他原因很容易导致不可再使用（over time ）。
some JDBC drivers intentionally "time-out" long-lasting database Connections; back-end databases or networks sometimes go down "stranding" pooled Connections; and Connections can simply become corrupted over time and use due to resource leaks, driver bugs, or other causes.
c3p0 提供的几种configuration paramters
- automaticTestTable
创建一个空表来做简单查询，来避免test database。 - connectionTesterClassName
- idleConnectTestPeriod
表示test connection前，一个空闲的connection可以空闲的时间。换句话说就是空闲连接每idleConnectTestPeriod时间后就会被测试一次来保持active状态。 - preferredTestQuery
最简单的方式加快test速度。它会测试每个Connection，默认null，如果设置的话，默认的ConnectionTester将会通过调用Connection的元数据来执行getTables()函数调用。缺点：初始化数据库没有查询的目标table的话会造成error。
独立查表的话可以使用select 1 就是足够了，如果不可以的话就使用
automaticTestTable代替。a call to DatabaseMetaData.getTables()
is often much slower than a simple database query, and using this test may significantly impair your pool's performance. - testConnectionOnChekcin
- testConnectionOnCheckout
高可靠的测试连接的方式。但是对于要求高性能的client视角来说就会花很大的代价。大部分使用idleConnectTestPeriod、testConnectionOnChekcin组合替代。
解释：
3/5/6表示test的时机，1/2/4表示test的方式。
当配置了test机制，那么c3p0首要的就是把test的代价降到最低。
- JDBC4、c3p0 0.9.5+将调用Connection类的isValid()方法。该方法速度快、可靠性高，可以不设置preferredTestQuery参数。
- JDBC3及以下调用DataBaseMetaData类的getTables()方法获取该方法健壮性较高，而且可以无视database的schema。但是往往查询比一个简单的数据库查询都要慢，明显的降低了连接池的性能。so 最好的办法要解决性能问题，就配置preferredTestQuery=SELECT 1，就不会查元数据了。
备注：DataBaseMetaData是java.sql包下的一个接口，各Sql Vendor实现通过实现该接口来获取相应的数据库元数据。Comprehensive information about the database as a whole.This interface is
implemented by driver vendorsto let users know the capabilities of a Database Management System (DBMS) in combination with the driver based on JDBC technology ("JDBC driver") that is used with it.
源码分析：
看一段C3P0 0.9.1version的获取元数据的代码。这里设置配置项automaticTestTable。
Connection c = null;
PreparedStatement testStmt = null;
PreparedStatement createStmt = null;
ResultSet mdrs = null;
ResultSet rs = null;
boolean exists;
boolean has_rows;
String out;
try
{
c = throwawayPooledConnection.getConnection();
DatabaseMetaData dmd = c.getMetaData();
String q = dmd.getIdentifierQuoteString();
String quotedTableName = q + automaticTestTable + q;
out = "SELECT * FROM " + quotedTableName;
mdrs = dmd.getTables( null, null, automaticTestTable, new String[] {"TABLE"} ); //这里获取配置项配置的automaticTestTable，如果没有该table，则查询所有table。
exists = mdrs.next();
//System.err.println("Table " + automaticTestTable + " exists? " + exists);
if (exists)
{
testStmt = c.prepareStatement( out );
rs = testStmt.executeQuery();
has_rows = rs.next();
if (has_rows)
throw new SQLException("automatic test table '" + automaticTestTable +
"' contains rows, and it should not! Please set this " +
"parameter to the name of a table c3p0 can create on its own, " +
"that is not used elsewhere in the database!");
}
else
{
String createSql = "CREATE TABLE " + quotedTableName + " ( a CHAR(1) )";
try
{
createStmt = c.prepareStatement( createSql );
createStmt.executeUpdate();
}
catch (SQLException e)
{
if (logger.isLoggable( MLevel.WARNING ))
logger.log(MLevel.WARNING,
"An attempt to create an automatic test table failed. Create SQL: " +
createSql,
e );
throw e;
}
}
return out;
}
测试：
- 配置项如标题二所示。我在automaticTestTable所配置的table bj_test中插入一条数据。
发生的现象如下所示。在初始化datasource时，读取配置并尝试测试时发生错误。
automatic test table 'bj_test' contains rows, and it should not! Please set this parameter to the name of a table c3p0 can create on its own, that is not used elsewhere in the database!:
二、test配置
简单test配置如下：
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/test"/>
<property name="user" value="root"/>
<property name="password" value="123456"/>
<property name="initialPoolSize" value="5"/>
<property name="minPoolSize" value="3"/>
<property name="maxPoolSize" value="50"/>
<property name="maxIdleTime" value="20"/>
<property name="acquireIncrement" value="1"/>
<property name="idleConnectionTestPeriod" value="60"/>
<!--<property name="preferredTestQuery"-->
<!--value="SELECT 1"/>-->
<property name="automaticTestTable" value="bj_test"/>
<property name="checkoutTimeout" value="3000"/>
</bean>
注意：
Even with active Connection testing (testConnectionOnCheckout
set to true, or testConnectionOnCheckin and a short idleConnectionTestPeriod), your application may see occasional Exceptions on database restart, for example if the restart occurs after a Connection to the database has already been checked out.
即使设置了test connection配置项，也会发生偶然的Exception。例如当数据库重启发生在数据库连接池已经chekout出一个Connection了。
cull过期的连接
private void cullExpired()
{
assert Thread.holdsLock( this );
if ( logger.isLoggable( MLevel.FINER ) )
logger.log( MLevel.FINER, "BEGIN check for expired resources. [" + this + "]");
// if we do not time-out checkedout resources, we only need to test unused resources
Collection checkMe = ( destroy_unreturned_resc_time > 0 ? (Collection) cloneOfManaged().keySet() : cloneOfUnused() );
for ( Iterator ii = checkMe.iterator(); ii.hasNext(); )
{
Object resc = ii.next();
if ( shouldExpire( resc ) )
{
if ( logger.isLoggable( MLevel.FINER ) )
logger.log( MLevel.FINER, "Removing expired resource: " + resc + " [" + this + "]");
target_pool_size = Math.max( min, target_pool_size - 1 ); //expiring a resource resources the target size to match
removeResource( resc );
if (Debug.DEBUG && Debug.TRACE == Debug.TRACE_MAX) trace();
}
}
if ( logger.isLoggable( MLevel.FINER ) )
logger.log( MLevel.FINER, "FINISHED check for expired resources. [" + this + "]");
ensureMinResources();
}
