c3p0 连接池配置

3 30 1000 false Test false 100 null false 60 3 60 15 100 3 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 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 amland samland Tran