在这里记录对c3p0连接池管理的一些代码
一、连接池管理类
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.jdom.Document;
import org.jdom.Element;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.shine.framework.util.XMLUtil;
/**
* 数据库连接池管理器,基于C3P0<br/>
*
*/
public class ConnPoolManager {
private final static ConnPoolManager instance = new ConnPoolManager();
private Map<String, ComboPooledDataSource> pools = new HashMap<String, ComboPooledDataSource>();
private ConnPoolManager(){
}
public static ConnPoolManager getInstance(){
return instance;
}
/**
* 初始化连接池
* @param configPath 配置文件路径
*/
public void initial(String configPath){
Document configDoc = XMLUtil.file2Doc(configPath);
List<Element> res = configDoc.getRootElement().getChildren("Resource");
if(res!=null){
for(Element re:res){
String name = null;
try{
name = re.getAttributeValue("name");
String driverClassName = re.getAttributeValue("driverClassName");
String jdbcUrl = re.getAttributeValue("jdbcUrl");
String username = re.getAttributeValue("username");
String password = re.getAttributeValue("password");
String minPoolSize = re.getAttributeValue("minPoolSize");
String maxPoolSize = re.getAttributeValue("maxPoolSize");
String initialPoolSize = re.getAttributeValue("initialPoolSize");
String acquireIncrement = re.getAttributeValue("acquireIncrement");
String idleConnectionTestPeriod = re.getAttributeValue("idleConnectionTestPeriod");
String checkoutTimeout = re.getAttributeValue("checkoutTimeout");
String maxStatement = re.getAttributeValue("maxStatement");
String maxStatementsPerConnection = re.getAttributeValue("maxStatementsPerConnection");
String maxIdleTime = re.getAttributeValue("maxIdleTime");
String numHelperThreads = re.getAttributeValue("numHelperThreads");
ComboPooledDataSource pool = new ComboPooledDataSource();
pool.setDataSourceName(name); //数据源名称
pool.setDriverClass(driverClassName); //数据库驱动类名
pool.setJdbcUrl(jdbcUrl); //数据库连接url
pool.setUser(username); //数据库用户名
pool.setPassword(password); //数据库密码
pool.setMinPoolSize(string2int(minPoolSize, 2)); //最小连接数
pool.setInitialPoolSize(string2int(initialPoolSize, 5)); //初始化连接数
pool.setMaxPoolSize(string2int(maxPoolSize, 20)); //最大连接数
pool.setAcquireIncrement(string2int(acquireIncrement, 3)); //连接数的增量
pool.setIdleConnectionTestPeriod(string2int(idleConnectionTestPeriod, 120)); //检测空闲连接的时间间隔,单位为秒
pool.setCheckoutTimeout(string2int(checkoutTimeout, 3000)); //获取连接的超时时间,超时则抛出异常,单位为毫秒
pool.setMaxStatements(string2int(maxStatement, 0)); //最大statement数
pool.setMaxStatementsPerConnection(string2int(maxStatementsPerConnection, 0)); //单个连接所拥有的最大statement缓存数
pool.setMaxIdleTime(string2int(maxIdleTime, 300)); //最大空闲时间,该时间内未使用的连接将被丢弃,单位为秒
pool.setNumHelperThreads(string2int(numHelperThreads, 3)); //帮助线程个数,通过多线程同时执行SQL
pools.put(name, pool);
System.out.println("注册数据库连接池"+name);
}catch(Exception e){
System.out.println("初始化数据库连接池异常,name:"+(name!=null?name:"null"));
e.printStackTrace();
}
}
}
res = null;
configDoc = null;
}
/**
* 获取数据库连接
* @param name
* @return
* @throws SQLException
*/
public synchronized Connection getConnection(String name) throws SQLException{
ComboPooledDataSource pool = pools.get(name);
if(pool!=null){
return pool.getConnection();
}else{
System.out.println("不存在的数据源名称:" + name);
}
return null;
}
/**
* String转成int,如果异常则返回默认值
* @param value
* @param defaultValue
* @return
*/
private int string2int(String value,int defaultValue){
if(value==null)
return defaultValue;
try{
return Integer.parseInt(value);
}catch(NumberFormatException e){
System.out.println("String转成int时异常,value:"+value);
return defaultValue;
}
}
}
二、配置文件示例
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<Resource name="jdbc/nms"
driverClassName="com.mysql.jdbc.Driver"
username="root" password="root"
jdbcUrl="jdbc:mysql://127.0.0.1:3306/nms?useUnicode=true&characterEncoding=utf-8"
initialPoolSize="5" minPoolSize="3" maxPoolSize="20" acquireIncrement="3"
maxIdleTime="120" idleConnectionTestPeriod="120" checkoutTimeout="1000"/>
</c3p0-config>
三、main测试
public static void main(String[] args) {
ConnPoolManager.getInstance().initial("d:/dbConfig.xml");
Connection conn = ConnPoolManager.getInstance().getConnection("jdbc/nms");
}