使用c3p0开源的JDBC连接池小实例

使⽤c3p0开源的JDBC连接池⼩实例
C3P0是⼀个开源的JDBC连接池，它实现了数据源和JNDI绑定，⽀持JDBC3规范和JDBC2的标准扩展。

⽬前使⽤它的开源项⽬有Hibernate，Spring等。

C3P0 properties配置⽂件：
c3p0.DriverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
c3p0.JdbcUrl=jdbc:sqlserver://200.10.10.172:1433;DatabaseName=Test;selectMethod=cursor
er=sa
#er=root
c3p0.password=123
#c3p0.password=RHqlO9D2wCM=
#Number of Connections a pool will try to acquire upon startup.
c3p0.initialPoolSize=30
#Maximum number of Connections a pool will maintain at any given time.
c3p0.maxPoolSize=50
#Minimum number of Connections a pool will maintain at any given time.
c3p0.minPoolSize=20
#Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted.
c3p0.acquireIncrement=3
#If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds. c3p0.idleConnectionTestPeriod=60
#
c3p0.maxIdleTime=60
#Defines how many times c3p0 will try to acquire a new Connection from the database before giving up.
c3p0.acquireRetryAttempts=10
#Milliseconds, time c3p0 will wait between acquire attempts.
c3p0.acquireRetryDelay=1000
C3P0管理类，实现配置⽂件的读取，获取连接和关闭连接等功能：
package com.hodmct.db;
import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.log4j.Logger;
import boPooledDataSource;
public class ConnectionManager {
private static Logger log = Logger.getLogger(ConnectionManager.class);
private static String CONFIG_FILE_LOCATION = System.getProperty("user.dir") + "/config/db.properties";
private final ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
private String configFile = CONFIG_FILE_LOCATION;
private ComboPooledDataSource ds;
private static ConnectionManager instance;
public static ConnectionManager getInstance(String path)
{
if (instance == null)
{
if (path == null)
{
path = CONFIG_FILE_LOCATION;
}
instance = new ConnectionManager(path);
}
return instance;
}
public static ConnectionManager getInstance()
{
return getInstance(CONFIG_FILE_LOCATION);
}
private void init() {
Properties dbProps = new Properties();
try {
InputStream is = new FileInputStream(configFile);
dbProps.load(is);
("db config load success!");
} catch (Exception e) {
e.printStackTrace();
log.error("DB config load failed.");
throw new RuntimeException("DB config load failed.");
}
ds = new ComboPooledDataSource();
try {
ds.setDriverClass(dbProps.getProperty("c3p0.DriverClass").trim());
} catch (PropertyVetoException e1) {
throw new RuntimeException("com.sqlserver.jdbc.Driver加载失败");
}
// ds.setJdbcUrl("jdbc:mysql://127.0.0.1/mysession");
// ds.setUser("sessadmin");
// ds.setPassword("8877007");
log.error(dbProps.toString());
ds.setJdbcUrl(dbProps.getProperty("c3p0.JdbcUrl").trim());
ds.setUser(dbProps.getProperty("er").trim());
String password = dbProps.getProperty("c3p0.password").trim();
//password = UtilCommon.dec(password);
ds.setPassword(password);
//ds.setPassword(UtilCommon.dec(dbProps.getProperty("c3p0.password").trim()));
// 连接关闭时默认将所有未提交的操作回滚。

Default: false autoCommitOnClose
ds.setAutoCommitOnClose(true);
// 定义所有连接测试都执⾏的测试语句。

在使⽤连接测试的情况下这个⼀显著提⾼测试速度。

注意：
// 测试的表必须在初始数据源的时候就存在。

Default: null preferredTestQuery
ds.setPreferredTestQuery("select 1");
// 因性能消耗⼤请只在需要的时候使⽤它。

如果设为true那么在每个connection提交的
// 时候都将校验其有效性。

建议使⽤idleConnectionTestPeriod或automaticTestTable
// 等⽅法来提升连接测试的性能。

Default: false testConnectionOnCheckout
ds.setTestConnectionOnCheckout(false);
// 如果设为true那么在取得连接的同时将校验连接的有效性。

Default: false testConnectionOnCheckin
ds.setTestConnectionOnCheckin(false);
// 获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。

但是数据源仍有效
// 保留，并在下次调⽤getConnection()的时候继续尝试获取连接。

如果设为true，那么在尝试
// 获取连接失败后该数据源将申明已断开并永久关闭。

Default: false breakAfterAcquireFailure
ds.setBreakAfterAcquireFailure(false);
try {
// 初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。

Default: 3
// initialPoolSize
ds.setInitialPoolSize(Integer.parseInt(dbProps.getProperty("c3p0.initialPoolSize").trim()));
// ds.setInitialPoolSize(3);
// 连接池中保留的最⼤连接数。

Default: 15 maxPoolSize
ds.setMaxPoolSize(Integer.parseInt(dbProps.getProperty("c3p0.maxPoolSize").trim()));
// ds.setMaxPoolSize(10);
// 连接池中保留的最⼩连接数。

ds.setMinPoolSize(Integer.parseInt(dbProps.getProperty("c3p0.maxPoolSize").trim()));
// ds.setMinPoolSize(1);
// 当连接池中的连接耗尽的时候c3p0⼀次同时获取的连接数。

Default: 3 acquireIncrement
ds.setAcquireIncrement(Integer.parseInt(dbProps.getProperty("c3p0.acquireIncrement").trim()));
// ds.setAcquireIncrement(1);
// 每60秒检查所有连接池中的空闲连接。

Default: 0 idleConnectionTestPeriod
ds.setIdleConnectionTestPeriod(Integer.parseInt(dbProps.getProperty("c3p0.idleConnectionTestPeriod").trim()));
// ds.setIdleConnectionTestPeriod(60);
// 最⼤空闲时间,25000秒内未使⽤则连接被丢弃。

若为0则永不丢弃。

Default: 0 maxIdleTime
ds.setMaxIdleTime(Integer.parseInt(dbProps.getProperty("c3p0.maxIdleTime").trim()));
// ds.setMaxIdleTime(25000);
// 定义在从数据库获取新连接失败后重复尝试的次数。

Default: 30 acquireRetryAttempts
ds.setAcquireRetryAttempts(Integer.parseInt(dbProps.getProperty("c3p0.acquireRetryAttempts").trim()));
// ds.setAcquireRetryAttempts(30);
// 两次连接中间隔时间，单位毫秒。

Default: 1000 acquireRetryDelay
ds.setAcquireRetryDelay(Integer.parseInt(dbProps.getProperty("c3p0.acquireRetryDelay").trim()));
// ds.setAcquireRetryDelay(1000);
("db set config success!");
} catch (Exception e) {
log.error("oh, db set config failed!");
e.printStackTrace();
}
}
private ConnectionManager() {
init();
(threadLocal);
}
private ConnectionManager(String dbFilePath) {
configFile = dbFilePath;
(threadLocal);
init();
}
public Connection getConnection() {
Connection connection = threadLocal.get();
if (connection == null) {
try {
connection = ds.getConnection();
} catch (SQLException e) {
log.error(e.getMessage(), e);
}
threadLocal.set(connection);
}
return connection;
}
public void closeConnection() {
Connection connection = threadLocal.get();
try {
if (connection != null && !connection.isClosed()) {
connection.close();
threadLocal.set(null);
}
} catch (SQLException e) {
log.error(e.getMessage(), e);
}
}
}
测试效果：
import java.sql.Connection;
import org.junit.Test;
public class ConnectionManagerTest {
@Test
public void testGetConnection() throws Exception{
Connection con = ConnectionManager.getInstance().getConnection();
if(!con.isClosed() && con != null){
System.out.println("success....");
}
}
}。