JDBC应用c3p0连接池,编写了一个工具类可以正常使用,但无法释放连接,连接全被占用后,就无法对数据库进行操作,怎么释放连接
慕容羽 2016-01-12 05:28:28
package com.gts.util;
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;
/**
* 定义jdbc的c3p0连接池模板类
* @author Administrator
*
*/
public class DBManager {
//定义相关属性
private Connection conn ;
private PreparedStatement pstmt ;
private static String dbDriver , dbUrl , dbUserName , dbPassWord ;
private static int initPoolSize , maxPoolSize , maxIdleTime ;
private static ComboPooledDataSource ds = null ;
private static DBManager instance = null;
//程序加载时,就从配置文件中读取数据库的连接信息,并赋值
static{
Properties prop = new Properties() ;
try {
InputStream in = DBManager.class.getClassLoader().getResourceAsStream("local_db.properties");
//加载配置文件
prop.load(in);
//读取数据库的连接信息
dbDriver = prop.getProperty("jdbc.driverClass") ;
dbUrl = prop.getProperty("jdbc.jdbcUrl") ;
dbUserName = prop.getProperty("jdbc.user") ;
dbPassWord = prop.getProperty("jdbc.password") ;
initPoolSize = Integer.parseInt(prop.getProperty("jdbc.initPoolSize")) ;
maxPoolSize = Integer.parseInt(prop.getProperty("jdbc.maxPoolSize")) ;
maxIdleTime = Integer.parseInt(prop.getProperty("jdbc.maxIdleTime")) ;
//新建连接池对象
ds = new ComboPooledDataSource();
ds.setDriverClass(dbDriver);
ds.setJdbcUrl(dbUrl);
ds.setUser(dbUserName);
ds.setPassword(dbPassWord);
//设置连接池初始化连接数目
ds.setInitialPoolSize(initPoolSize);
//设置连接池的最大连接数目
ds.setMaxPoolSize(maxPoolSize);
//当连接池中的连接耗尽的时候c3p0一次同时获取的连接数
ds.setAcquireIncrement(5);
//最大空闲时间,在设置时间内未使用则连接被丢弃,若为0则永不丢弃
ds.setMaxIdleTime(maxIdleTime);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (PropertyVetoException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//创建模板类对象
public static synchronized DBManager getInstance(){
if(instance == null){
instance = new DBManager();
}
return instance;
}
//打开数据库连接
public synchronized void openConnection(){
try {
this.conn = ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//更新方法
public synchronized int execUpdate(String sql , Object... params){
this.openConnection();
try {
//准备预编译的sql语句
this.pstmt = this.conn.prepareStatement(sql) ;
//参数赋值
for (int i = 0; i < params.length; i++) {
this.pstmt.setObject(i+1, params[i]);
}
return this.pstmt.executeUpdate() ;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return -1 ;
}finally{
this.closeConnection();
}
}
//查询方法
public synchronized ResultSet execQuery(String sql, Object... params){
this.openConnection();
try {
//准备预编译的SQL语句
this.pstmt = this.conn.prepareStatement(sql);
//参数赋值
for(int i=0; i<params.length; i++){
this.pstmt.setObject(i+1, params[i]);
}
return this.pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
//关闭数据库连接
public synchronized void closeConnection(){
if(this.pstmt!=null){
try {
this.pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(this.conn!=null){
try {
this.conn.close();
System.out.println("进入了关闭连接方法");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//关闭连接池
public synchronized void close(){
try{
DataSources.destroy(ds);
}catch (SQLException e){
e.printStackTrace();
}
}
}