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();
        }
    }
}