java C/S客户端实现数据库连接池

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import IPNMS.AppMain;

/**
* 配置数据库连接 driver url user password
*
* @author llt
*
*/


public class DBConnection {

/**
* DBConnectin instance 定义了数据库的唯一实例 定义了四个连接 conn,conn1,conn2,conn3
*
*/
private static DBConnection instance;

private final static int databaseConnectionNum = 15;
private final static int maxConnectNum = 100;
private static int checkOut = 0;//已用连接
private static ArrayList<Connection> connectlist = new ArrayList<Connection>();
private static ArrayList<Statement> stmtList = new ArrayList<Statement>();

public static void createDBConnectionPool(){
for (int i = connectlist.size(); i < databaseConnectionNum; i++) {
instance = new DBConnection();
Connection connobject=null;
try {
connobject = instance.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
connectlist.add(connobject);
}
}
public static void closeDBConnectionPool(){
Iterator dbConnectionIterator = connectlist.iterator();
// Iterator stmtIterator = stmtList.iterator();
while(dbConnectionIterator.hasNext()){
Connection connection = (Connection) dbConnectionIterator.next();

try {
if(connection!=null){
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// while(stmtIterator.hasNext()){
// Statement stmtobj = (Statement) stmtIterator.next();
// try {
// dealClose(stmtobj,null);
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// }
}

public DBConnection() {
super();
}

// public static void setText(String text) {
// DBConnection.text = text;
// }

public static Connection getConnection() throws SQLException {

// // 读取数据库配置信息（设计内部软件信息 略）



return DriverManager.getConnection(url, uNameEnyet, uPwdEnyet);

}

private static Connection getInstance() throws SQLException {

if (instance == null) {
instance = new DBConnection();
}
return instance.getConnection();
}

/*
//处理开始事物

public static void beginTrans() throws SQLException{
Statement stmt = null;
boolean autoCommit;
try
{
autoCommit = getInstance().getAutoCommit();
getInstance().setAutoCommit(false);
}
catch(SQLException ex)
{
ex.printStackTrace();
System.out.print("beginTrans Errors");
throw ex;
}
}

//处理更新sql

public static void executeUpdate(String sql) throws SQLException {

Statement stmt = null;
try
{
stmt=getInstance().createStatement();
stmt.executeUpdate(sql);
}
catch(SQLException ex)
{
ex.printStackTrace();
System.out.println("conndb.executeUpdate:"+ex.getMessage());
throw ex;
}
}


//处理提交事物

public static void commit() throws SQLException {
boolean autoCommit = false;
try
{
getInstance().commit();
getInstance().setAutoCommit(autoCommit);
}
catch(SQLException ex)
{
ex.printStackTrace();
System.out.print("Commit Errors");
throw ex;
}
}

// 处理回滚事物

public static void rollback(){
Statement stmt = null;
boolean autoCommit = false;
try
{
getInstance().rollback();
getInstance().setAutoCommit(autoCommit);
}
catch(SQLException ex)
{
ex.printStackTrace();
System.out.print("Rollback Errors");
}
}*/

private static Connection getFreeConnection(){
int stmtNum = stmtList.size();//获取statement对象的个数
Connection stmtConn=null;
if (connectlist.size() > 0) {
// 获取向量中第一个可用连接
stmtConn = (Connection) connectlist.get(0);
connectlist.remove(0);
try {
if (stmtConn.isClosed()) {
// 递归调用自己,尝试再次获取可用连接
stmtConn = getFreeConnection();
}
} catch (SQLException e) {
// 递归调用自己,尝试再次获取可用连接
stmtConn = getFreeConnection();
}
} else if (checkOut < maxConnectNum) {
instance = new DBConnection();
Connection connobject=null;
try {
stmtConn = instance.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// con = newConnection();
}
if (stmtConn != null) {
checkOut++;
}
return stmtConn;
}

// 得到一个会话

public static Statement getStatement() {

Statement stmt = null;

Connection stmtConn = getFreeConnection();
try {
stmt = stmtConn.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return stmt;

}

// 处理提取查询，返回结果集
public static ResultSet dealQuery(String sql, Statement stmt) {
ResultSet rs = null;
try {
rs = stmt.executeQuery(sql);

} catch (SQLException e) {
e.printStackTrace();
return null;
}
return rs;
}

/**·
* 使用给定连接执行查询操作
* @param sql 执行语句
* @param conn 连接
* @return
* @throws SQLException
*/
public static ResultSet dealQuery(String sql,Connection conn) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
try{
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
}finally{
// if(stmt!= null&& stmt.isClosed() == false){
// stmt.close();
// }
}
return rs;
}

// 处理数据更新
public static int dealUpdate(String sql, Statement stmt) {
try {
return stmt.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
return -1;
}
}

/**
* 使用给定连接来处理更新
* @param sql 执行语句
* @param conn 连接
* @return
* @throws SQLException
*/
public static int dealUpdate(String sql,Connection conn) throws SQLException {
Statement stmt = null;
int check =-1;
try{
stmt = conn.createStatement();
check = stmt.executeUpdate(sql);
}finally{
// if(stmt!= null&& stmt.isClosed() == false){
// stmt.close();
// }
}
return check;
}

// 关闭会话连接
public static void dealClose(Statement stmt, ResultSet rs) throws SQLException {
Connection connObj = stmt.getConnection();
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();

if(checkOut > databaseConnectionNum){//当statement对象数大于数据库连接数的时候，关闭一个statement对象的同时关闭一个连接

if(connObj!=null&&(connObj.isClosed()==false)){
connObj.close();
checkOut--;
}

}else{
connectlist.add(connObj);
checkOut--;
}
}

public static void dealClose(Connection conn,Statement stmt, ResultSet rs) throws SQLException {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
// stmt.getConnection().close();
if(conn!= null && (conn.isClosed()==false)){
conn.close();
}
}
}