Java 数据库连接池 实现

        写项目每次用到数据库不可避免，但是项目越大，项目模块中对数据库的链接就越多，这也就对系统造成了问题，反应慢，开销大，还容易崩溃。一直想写个可以复用的连接池，所以查阅书籍，写了个不成形的。当然，要实现，就必须根据原理来，为了文章简洁，重要的就关键点捎带。写这个就要有用，不然意义何在。这是百度百科对数据库连接池的简要讲解：http://baike.baidu.com/view/84055.htm?fr=aladdin

       为什么写？

       第一：内存管理，也就是对象和实例的管理，连接从建立之初，就在掌控当中，直到死亡，准确回收，节省资源，节省内存；

       第二个是可维护性，统一管理所有连接，一旦那个连接不用，立即释放，新建连接还可以直接从已释放连接取；

       第三，安全性，如果是零散的连接，不仅维护不容易，而且安全是大问题，只要涉及到并发，对系统也造成不安全因素

个人实现的部分功能：

      实现点：

     连接池只能有一个，连接池中连接数量有最小最大值，最大值还受数据库所能允许的最大值限制，连接类型不定（MySQL或者Oracle等）

     每个连接有名称，根据名称可以得到连接，连接存放在线程安全的HashTable中，Vector也是线程安全的

     定时释放无用连接，刷新连接，设置最大连接量，设置繁忙状态标志灯，为了便于区分，自动完成的方法名首字母大写

     这个类是连接池相关的类，另外一个类是冲xml文件读取账号等信息。

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Vector;
/*
 * Copy right By Gongxuesong
 * this is a auto DataBase Pool
 * it can improve to reduce memory
 * it can make your program more efficiency
 * of course,most importantly,it is auto!!!!
 * only one DBConnectionPool instance
 */
class DBConnectionPool {
private Vector connections=null;
private String user="root";
private String password="leilei520";
private String URL="jdbc:mysql://localhost:3306/test";
private String driver_MySQL="com.mysql.jdbc.Driver";
private static DBConnectionPool DBPOOL = null;
private static Hashtable<String,Connection> freeConnections=new Hashtable<String,Connection>();
private static Hashtable<String,Connection> totalConnections=new Hashtable<String,Connection>();
private int checkedOut= totalConnections.size()-freeConnections.size();
    private int initialConnections = 10; 
    private int incrementalConnections = 5; 
private int maxConnections=100;
private String poolName;
private static String connectionsName;
private Iterator freeConnectionsKeyArray = freeConnections.keySet().iterator();
Connection conn= null;
private Timer timer = new Timer();
private boolean busy =false;
public DBConnectionPool(String poolName,String URL,String user,  
String password,int maxConnections){
   this.poolName=poolName;
   this.user=user;
   this.password=password;
   this.URL=URL;
   this.maxConnections=maxConnections;
if(DBPOOL == null) {
   DBPOOL=new DBConnectionPool(poolName, URL, user, password, maxConnections);
}
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getURL() {
return URL;
}
public void setURL(String uRL) {
URL = uRL;
}
public String getPoolName() {
return poolName;
}
public void setPoolName(String poolName) {
this.poolName = poolName;
}
public String getConnectionsName() {
return connectionsName;
}
public void setConnectionsName(String connectionsName) {
this.connectionsName = connectionsName;
}
public int getInitialConnections() {
    return this.initialConnections;
}
    public void setInitialConnections(int initialConnections) {
        this.initialConnections = initialConnections;
    }   
    public int getIncrementalConnections() {
        return this.incrementalConnections;
    }   
    public void setIncrementalConnections(int incrementalConnections) {
        this.incrementalConnections = incrementalConnections;
    }  
    public int getMaxConnections() {
        return this.maxConnections;
    }   
    public void setMaxConnections(int maxConnections) {
        this.maxConnections = maxConnections;
    } 
    public int getExistConnectionsNumbers() {
     return checkedOut;
    }
    public Connection getConnectionByName(String poolName) {
     return totalConnections.get(poolName);
    }
    public synchronized void createPool(String poolName) throws Exception {
        // make sure new ConnectionPool has not built
     this.poolName=poolName;
        if (connections != null) {
            return; 
        }else {
        Driver driver = (Driver) (Class.forName(this.driver_MySQL).newInstance());
        DriverManager.registerDriver(driver); 
        connections = new Vector();
        createConnections(poolName,this.initialConnections);
        System.out.println(" DBConnectionPool create success! ");
        }
    }
   
    @SuppressWarnings("unchecked")
private void createConnections(String connectionsName,int numConnections) throws SQLException {
        this.connectionsName=connectionsName;
     for (int x = 0; x < numConnections; x++) {
            if (this.maxConnections > 0 &&
                this.connections.size() >= this.maxConnections) {
                break;
            }else {
            Connection here = (Connection) newConnection();
            connections.addElement(here);
            totalConnections.put(connectionsName, here);
            System.out.println(" DBConnection is created! ......");
            }
        }
    }
public synchronized void freeConnection(String connectionsName,Connection conn) throws SQLException {
//conn.close();
//DBConnectionPool dbcPool = dbcPool.getConnectionByName(poolName);
this.connectionsName=connectionsName;
freeConnections.put(poolName, conn);
conn.close();
this.checkedOut--;
} 
public synchronized Connection getConnection(String connectionsName,long timeout) {
        this.connectionsName=connectionsName;
if (connections == null) {
            return null; 
        }
        try {
            Connection conn = getFreedConnection(); 
            while (conn == null) {
                wait(250);
                conn = getFreedConnection(); 
            }
} catch (Exception e) {
// TODO: handle exception
}
        return conn; 
}
   private Connection AutoFindFreeConnection() throws SQLException {
        Connection conn = null;
        Enumeration enumerate = connections.elements();
        if(freeConnections.size()==0) {
          while (enumerate.hasMoreElements()) {
             conn = (Connection)enumerate.nextElement();
               if (isBusy()) {
                  conn =getFreedConnection();
                  setBusy(true);
                   if (!AutoTestConnection(connectionsName)) {
                      conn = newConnection();
                      setCurrentConnection(conn);
                }
                break; 
            }
        }
        }else {
         conn = freeConnections.get((String)freeConnectionsKeyArray.next());
        }
        return conn; 
    }
    private boolean AutoTestConnection(String connectionsName) throws SQLException  {
            try {
                if (conn == null) {
                 conn = getTotalConnection();
                    conn.setAutoCommit(false);
                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.getResultSet();
                    if(rs.getRow()!=0){
                     System.out.println("this is a valid connection! 小 boy");
                    }
                }
} catch (Throwable e) {
           if(conn!=null){  
               try {  
                   conn.rollback();  
               } catch (SQLException e1) {  
                   e1.printStackTrace();  
               } 
           }
}finally {
            closeConnection(totalConnections.get(connectionsName));
}
        return true;
    }
    public void returnConnection(Connection conn) {
        if (connections == null) {
            System.out.println(" 连接池不存在，无法返回此连接到连接池中 !");
            return;
        }
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
            Connection xuesong = (Connection) enumerate.nextElement();
            if (xuesong == getFreedConnection()) {
                setBusy(false);
                break;
            }
        }
    }
    private synchronized void AutoRefreshConnections() throws SQLException {
        if (connections == null) {
            System.out.println(" 连接池不存在，无法刷新 !");
            return;
        }
        Connection helloCC=null;
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
            helloCC = (Connection) enumerate.nextElement();
            if (isBusy()) {
                wait(5000); 
            }
            closeConnection(getFreedConnection());
            setCurrentConnection(newConnection());
            setBusy(false);
        }
    }
   
    public synchronized void closeConnectionPool() throws SQLException {
        if (DBPOOL == null && conn == null) {
            System.out.println(" 连接池不存在，无法关闭 !");
            return;
        }
        Enumeration enumerate = connections.elements();
        while (enumerate.hasMoreElements()) {
            if (isBusy()) {
                wait(5000); 
            }
            closeConnection(getTotalConnection());
            connections.removeElement(getTotalConnection());
        }
        connections = null;
    }
private synchronized void AutoRelease() throws SQLException  {
while(freeConnectionsKeyArray.hasNext()) {
String key = (String)freeConnectionsKeyArray.next();
conn=(Connection)freeConnections.get(key);
conn.close();
this.freeConnections.clear();
}
}


private Connection newConnection() throws SQLException  {
        Connection conn = DriverManager.getConnection(URL, user,
                password);
        return conn; 
}
public synchronized void TimerEvent(long mSeconds) {
timer.schedule(new TimerTask(){
        //here i will do some clear work to make sure high rates.By Gongxuesong
@Override
public void run() {
// TODO Auto-generated method stub
try {
AutoRefreshConnections();
AutoSetCapicaty();
AutoFindFreeConnection();
AutoRelease();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}, mSeconds);
} 
    private boolean closeConnection(Connection conn) throws SQLException {
            freeConnections.put(poolName, conn);
            conn.close();
            return true;
    }
    private void wait(int mSeconds) {
        try {
            Thread.sleep(mSeconds);
        } catch (InterruptedException e) {
        }
}
    private void AutoSetCapicaty () throws SQLException {
        if (connections.size() == 0) {
            DatabaseMetaData metaData = conn.getMetaData();
            int driverMaxConnections = metaData.getMaxConnections();
            if (driverMaxConnections > 0 &&
                this.maxConnections > driverMaxConnections) {
                this.maxConnections = driverMaxConnections;
            }
        }
    }
    public DBConnectionPool returnDBConnectionPool() {
     return DBPOOL;
    }
    public void setCurrentConnection(Connection gongxuesong){
     this.conn = gongxuesong;
    }
    private Connection getFreedConnection() {
        return freeConnections.get(connectionsName);
    }
    public Connection getTotalConnection() {
        return totalConnections.get(connectionsName);
    }
    private boolean isBusy() {
     if (totalConnections.size()*2>maxConnections || checkedOut*2>maxConnections) {
busy=true;
}
        return busy;
    }
    private boolean setBusy(boolean busy) {
     this.busy=busy;
     return busy;
    }
}