c3p0-连接池配置.doc

3

30

1000

false

Test

false

ty name="checkoutTimeout">100

false

60

3

60

15

100

3

e="overrideDefaultUser">root

password

select id from test where id=1

300

false

true

root

false

acquireIncrement -> 5
acquireRetryAttempts -> 30
acquireRetryDelay -> 1000
autoCommitOnClose -> false
automaticTestTable -> null

breakAfterAcquireFailure -> true
checkoutTimeout -> 0
connectionCustomizerClassName -> null
connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester

dataSourceName -> 1bqp8hc8ezqxpjc1hp9gny|704baa
debugUnreturnedConnectionStackTraces -> false
description -> null
driverClass -> com.mysql.jdbc.Driver

factoryClassLocation -> null
forceIgnoreUnresolvedTransactions -> false
identityToken -> 1bqp8hc8ezqxpjc1hp9gny|704baa
idleConnectionTestPeriod -> 60
initialPoolSize -> 5
jdbcUrl -> jdbc:mysql://localhost:3306/db_temp?useUnicode=true&characterEncoding=utf-8
lastAcquisitionFailureDefaultUser -> null
maxAdministrativeTaskTime -> 0
maxConnectionAge -> 0
maxIdleTime -> 60
maxIdleTimeExcessConnections -> 0
maxPoolSize -> 30
maxStatements -> 0
maxStatementsPerConnection -> 0
minPoolSize -> 5
numHelperThreads -> 3
numThreadsAwaitingCheckoutDefaultUser -> 0
preferredTestQuery -> null

properties -> {user=******password=******}
propertyCycle -> 0
testConnectionOnCheckin -> false
testConnectionOnCheckout -> false
unreturnedConnectionTimeout -> 0
usesTraditionalReflectiveProxies -> false

package com.paipaiestore.database.c3p0;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.DataSources;
/**
* 数据连接池工厂
* @author samland
* @version 1.0 2006-11
*/
public class DBPoolFactory {

private static DBPoolFactory dbpoolFactory = new DBPoolFactory();
/**
* 连接池列表
*/
private static Map poollist = new HashMap();

private DBPoolFactory(){

}

public static DBPoolFactory getInstance(){
return dbpoolFactory;
}
/**
* 向连接池列表添加一个新的池化（Pooled）数据源
* @param key
* @param configFile 配置文件名。为了简化参数输入与便于外部维护，可以把配置好的文件传入这里分析
*/
public void add(String key, String configFile) {
//read config from file, and build a dbpool
}
/**
* 向连接池列表添加一个新的池化（Pooled）数据源
* @param key
* @param driver 数据连接驱动，如com.mysql.jdbc.Driver
* @param url 连接串， 如jdbc:mysql://localhost/samland
* @param username 数据库连接用户名，如samland
* @param password 数据库连接用户密码
* @throws Exception
*/
public void add(String key, String driver, String url, String username, String password)
throws Exception{
if (key==null) throw new Exception("DBPool 'key' CANNOT be null") ;

Class.forName(driver);
DataSource ds_unpooled = DataSources.unpooledDataSource(url, username, password);
DataSource ds_pooled = DataSources.pooledDataSource( ds_unpooled );
if (poollist.containsKey(key)){
poollist.remove(key);
}
poollist.put(key, ds_pooled);
}
/**
* 根据key字符串获取连接池
@param key
* @return DBPool 连接池
* @throws Exception
*/
public DBPool getDBPool(String key) throws Exception{
if (key==null) throw new Exception("DBPool 'key' CANNOT be null") ;
DataSource ds = (DataSource)poollist.get(key);
DBPool dbpool = new DBPool();
dbpool.setDs_pooled(ds);
return dbpool;
}

}
///////////////////////////////////////////////////////////////

四、连接池的使用
通过DBPool来获取数据库连接 （jdbc connection）。
4.1 由DBPoolFactory工厂产生一个DBPool
4.2 同时，DBPoolFactory工厂根据传入的参数key向这个DBPool注入数据源
4.3 从DBPool产生线程内的Connection
///////////////////////////////////////////////////////////////
package com.paipaiestore.database.c3p0;

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

import javax.sql.DataSource;

public class DBPool {
/**
* 数据库连接。同一个线程使用同一个连接。
*/
protected static ThreadLocal connection = new ThreadLocal();
/**
* 数据源，需由DBPoolFactory工厂注入
*/
private DataSource ds_pooled;

/**
* 不能独立构造，必须从DBPoolFactory工厂产生
*/
protected DBPool(){}

/**
* get 数据源
* @return
*/
public DataSource getDs_pooled() {
return ds_pooled;
}

/**
* 注入数据源
* @param ds_pooled
*/
public void setDs_pooled(DataSource ds_pooled) {
this.ds_pooled = ds_pooled;
}

/**
* 获得数据库连接
* 同一个线程只返回相同的一个连接。
* @return Connection
* @throws SQLException
*/
public Connection getConnection() throws SQLException{
Connection con = (Connection)connection.get();
if (con==null) {
con = ds_pooled.getConnection();
connection.set(con);
}
return con;
}
}
///////////////////////////////////////////////////////////////

五、改进线程内数据库连接的使用
为了适应程序已经使用开的jdbc事务管理，因此需要改进DBPool成为Transaction类，
而原来的DBPool则缩减为如下代码：
///////////////////////////////////////////////////////////////
public class DBPool {
private DataSource ds_pooled;
protected DBPool(){}
public DataSource getDs_pooled() {
return ds_pooled;
}
public void setDs_pooled(DataSource ds_pooled) {
this.ds_pooled = ds_pooled;
}
}
///////////////////////////////////////////////////////////////
package com.paipaiestore.database.c3p0;

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

import javax.sql.DataSource;

public class Transaction {
/**
* 数据库连接。同一个线程使用同一个连接。
*/
protected static ThreadLocal connection = new ThreadLocal();
protected static boolean isInUsed = false;
/**
* 开始一个事务
* 如果当前线程的数据库连接不存在，则会新获得一个数据库连接
* @param key
* @throws Exception
*/
public static void beginTransaction(String key) throws Exception{
Connection conn = (Connection)connection.get();
if (conn==null) {
conn = createConnection(key);
setConnection(conn);
}
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);
isInUsed = true;
}
/**
* 提交一个事务
* @throws SQLException
*/
public static void commitTransaction() throws SQLException {
Connection conn = (Connection)connection.get();
mit();
}
/**
* 回滚一个事务
* @throws SQLException
*/
public static void rollbackTransaction() throws SQLException {
Connection conn = (Connection)connection.get();
conn.rollback();
}
public static boolean getIsInUsed() {
return isInUsed;
}
private static void closeConnection() {
try {
Connection conn = (Connection)connection.get();
if (conn!=null) {
connection.set(null);
conn.close()
}
}catch(SQLException sqlexception) {

}
}
private static void close(ResultSet rs){
try
{
Statement st = rs.getStatement();
Connection conn = st.getConnection();
rs.close();
st.close();
conn.close();
}
catch(Exception exception) { }
}
/**
* 获得数据库连接
* 同一个线程只返回相同的一个连接。
* @return

* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
Connection conn = null;
conn = (Connection)connection.get();
if(conn == null)
throw new NullPointerException();
else
return conn;
}

private static void setConnection(Connection conn){
connection.set(conn);
}
/**
* 获得一个新的数据库连接
* @param key
* @return Connection
* @throws Exception
*/
private static Connection createConnection(String key)
throws Exception {
DBPoolFactory df = DBPoolFactory.getInstance();
DataSource ds_pooled = df.getDBPool(key).getDs_pooled() ;
Connection conn = ds_pooled.getConnection();
df = null;
return conn;
}
}
///////////////////////////////////////////////////////////////
六、测试
///////////////////////////////////////////////////////////////
package samland;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.paipaiestore.database.c3p0.DBPoolFactory;
import com.paipaiestore.database.c3p0.Transaction;

public class TestTransaction {

/**
* @param args
*/
public static void main(String[] args) {
DBPoolFactory df = DBPoolFactory.getInstance();

Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
df.add("jxc","com.mysql.jdbc.Driver","jdbc:mysql://

Transaction.beginTransaction("samland");
con = Transaction.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT * FROM users");
while (rs.next())
{
System.out.print( rs.getString(1) );
System.out.print( rs.getString(2) );
System.out.println( rs.getString(3) );
}
TmitTransaction();
df = null;
} catch (Exception e) {
e.printStackTrace();
}
}
}