c3p0连接池

在这里记录对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");
}