C3P0连接池----Resin

C3P0.xml 连接池配置文件：
<!--
author：Gavin.lee
date：2009-12-10 20:46:50
c3p0连接池配置文件，这里目前配置了2个MSSQ库的连接池，设置了主要的参数
c3p0 api 手册 http://www.mchange.com/projects/c3p0/apidocs/index.html
-->
<root>
<cp-sqlserver>
<user>wap</user>
<password>esunxyz500wan!^wap</password>
<url>jdbc:jtds:sqlserver://serverIP:port/wubai_wapcp</url>
<driver>net.sourceforge.jtds.jdbc.Driver</driver>
<maxPoolSize>100</maxPoolSize>
<minPoolSize>5</minPoolSize>
<initialPoolSize>20</initialPoolSize>
<maxStatements>100</maxStatements>
<maxIdleTime>600</maxIdleTime>
</cp-sqlserver>

<new-sqlserver>
<user>wap</user>
<password>esunxyz500wan!^wap</password>
<url>jdbc:jtds:sqlserver://serverIP:port/wubai_wapnew</url>
<driver>net.sourceforge.jtds.jdbc.Driver</driver>
<maxPoolSize>100</maxPoolSize>
<minPoolSize>5</minPoolSize>
<initialPoolSize>20</initialPoolSize>
<maxStatements>100</maxStatements>
<maxIdleTime>600</maxIdleTime>
</new-sqlserver>

<cp-mysql></cp-mysql>
</root>


初始化新闻库连接池；
package yixun.wap.db;

import java.beans.PropertyVetoException;
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.SQLException;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import yixun.wap.tools.BaseConfig;
import com.mchange.v2.c3p0.ComboPooledDataSource;

/** *//**
* new-sqlserver 库的连接池单例
* @author 显武
* @date 2009-12-10 20:42:30
*/
public class NewPoolSource {
private static NewPoolSource dbPool;
private ComboPooledDataSource dataSource;

/** *//**
* 获取池
* @return
*/
public final static NewPoolSource getInstance() {
return dbPool;
}

public final ComboPooledDataSource getDataSource() {
return dataSource;
}

public final int getNumConnections() {
int num = 0;
try {
num = dataSource.getNumConnections();
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}

/** *//**
* 获取连接
* @return
*/
public final Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException("获取连接失败", e);
}
}

private static String user;
private static String password;
private static String url;
private static String driver;
private static String initialPoolSize;
private static String maxPoolSize;
private static String minPoolSize;
private static String maxStatements;
private static String maxIdleTime;

static {
SAXReader reader = new SAXReader();
try {
Document doc = reader.read(Thread.currentThread().getContextClassLoader().getResource("") + BaseConfig.getBaseconfigpath() + "c3p0.xml");
// System.out.println(Thread.currentThread().getContextClassLoader().getResource("") + BaseConfig.getBaseconfigpath() + "c3p0.xml");
// file:/I:/resin/deploy/wap.500wan.com/WEB-INF/classes/yixun/wap/config/local/c3p0.xml
Element root = doc.getRootElement();
Element elements = root.element("new-sqlserver");
user = elements.elementText("user");
password = elements.elementText("password");
url = elements.elementText("url");
driver = elements.elementText("driver");
initialPoolSize = elements.elementText("initialPoolSize");
maxPoolSize = elements.elementText("maxPoolSize");
minPoolSize = elements.elementText("minPoolSize");
maxStatements = elements.elementText("maxStatements");
maxIdleTime = elements.elementText("maxIdleTime");

} catch (DocumentException e) {
e.printStackTrace();
}
dbPool = new NewPoolSource();
}

private NewPoolSource() {
try {
dataSource = new ComboPooledDataSource();
dataSource.setUser(user);
dataSource.setPassword(password);
dataSource.setJdbcUrl(url);
dataSource.setDriverClass(driver);
dataSource.setInitialPoolSize(Integer.valueOf(initialPoolSize));
dataSource.setMinPoolSize(Integer.valueOf(minPoolSize));
dataSource.setMaxPoolSize(Integer.valueOf(maxPoolSize));
dataSource.setMaxStatements(Integer.valueOf(maxStatements));
dataSource.setMaxIdleTime(Integer.valueOf(maxIdleTime)); //6分钟

} catch (PropertyVetoException e) {
throw new RuntimeException(e);
}
}
}
利用连接池数据源对数据库进行操作工具类：
package yixun.wap.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
//import java.sql.Types;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/** *//**
*
* @author 显武
* @date 2009-12-4 22:32:52
*
*/
public class DBUtil {
private Connection conn = null;

private PreparedStatement prepStmt = null;

private ResultSet rs = null;

public CallableStatement cstmt;

public String poolName = "";

/** *//**
* 执行SQL
* @param poolName
* @param sql
* @throws SQLException
*/
public DBUtil(Connection conn, String sql) throws SQLException {
this.conn = conn;
prepStmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
}

public DBUtil(ComboPooledDataSource ds, String sql) throws SQLException {
conn = ds.getConnection();
prepStmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
}

public DBUtil(DataSource ds, String sql) throws SQLException {
conn = ds.getConnection();
prepStmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
}

public DBUtil(DataSource ds) throws SQLException {
conn = ds.getConnection();
}

public DBUtil(ComboPooledDataSource ds) throws SQLException {
conn = ds.getConnection();
}

public Connection getConnection() {
return conn;
}

public PreparedStatement getPreparedStatement() {
return prepStmt;
}

public ResultSet executeQuery() throws SQLException {
return this.prepStmt.executeQuery();
}

public boolean execute() throws SQLException {
return this.prepStmt.execute();
}

public int executeUpdate() throws SQLException {
return this.prepStmt.executeUpdate();
}

/** *//**
* 释放连接
*
*/
public void destory() {
try {
if (this.prepStmt != null) {
this.prepStmt.close();
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}

/** *//**
* 有返回值的存储过程执行
* @param procName
* @param params
* @return
* @throws SQLException
*/
public ResultSet executeQueryProcedure(String procName, Object[] params)
throws SQLException {
int index = 0;
cstmt = conn.prepareCall(procName);
for (Object obj : params) {
index++;
cstmt.setObject(index, obj);
}
//cstmt.registerOutParameter(3, Types.INTEGER);
rs = cstmt.executeQuery();

return rs;
}

/** *//**
* 返回boolean的存储过程
* @param procName
* @param params
* @return
* @throws SQLException
*/
public boolean executeProcedure(String procName, Object[] params)
throws SQLException {
boolean flag = false;
int index = 0;
cstmt = conn.prepareCall(procName);
for (Object obj : params) {
index++;
cstmt.setObject(index, obj == null ? "" : obj);
}
flag = cstmt.execute();
return flag;
}

public void setString(int index, String value) throws SQLException {
prepStmt.setString(index, value);
}

public void setInt(int index, int value) throws SQLException {
prepStmt.setInt(index, value);
}

public void setBoolean(int index, boolean value) throws SQLException {
prepStmt.setBoolean(index, value);
}

public void setDate(int index, Date value) throws SQLException {
prepStmt.setDate(index, value);
}

public void setLong(int index, long value) throws SQLException {
prepStmt.setLong(index, value);
}

public void setFloat(int index, float value) throws SQLException {
prepStmt.setFloat(index, value);
}

public void setDouble(int index, double value) throws SQLException {
prepStmt.setDouble(index, value);
}

public void setObject(int index, Object obj) throws SQLException {
prepStmt.setObject(index, obj);
}

public void commit() {
try {
conn.commit();
} catch (Exception e) {
e.printStackTrace();
}
}

public void rollback() {
try {
conn.rollback();
} catch (Exception e) {
e.printStackTrace();
}
}

}

应用：
package yixun.wap.news.news.number;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import yixun.wap.db.DBUtil;
import yixun.wap.db.NewPoolSource;
import yixun.wap.news.news.New;

public class NumberOperate {

private DataSource ds = null;
public NumberOperate() {
ds = NewPoolSource.getInstance().getDataSource();
}

public List<New> getNumberRecommend(String itemid, String titleNum) {
List<New> recommentlist = null;
DBUtil dbBean = null;
String sql = null;
ResultSet rs = null;
try {
int articleNum = Integer.parseInt(titleNum);
recommentlist = new ArrayList<New>();
for(int i = 1; i <= articleNum; i++) {
sql = "select top 1 c_id,c_title from w_content where c_ishome=1 and c_istop="+i+" and c_bid in (select i_id from w_index where i_bid in (select i_id from w_index where i_bid ='"+itemid+"')) order by c_data desc";
dbBean = new DBUtil(ds, sql);
rs = dbBean.executeQuery();
if(rs.next()){
New news = new New();
news.setTitleid(rs.getString("c_id"));
news.setTitle(rs.getString("c_title"));

recommentlist.add(news);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
dbBean.destory();
}
return recommentlist;
}
}

调用存储过程：
public New getContent(String titleid, String pages, String pagesize, String picpage) {
New pagecontent = null;
if(picpage == null || picpage.trim().equals("")) {
picpage = "1";
}
DBUtil dbBean = null;
Object[] ob = new Object[4];
ob[0] = titleid;
ob[1] = pages;
ob[2] = pagesize;
ob[3] = picpage;

String procName = "{call wap_biz_getContent(?,?,?,?)}";
ResultSet rs = null;
try {
dbBean = new DBUtil(ds);
rs = dbBean.executeQueryProcedure(procName, ob);
if(rs.next()) {
pagecontent = new New();
pagecontent.setTitle(rs.getString("title"));
pagecontent.setContent(rs.getString("content"));
pagecontent.setTotalView(rs.getString("click"));
pagecontent.setTotalComment(rs.getString("comment"));
pagecontent.setPageCount(rs.getString("pageCount"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
dbBean.destory();
}
return pagecontent;
}

以下是连接池初始化时的记录日志：
- MLog clients using log4j logging.
- Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
- Initializing c3p0 pool com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hge0ww8510fz9rlfl7a1o|1446579, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> net.sourceforge.jtds.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hge0ww8510fz9rlfl7a1o|1446579, idleConnectionTestPeriod -> 0, initialPoolSize -> 20, jdbcUrl -> jdbc:jtds:sqlserver://serverIP:port/wubai_wapnew, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 600, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 100, maxStatements -> 100, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
-
附：摘了网上一段连接池配置属性的意义，帮助理解
<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
<property name="acquireIncrement">3</property>

<!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
<property name="acquireRetryAttempts">30</property>

<!--两次连接中间隔时间，单位毫秒。Default: 1000 -->
<property name="acquireRetryDelay">1000</property>

<!--连接关闭时默认将所有未提交的操作回滚。Default: false -->
<property name="autoCommitOnClose">false</property>

<!--c3p0将建一张名为Test的空表，并使用其自带的查询语句进行测试。如果定义了这个参数那么
属性preferredTestQuery将被忽略。你不能在这张Test表上进行任何操作，它将只供c3p0测试
使用。Default: null-->
<property name="automaticTestTable">Test</property>

<!--获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效
保留，并在下次调用getConnection()的时候继续尝试获取连接。如果设为true，那么在尝试
获取连接失败后该数据源将申明已断开并永久关闭。Default: false-->
<property name="breakAfterAcquireFailure">false</property>

<!--当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出
SQLException,如设为0则无限期等待。单位毫秒。Default: 0 -->
<property name="checkoutTimeout">100</property>

<!--通过实现ConnectionTester或QueryConnectionTester的类来测试连接。类名需制定全路径。
Default: com.mchange.v2.c3p0.impl.DefaultConnectionTester-->
<property name="connectionTesterClassName"></property>

<!--指定c3p0 libraries的路径，如果（通常都是这样）在本地即可获得那么无需设置，默认null即可
Default: null-->
<property name="factoryClassLocation">null</property>

<!--Strongly disrecommended. Setting this to true may lead to subtle and bizarre bugs.
（文档原文）作者强烈建议不使用的一个属性-->
<property name="forceIgnoreUnresolvedTransactions">
false
</property>

<!--每60秒检查所有连接池中的空闲连接。Default: 0 -->
<property name="idleConnectionTestPeriod">60</property>

<!--初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
<property name="initialPoolSize">3</property>

<!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
<property name="maxIdleTime">60</property>

<!--连接池中保留的最大连接数。Default: 15 -->
<property name="maxPoolSize">15</property>

<!--JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements
属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。
如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0-->
<property name="maxStatements">100</property>

<!--maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 -->
<property name="maxStatementsPerConnection"></property>

<!--c3p0是异步操作的，缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能
通过多线程实现多个操作同时被执行。Default: 3-->
<property name="numHelperThreads">3</property>

<!--当用户调用getConnection()时使root用户成为去获取连接的用户。主要用于连接池连接非c3p0
的数据源时。Default: null-->
<property name="overrideDefaultUser">root</property>

<!--与overrideDefaultUser参数对应使用的一个参数。Default: null-->
<property name="overrideDefaultPassword">password</property>

<!--密码。Default: null-->
<property name="password"></property>

<!--定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个一显著提高测试速度。注意：
测试的表必须在初始数据源的时候就存在。Default: null-->
<property name="preferredTestQuery">
select id from test where id=1
</property>

<!--用户修改系统配置参数执行前最多等待300秒。Default: 300 -->
<property name="propertyCycle">300</property>

<!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的
时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable
等方法来提升连接测试的性能。Default: false -->
<property name="testConnectionOnCheckout">false</property>

<!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false -->
<property name="testConnectionOnCheckin">true</property>

<!--用户名。Default: null-->
<property name="user">root</property>

<!--早期的c3p0版本对JDBC接口采用动态反射代理。在早期版本用途广泛的情况下这个参数
允许用户恢复到动态反射代理以解决不稳定的故障。最新的非反射代理更快并且已经开始
广泛的被使用，所以这个参数未必有用。现在原先的动态反射与新的非反射代理同时受到
支持，但今后可能的版本可能不支持动态反射代理。Default: false-->
<property name="usesTraditionalReflectiveProxies">
false
</property>
下面这个比较容易理解，但是不推荐使用：
package yixun.wap.db;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import yixun.wap.tools.BaseConfig;
import com.mchange.v2.c3p0.DataSources;
import com.mchange.v2.c3p0.PoolConfig;

/** *//**
* wubai_wapcp 库的连接池单例
* @author 显武
* @date 2009-12-10 17:45:57
*/
public class CPPoolSource {
private static CPPoolSource dbPool;
private PoolConfig poolConfig;
private DataSource dataSource;

/** *//**
* 获取池
* @return
*/
public final static CPPoolSource getInstance() {
return dbPool;
}

/** *//**
* 获取连接
* @return
*/
public final Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException("获取连接失败", e);
}
}

public final DataSource getDataSource() {
return dataSource;
}

private static String user;
private static String password;
private static String url;
private static String driver;
private static String initialPoolSize;
private static String maxPoolSize;
private static String minPoolSize;
private static String maxStatements;
private static String maxIdleTime;

static {
SAXReader reader = new SAXReader();
try {
Document doc = reader.read(Thread.currentThread().getContextClassLoader().getResource("") + BaseConfig.getBaseconfigpath() + "c3p0.xml");
// System.out.println(Thread.currentThread().getContextClassLoader().getResource("") + BaseConfig.getBaseconfigpath() + "c3p0.xml");
// file:/I:/resin/deploy/wap.500wan.com/WEB-INF/classes/yixun/wap/config/local/c3p0.xml
Element root = doc.getRootElement();
Element elements = root.element("cp-sqlserver");
user = elements.elementText("user");
password = elements.elementText("password");
url = elements.elementText("url");
driver = elements.elementText("driver");
initialPoolSize = elements.elementText("initialPoolSize");
maxPoolSize = elements.elementText("maxPoolSize");
minPoolSize = elements.elementText("minPoolSize");
maxStatements = elements.elementText("maxStatements");
maxIdleTime = elements.elementText("maxIdleTime");

} catch (DocumentException e) {
e.printStackTrace();
}

dbPool = new CPPoolSource();
}

@SuppressWarnings("deprecation")
private CPPoolSource() {
try {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
DataSource unpooled = DataSources.unpooledDataSource(url, user, password);
poolConfig.setInitialPoolSize(Integer.valueOf(initialPoolSize));
poolConfig.setMinPoolSize(Integer.valueOf(minPoolSize));
poolConfig.setMaxPoolSize(Integer.valueOf(maxPoolSize));
poolConfig.setMaxStatements(Integer.valueOf(maxStatements));
poolConfig.setMaxIdleTime(Integer.valueOf(maxIdleTime)); //6分钟

dataSource = DataSources.pooledDataSource(unpooled, poolConfig);

} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}