一陷寝、 Cofigure Connection Testing
c3p0 可以配置多種方式來檢測Connection，以最小可能性來防止出現(xiàn)舊的和壞掉的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 因為和數(shù)據(jù)庫保持太久的連接會主動發(fā)出time-out。
?2. 后臺數(shù)據(jù)庫或者網(wǎng)絡(luò)有時候會擱淺掉連接腮出。
?3. 連接會因為資源泄露帖鸦、driver的bug、或者其他原因很容易導(dǎo)致不可再使用（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
? 創(chuàng)建一個空表來做簡單查詢富蓄，來避免test database剩燥。 - connectionTesterClassName
- idleConnectTestPeriod
?表示test connection前慢逾，一個空閑的connection可以空閑的時間。換句話說就是空閑連接每idleConnectTestPeriod時間后就會被測試一次來保持active狀態(tài)灭红。 - preferredTestQuery
?最簡單的方式加快test速度侣滩。它會測試每個Connection，默認(rèn)null变擒，如果設(shè)置的話君珠，默認(rèn)的ConnectionTester將會通過調(diào)用Connection的元數(shù)據(jù)來執(zhí)行g(shù)etTables()函數(shù)調(diào)用。缺點：初始化數(shù)據(jù)庫沒有查詢的目標(biāo)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的方式物臂。
當(dāng)配置了test機制，那么c3p0首要的就是把test的代價降到最低产上。
- JDBC4棵磷、c3p0 0.9.5+將調(diào)用Connection類的isValid()方法。該方法速度快晋涣、可靠性高仪媒，可以不設(shè)置preferredTestQuery參數(shù)。
- JDBC3及以下調(diào)用DataBaseMetaData類的getTables()方法獲取該方法健壯性較高谢鹊，而且可以無視database的schema规丽。但是往往查詢比一個簡單的數(shù)據(jù)庫查詢都要慢，明顯的降低了連接池的性能撇贺。so 最好的辦法要解決性能問題赌莺，就配置preferredTestQuery=SELECT 1，就不會查元數(shù)據(jù)了松嘶。
備注：DataBaseMetaData是java.sql包下的一個接口艘狭，各Sql Vendor實現(xiàn)通過實現(xiàn)該接口來獲取相應(yīng)的數(shù)據(jù)庫元數(shù)據(jù)。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的獲取元數(shù)據(jù)的代碼翠订。這里設(shè)置配置項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;
}
測試：
- 配置項如標(biāo)題二所示。我在automaticTestTable所配置的table bj_test中插入一條數(shù)據(jù)似谁。
發(fā)生的現(xiàn)象如下所示傲绣。在初始化datasource時，讀取配置并嘗試測試時發(fā)生錯誤巩踏。
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.
即使設(shè)置了test connection配置項，也會發(fā)生偶然的Exception塞琼。例如當(dāng)數(shù)據(jù)庫重啟發(fā)生在數(shù)據(jù)庫連接池已經(jīng)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();
}
