c3p0连接池

最近项目中使用了c3p0作为连接池，遇到了mysql八小时连接废弃的问题，下面展示一下我的解决方案

configuration.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration     
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"     
    "http://mybatis.org/dtd/mybatis-3-config.dtd">  
<configuration>  
	<typeAliases>  
   
    </typeAliases> 
     <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="C3P0DataSourceFactory">
               <property name="driverClass" value="com.mysql.jdbc.Driver" />
			   <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull" />
			   <property name="user" value="root" />
			   <property name="password" value="123" />
			   <property name="minPoolSize" value="5" />
			   <!--连接池中保留的最大连接数。Default: 15 --> 
			   <property name="maxPoolSize" value="20"/>  
			   <!--初始化时获取十个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3 --> 
			   <property name="initialPoolSize" value="10"/>
			   <!--最大空闲时间,36000秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 --> 
			   <property name="maxIdleTime" value="25000"/>
			   <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
			   <property name="acquireIncrement" value="3"/>
			   <!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
			   <property name="acquireRetryAttempts" value="30"/>
			   <!--两次连接中间隔时间，单位毫秒。Default: 1000 -->
			   <property name="acquireRetryDelay" value="1000"/>
			  
			   <!--每18000秒检查所有连接池中的空闲连接。Default: 0 -->
			   <property name="idleConnectionTestPeriod" value="3600"/>
			   <!--当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出
				 SQLException,如设为0则无限期等待。单位毫秒。Default: 0 -->
			   <property name="checkoutTimeout" value="3000"/>  
			   <property name="testConnectionOnCheckout" value="false" />
			   <!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false -->
			   <property name="testConnectionOnCheckin" value="true"/>
			   <property name="automaticTestTable" value="c3p0testtable"/>
			   <property name="connectionTesterClassName" value="C3p0ConnectTest"/> 
			   
            </dataSource>
        </environment>
    </environments>
    <mappers>  

    </mappers>  
</configuration>   

下面是用的到的类

C3P0DataSourceFactory.java

import java.util.Properties;

import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0DataSourceFactory extends UnpooledDataSourceFactory {
	public C3P0DataSourceFactory() {
		this.dataSource = new ComboPooledDataSource();
	}

	@Override
	public void setProperties(Properties arg0) {
		super.setProperties(arg0);
	}
	
}






C3p0ConnectTest.java



import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import com.mchange.v2.c3p0.impl.DefaultConnectionTester;
/**
 * c3p0测试类
 * @author siyunlong
 * @version 1.0
 * @date 2013-8-20下午6:16:58
 *
 */
public class C3p0ConnectTest extends DefaultConnectionTester{

	@Override
	public int activeCheckConnection(Connection connection, String sql,
			Throwable[] arg2) {
		try {
			Statement s = connection.createStatement();
			s.execute(sql);
		} catch (SQLException e) {
			System.out.println("测试connection有效性异常");
			e.printStackTrace();
		}
		
		return 0;
	}
}