c3p0 連接池配置參考模板

1、3301000falseTestfalse100nullfalse602 / 16360151003rootpasswordselect id from test where id=1300falsetruerootfalseacquireIncrement - 5 acquireRetryAttempts - 30 acquireRetryDelay - 1000 autoCommitOnClose - false automaticTestTable - null breakAfterAcquireFailure - true checkoutTimeout - 0 connectionC

2、ustomizerClassName - null connectionTesterClassName - com.mchange.v2.c3p0.impl.DefaultConnectionTester dataSourceName - 1bqp8hc8ezqxpjc1hp9gny|704baa debugUnreturnedConnectionStackTraces - false description - null driverClass - com.mysql.jdbc.Driver factoryClassLocation - null forceIgnoreUnresolvedT

3、ransactions - 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 maxIdle

4、Time - 60 maxIdleTimeExcessConnections - 0 maxPoolSize - 30 maxStatements - 0 maxStatementsPerConnection - 0 minPoolSize - 5 numHelperThreads - 3 numThreadsAwaitingCheckoutDefaultUser - 0 preferredTestQuery - null properties - user=*password=* propertyCycle - 0 testConnectionOnCheckin - false testCo

5、nnectionOnCheckout - false unreturnedConnectionTimeout - 0 usesTraditionalReflectiveProxies - falsepackage 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;/*

6、 數(shù)據(jù)連接池工廠(chǎng)* 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;/* 向連接池列表添加一個(gè)新的池化（Poo

7、led）數(shù)據(jù)源* param key* param configFile 配置文件名。為了簡(jiǎn)化參數(shù)輸入與便于外部維護(hù)，可以把配置好的文件傳入這里分析*/public void add(String key, String configFile) /read config from file, and build a dbpool/* 向連接池列表添加一個(gè)新的池化（Pooled）數(shù)據(jù)源* param key* param driver 數(shù)據(jù)連接驅(qū)動(dòng)，如com.mysql.jdbc.Driver* param url 連接串， 如jdbc:mysql:/localhost/samland* par

8、am username 數(shù)據(jù)庫(kù)連接用戶(hù)名，如samland* param password 數(shù)據(jù)庫(kù)連接用戶(hù)密碼* throws Exception*/public void add(String key, String driver, String url, String username, String password)throws Exceptionif (key=null) throw new Exception(DBPool key CANNOT be null) ;Class.forName(driver);DataSource ds_unpooled = DataSources.

9、unpooledDataSource(url, username, password);DataSource ds_pooled = DataSources.pooledDataSource( ds_unpooled );if (poollist.containsKey(key)poollist.remove(key);poollist.put(key, ds_pooled);/* 根據(jù)key字符串獲取連接池* param key* return DBPool 連接池* throws Exception*/public DBPool getDBPool(String key) throws E

10、xceptionif (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;/四、連接池的使用通過(guò)DBPool來(lái)獲取數(shù)據(jù)庫(kù)連接 （jdbc connection）。4.1 由DBPoolFactory工廠(chǎng)產(chǎn)生一個(gè)DBPool4.2 同時(shí)，DBPoolFactory工廠(chǎng)根據(jù)傳入的參數(shù)key向這個(gè)DBPool注入

11、數(shù)據(jù)源4.3 從DBPool產(chǎn)生線(xiàn)程內(nèi)的Connection/package com.paipaiestore.database.c3p0;import java.sql.Connection;import java.sql.SQLException;import javax.sql.DataSource;public class DBPool /* 數(shù)據(jù)庫(kù)連接。同一個(gè)線(xiàn)程使用同一個(gè)連接。*/protected static ThreadLocal connection = new ThreadLocal();/* 數(shù)據(jù)源，需由DBPoolFactory工廠(chǎng)注入*/private DataSo

12、urce ds_pooled;/* 不能獨(dú)立構(gòu)造，必須從DBPoolFactory工廠(chǎng)產(chǎn)生*/protected DBPool()/* get 數(shù)據(jù)源* return*/public DataSource getDs_pooled() return ds_pooled;/* 注入數(shù)據(jù)源* param ds_pooled*/public void setDs_pooled(DataSource ds_pooled) this.ds_pooled = ds_pooled;/* 獲得數(shù)據(jù)庫(kù)連接* 同一個(gè)線(xiàn)程只返回相同的一個(gè)連接。* return Connection* throws SQLExcep

13、tion*/public Connection getConnection() throws SQLExceptionConnection con = (Connection)connection.get();if (con=null) con = ds_pooled.getConnection();connection.set(con);return con;/五、改進(jìn)線(xiàn)程內(nèi)數(shù)據(jù)庫(kù)連接的使用為了適應(yīng)程序已經(jīng)使用開(kāi)的jdbc事務(wù)管理，因此需要改進(jìn)DBPool成為T(mén)ransaction類(lèi)，而原來(lái)的DBPool則縮減為如下代碼：/public class DBPool private DataSo

14、urce 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.State

15、ment;import javax.sql.DataSource;public class Transaction /* 數(shù)據(jù)庫(kù)連接。同一個(gè)線(xiàn)程使用同一個(gè)連接。*/protected static ThreadLocal connection = new ThreadLocal();protected static boolean isInUsed = false;/* 開(kāi)始一個(gè)事務(wù)* 如果當(dāng)前線(xiàn)程的數(shù)據(jù)庫(kù)連接不存在，則會(huì)新獲得一個(gè)數(shù)據(jù)庫(kù)連接* param key* throws Exception*/public static void beginTransaction(String key

16、) throws ExceptionConnection conn = (Connection)connection.get();if (conn=null) conn = createConnection(key);setConnection(conn);conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);conn.setAutoCommit(false);isInUsed = true;/* 提交一個(gè)事務(wù)* throws SQLException*/public static void commitTran

17、saction() throws SQLException Connection conn = (Connection)connection.get();mit();/* 回滾一個(gè)事務(wù)* throws SQLException*/public static void rollbackTransaction() throws SQLException Connection conn = (Connection)connection.get();conn.rollback();public static boolean getIsInUsed() return isInUsed;private s

18、tatic 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)tryStatement st = rs.getStatement();Connection conn = st.getConnection();rs.close();st.close();con

19、n.close();catch(Exception exception) /* 獲得數(shù)據(jù)庫(kù)連接* 同一個(gè)線(xiàn)程只返回相同的一個(gè)連接。* return* throws SQLException*/public static Connection getConnection() throws SQLException Connection conn = null;conn = (Connection)connection.get();if(conn = null)throw new NullPointerException();elsereturn conn;private static void

20、setConnection(Connection conn)connection.set(conn);/* 獲得一個(gè)新的數(shù)據(jù)庫(kù)連接* 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;/六、測(cè)試/package samland;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement