數據庫連接池Java實現小結 - 中國WEB開發者網絡 (http://www.webasp.net) -- 技術教程 (http://www.webasp.net/article/) --- 數據庫連接池Java實現小結 (http://www.webasp.net/article/13/12560.htm)

-- 作者：未知 -- 發佈日期： 2004-08-24

因為工作需要要使用到連接池，所以拜讀了互聯網上眾多前輩的文章，學了不少經驗，這裡想做一個小結，加上自己的想法和在一起，希望能給大家一些幫助。 　　目的： 消除數據庫頻繁連接帶來的開銷和瓶頸。 　　解決方案： 不過多的限制用戶的使用，既不能太多的要求用戶按規定的方法得到和使用數據庫連 盡量保持用戶的習慣 　　目前的很多方法都是要求用戶只能按規定方法使用連接，不能使用直接關閉數據連接的方法。解決辦法就是使用代理類，來中間解決。可以參考http://www-900.ibm.com/developerWorks/cn/java/l-connpoolproxy/index.shtml 能維護連接的正常狀態 要求用戶按規定的方法得到和使用數據庫連 盡量保持用戶的習慣 　　目前的很多方法都是要求用戶只能按規定方法使用連接，不能使用直接關閉數據連接的方法。解決辦法就是使用代理類，來中間解決。可以參考http://www-900.ibm.com/developerWorks/cn/java/l-connpoolproxy/index.shtml 能維護連接的正常狀態 　　因為針對數據庫連接創建的資源，如果不能及時的釋放，就會影響下一次數據連接的使用。例如在sql 2k中，一個連接不同創建多條Statement否則操作時會有數據連接占線的異常，所以必須在歸還連接以後釋放這些資源。 //判斷是使用了createStatement語句 if (CREATESTATE.equals(method.getName())) { obj = method.invoke(conn, args); statRef = (Statement)obj;//記錄語句 return obj; } 　 //判斷是否調用了close的方法，如果調用close方法則把連接置為無用狀態 if(CLOSE.equals(method.getName())) { //設置不使用標誌 setIsFree(false); //檢查是否有後續工作，清除該連接無用資源 if (statRef != null) statRef.close(); if (prestatRef != null) prestatRef.close(); return null; } 正確保護類不被違例使用 一個考慮就是不能讓用戶隨便使用代理類，而只能自己使用，一個就是用內部私有類，一個就是使用只有指定類才能調用的標誌。我的實現就是採用後者。 /** * 創建連接的工廠，只能讓工廠調用 * @param factory 要調用工廠，並且一定被正確初始化 * @param param 連接參數 * @return 連接 */ static public _Connection getConnection(ConnectionFactory factory, ConnectionParam param) { if (factory.isCreate())//判斷是否正確初始化的工廠 { _Connection _conn = new _Connection(param); return _conn; } else return null; } 提供良好的用戶接口，簡單實用 　　使用靜態方法創建工廠，然後來得到連接，使用完全和普通的Connection方法一樣，沒有限制。同時為了方便，設置了連接參數類和工廠參數類。 ConnectionParam param = new ConnectionParam(driver,url,user,password); ConnectionFactory cf = null;//new ConnectionFactory(param, new FactoryParam()); try{ cf = new ConnectionFactory(param,new FactoryParam()); Connection conn1 = cf.getFreeConnection(); Connection conn2 = cf.getFreeConnection(); Connection conn3 = cf.getFreeConnection(); Statement stmt = conn1.createStatement(); ResultSet rs = stmt.executeQuery("select * from requests"); if (rs.next()) { System.out.println("conn1 y"); } else { System.out.println("conn1 n"); } stmt.close(); conn1.close(); 為了實現連接池的正常運作，使用了單態模 　 /** * 使用指定的參數創建一個連接池 */ public ConnectionFactory(ConnectionParam param, FactoryParam fparam) throws SQLException { //不允許參數為空 if ((param == null)||(fparam == null)) throw new SQLException("ConnectionParam和FactoryParam不能為空"); if (m_instance == null) { synchronized(ConnectionFactory.class){ if (m_instance == null) { //new instance //參數定制 m_instance = new ConnectionFactory(); m_instance.connparam = param; m_instance.MaxConnectionCount = fparam.getMaxConn(); m_instance.MinConnectionCount = fparam.getMinConn(); m_instance.ManageType = fparam.getType(); m_instance.isflag = true; //初始化，創建MinConnectionCount個連接 System.out.println("connection factory 創建！"); try{ for (int i=0; i < m_instance.MinConnectionCount; i++) { _Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam); if (_conn == null) continue; System.out.println("connection創建"); m_instance.FreeConnectionPool.add(_conn);//加入空閒連接池 m_instance.current_conn_count ++; //標誌是否支持事務 m_instance.supportTransaction = _conn.isSupportTransaction(); } } catch(Exception e) { e.printStackTrace(); } //根據策略判斷是否需要查詢 if (m_instance.ManageType != 0) { Thread t = new Thread(new FactoryMangeThread(m_instance)); t.start(); } } } } } 連接池的管理 　　對於連接池的管理，我是設想使用靜態管理和動態管理兩種策略，設置了最大限制，和恆定的連接數。使用了2個池，一個空閒池，一個使用池。靜態就是使用的時候發現空閒連接不夠再去檢查。動態就是使用了一個線程定時檢查。 //根據策略判斷是否需要查詢 if (m_instance.ManageType != 0) { Thread t = new Thread(new FactoryMangeThread(m_instance)); t.start(); } //連接池調度線程 public class FactoryMangeThread implements Runnable { ConnectionFactory cf = null; long delay = 1000; public FactoryMangeThread(ConnectionFactory obj) { cf = obj; } /* (non-Javadoc) * @see java.lang.Runnable#run() */ public void run() { while(true){ try{ Thread.sleep(delay); } catch(InterruptedException e){} System.out.println("eeeee"); //判斷是否已經關閉了工廠，那就退出監聽 if (cf.isCreate()) cf.schedule(); else System.exit(1); } } } 最後給出完整的源代碼： 　　_Connectio.java package scut.ailab.connectionpool; import java.lang.reflect.*; import java.sql.*; /** * @author youyongming * 定義數據庫連接的代理類 */ public class _Connection implements InvocationHandler { //定義連接 private Connection conn = null; //定義監控連接創建的語句 private Statement statRef = null; private PreparedStatement prestatRef = null; //是否支持事務標誌 private boolean supportTransaction = false; //數據庫的忙狀態 private boolean isFree = false; //最後一次訪問時間 long lastAccessTime = 0; //定義要接管的函數的名字 String CREATESTATE = "createStatement"; String CLOSE = "close"; String PREPARESTATEMENT = "prepareStatement"; String COMMIT = "commit"; String ROLLBACK = "rollback"; /** * 構造函數，採用私有，防止被直接創建 * @param param 連接參數 */ private _Connection(ConnectionParam param) { //記錄日至 try{ //創建連接 Class.forName(param.getDriver()).newInstance(); conn = DriverManager.getConnection(param.getUrl(),param.getUser(), param.getPassword()); DatabaseMetaData dm = null; dm = conn.getMetaData(); //判斷是否支持事務 supportTransaction = dm.supportsTransactions(); } catch(Exception e) { e.printStackTrace(); } } /* (non-Javadoc) * @see java.lang.reflect.InvocationHandler#invoke 　*(java.lang.Object, java.lang.reflect.Method, java.lang.Object[]) */ public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { Object obj = null; //判斷是否調用了close的方法，如果調用close方法則把連接置為無用狀態 if(CLOSE.equals(method.getName())) { //設置不使用標誌 setIsFree(false); //檢查是否有後續工作，清除該連接無用資源 if (statRef != null) statRef.close(); if (prestatRef != null) prestatRef.close(); return null; } //判斷是使用了createStatement語句 if (CREATESTATE.equals(method.getName())) { obj = method.invoke(conn, args); statRef = (Statement)obj;//記錄語句 return obj; } //判斷是使用了prepareStatement語句 if (PREPARESTATEMENT.equals(method.getName())) { obj = method.invoke(conn, args); prestatRef = (PreparedStatement)obj; return obj; } //如果不支持事務，就不執行該事物的代碼 if ((COMMIT.equals(method.getName())||ROLLBACK.equals(method.getName())) && (!isSupportTransaction())) return null; obj = method.invoke(conn, args); //設置最後一次訪問時間，以便及時清除超時的連接 lastAccessTime = System.currentTimeMillis(); return obj; } /** * 創建連接的工廠，只能讓工廠調用 * @param factory 要調用工廠，並且一定被正確初始化 * @param param 連接參數 * @return 連接 */ static public _Connection getConnection(ConnectionFactory factory, ConnectionParam param) { if (factory.isCreate())//判斷是否正確初始化的工廠 { _Connection _conn = new _Connection(param); return _conn; } else return null; } public Connection getFreeConnection() { //返回數據庫連接conn的接管類，以便截住close方法 Connection conn2 = (Connection)Proxy.newProxyInstance( conn.getClass().getClassLoader(), conn.getClass().getInterfaces(),this); return conn2; } /** * 該方法真正的關閉了數據庫的連接 * @throws SQLException */ void close() throws SQLException{ //由於類屬性conn是沒有被接管的連接，因此一旦調用close方法後就直接關閉連接 conn.close(); } public void setIsFree(boolean value) { isFree = value; } public boolean isFree() { return isFree; } /** * 判斷是否支持事務 * @return boolean */ public boolean isSupportTransaction() { return supportTransaction; } } 　　ConnectionFactory.java 　 package scut.ailab.connectionpool; /** * @author youyongming * */ import java.util.LinkedHashSet; import java.sql.*; import java.util.Iterator; public class ConnectionFactory { private static ConnectionFactory m_instance = null; //在使用的連接池 private LinkedHashSet ConnectionPool = null; //空閒連接池 private LinkedHashSet FreeConnectionPool = null; //最大連接數 private int MaxConnectionCount = 4; //最小連接數 private int MinConnectionCount = 2; //當前連接數 private int current_conn_count = 0; //連接參數 private ConnectionParam connparam = null; //是否創建工廠的標誌 private boolean isflag = false; //是否支持事務 private boolean supportTransaction = false; //定義管理策略 private int ManageType = 0; private ConnectionFactory() { ConnectionPool = new LinkedHashSet(); FreeConnectionPool = new LinkedHashSet(); } /** * 使用指定的參數創建一個連接池 */ public ConnectionFactory(ConnectionParam param, FactoryParam fparam) throws SQLException { //不允許參數為空 if ((param == null)||(fparam == null)) throw new SQLException("ConnectionParam和FactoryParam不能為空"); if (m_instance == null) { synchronized(ConnectionFactory.class){ if (m_instance == null) { //new instance //參數定制 m_instance = new ConnectionFactory(); m_instance.connparam = param; m_instance.MaxConnectionCount = fparam.getMaxConn(); m_instance.MinConnectionCount = fparam.getMinConn(); m_instance.ManageType = fparam.getType(); m_instance.isflag = true; //初始化，創建MinConnectionCount個連接 System.out.println("connection factory 創建！"); try{ for (int i=0; i < m_instance.MinConnectionCount; i++) { _Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam); if (_conn == null) continue; System.out.println("connection創建"); m_instance.FreeConnectionPool.add(_conn);//加入空閒連接池 m_instance.current_conn_count ++; //標誌是否支持事務 m_instance.supportTransaction = _conn.isSupportTransaction(); } } catch(Exception e) { e.printStackTrace(); } //根據策略判斷是否需要查詢 if (m_instance.ManageType != 0) { Thread t = new Thread(new FactoryMangeThread(m_instance)); t.start(); } } } } } /** * 標誌工廠是否已經創建 * @return boolean */ public boolean isCreate() { return m_instance.isflag; } /** * 從連接池中取一個空閒的連接 * @return Connection * @throws SQLException */ public synchronized Connection getFreeConnection() throws SQLException { Connection conn = null; //獲取空閒連接 Iterator iter = m_instance.FreeConnectionPool.iterator(); while(iter.hasNext()){ _Connection _conn = (_Connection)iter.next(); //找到未用連接 if(!_conn.isFree()){ conn = _conn.getFreeConnection(); _conn.setIsFree(true); //移出空閒區 m_instance.FreeConnectionPool.remove(_conn); //加入連接池 m_instance.ConnectionPool.add(_conn); break; } } //檢查空閒池是否為空 if (m_instance.FreeConnectionPool.isEmpty()) { //再檢查是否能夠分配 if (m_instance.current_conn_count < m_instance.MaxConnectionCount) { //新建連接到空閒連接池 int newcount = 0 ; //取得要建立的數目 if (m_instance.MaxConnectionCount - m_instance.current_conn_count >= 　　　　　　　　　m_instance.MinConnectionCount) { newcount = m_instance.MinConnectionCount; } else { newcount = m_instance.MaxConnectionCount - m_instance.current_conn_count; } //創建連接 for (int i=0;i <newcount; i++) { _Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam); m_instance.FreeConnectionPool.add(_conn); m_instance.current_conn_count ++; } } else {//如果不能新建，檢查是否有已經歸還的連接 iter = m_instance.ConnectionPool.iterator(); while(iter.hasNext()){ _Connection _conn = (_Connection)iter.next(); if(!_conn.isFree()){ conn = _conn.getFreeConnection(); _conn.setIsFree(false); m_instance.ConnectionPool.remove(_conn); m_instance.FreeConnectionPool.add(_conn); break; } } } }//if (FreeConnectionPool.isEmpty()) //再次檢查是否能分配連接 if(conn == null){ iter = m_instance.FreeConnectionPool.iterator(); while(iter.hasNext()){ _Connection _conn = (_Connection)iter.next(); if(!_conn.isFree()){ conn = _conn.getFreeConnection(); _conn.setIsFree(true); m_instance.FreeConnectionPool.remove(_conn); m_instance.ConnectionPool.add(_conn); break; } } if(conn == null)//如果不能則說明無連接可用 throw new SQLException("沒有可用的數據庫連接"); } System.out.println("get connection"); return conn; } /** * 關閉該連接池中的所有數據庫連接 * @throws SQLException */ public synchronized void close() throws SQLException { this.isflag = false; SQLException excp = null; //關閉空閒池 Iterator iter = m_instance.FreeConnectionPool.iterator(); while(iter.hasNext()){ try{ ((_Connection)iter.next()).close(); System.out.println("close connection:free"); m_instance.current_conn_count --; }catch(Exception e){ if(e instanceof SQLException) excp = (SQLException)e; } } //關閉在使用的連接池 iter = m_instance.ConnectionPool.iterator(); while(iter.hasNext()){ try{ ((_Connection)iter.next()).close(); System.out.println("close connection:inused"); m_instance.current_conn_count --; }catch(Exception e){ if(e instanceof SQLException) excp = (SQLException)e; } } if(excp != null) throw excp; } /** * 返回是否支持事務 * @return boolean */ public boolean isSupportTransaction() { return m_instance.supportTransaction; } /** * 連接池調度管理 * */ public void schedule() { Connection conn = null; //再檢查是否能夠分配 Iterator iter = null; //檢查是否有已經歸還的連接 { iter = m_instance.ConnectionPool.iterator(); while(iter.hasNext()){ _Connection _conn = (_Connection)iter.next(); if(!_conn.isFree()){ conn = _conn.getFreeConnection(); _conn.setIsFree(false); m_instance.ConnectionPool.remove(_conn); m_instance.FreeConnectionPool.add(_conn); break; } } } if (m_instance.current_conn_count < m_instance.MaxConnectionCount) { //新建連接到空閒連接池 int newcount = 0 ; //取得要建立的數目 if (m_instance.MaxConnectionCount - m_instance.current_conn_count 　　　　>=m_instance.MinConnectionCount) { newcount = m_instance.MinConnectionCount; } else { newcount = m_instance.MaxConnectionCount - m_instance.current_conn_count; } //創建連接 for (int i=0;i <newcount; i++) { _Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam); m_instance.FreeConnectionPool.add(_conn); m_instance.current_conn_count ++; } } } } 　　ConnectionParam.java 　 package scut.ailab.connectionpool; import java.io.Serializable; /** * @author youyongming * 實現數據庫連接的參數類 */ public class ConnectionParam implements Serializable { private String driver; //數據庫驅動程序 private String url; //數據連接的URL private String user; //數據庫用戶名 private String password; //數據庫密碼 /** * 唯一的構造函數，需要指定連接的四個必要參數 * @param driver 數據驅動 * @param url 數據庫連接url * @param user 用戶名 * @param password 密碼 */ public ConnectionParam(String driver,String url,String user,String password) { this.driver = driver; this.url = url; this.user = user; this.password = password; } public String getDriver() { return driver; } public String getPassword() { return password; } public String getUrl() { return url; } public String getUser() { return user; } public void setDriver(String driver) { this.driver = driver; } public void setPassword(String password) { this.password = password; } public void setUrl(String url) { this.url = url; } public void setUser(String user) { this.user = user; } /** * @see java.lang.Object#clone() */ public Object clone(){ ConnectionParam param = new ConnectionParam(driver,url,user,password); return param; } /** * @see java.lang.Object#equals(java.lang.Object) */ public boolean equals(Object obj) { if(obj instanceof ConnectionParam){ ConnectionParam param = (ConnectionParam)obj; return ((driver.compareToIgnoreCase(param.getDriver()) == 0)&& (url.compareToIgnoreCase(param.getUrl()) == 0)&& (user.compareToIgnoreCase(param.getUser()) == 0)&& (password.compareToIgnoreCase(param.getPassword()) == 0)); } return false; } } 　　FactoryMangeThread.java 　 /* * Created on 2003-5-13 * * To change the template for this generated file go to * Window>Preferences>Java>Code Generation>Code and Comments */ package scut.ailab.connectionpool; /** * @author youyongming * */ //連接池調度線程 public class FactoryMangeThread implements Runnable { ConnectionFactory cf = null; long delay = 1000; public FactoryMangeThread(ConnectionFactory obj) { cf = obj; } /* (non-Javadoc) * @see java.lang.Runnable#run() */ public void run() { while(true){ try{ Thread.sleep(delay); } catch(InterruptedException e){} System.out.println("eeeee"); //判斷是否已經關閉了工廠，那就退出監聽 if (cf.isCreate()) cf.schedule(); else System.exit(1); } } } 　　FactoryParam.java 　 /* * Created on 2003-5-13 * * To change the template for this generated file go to * Window>Preferences>Java>Code Generation>Code and Comments */ package scut.ailab.connectionpool; /** * @author youyongming * */ //連接池工廠參數 public class FactoryParam { //最大連接數 private int MaxConnectionCount = 4; //最小連接數 private int MinConnectionCount = 2; //回收策略 private int ManageType = 0; public FactoryParam() { } /** * 構造連接池工廠參數的對象 * @param max 最大連接數 * @param min 最小連接數 * @param type 管理策略 */ public FactoryParam(int max, int min, int type) { this.ManageType = type; this.MaxConnectionCount = max; this.MinConnectionCount = min; } /** * 設置最大的連接數 * @param value */ public void setMaxConn(int value) { this.MaxConnectionCount = value; } /** * 獲取最大連接數 * @return */ public int getMaxConn() { return this.MaxConnectionCount; } /** * 設置最小連接數 * @param value */ public void setMinConn(int value) { this.MinConnectionCount = value; } /** * 獲取最小連接數 * @return */ public int getMinConn() { return this.MinConnectionCount; } public int getType() { return this.ManageType; } } 　　testmypool.java 　 /* * Created on 2003-5-13 * * To change the template for this generated file go to * Window>Preferences>Java>Code Generation>Code and Comments */ package scut.ailab.connectionpool; /** * @author youyongming * */ import java.sql.*; public class testmypool { public void test1() { String user = "DevTeam"; String password = "DevTeam"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String url = "jdbc:odbc:gfqh2"; ConnectionParam param = new ConnectionParam(driver,url,user,password); ConnectionFactory cf = null;//new ConnectionFactory(param, new FactoryParam()); try{ cf = new ConnectionFactory(param,new FactoryParam()); Connection conn1 = cf.getFreeConnection(); Connection conn2 = cf.getFreeConnection(); Connection conn3 = cf.getFreeConnection(); Statement stmt = conn1.createStatement(); ResultSet rs = stmt.executeQuery("select * from requests"); if (rs.next()) { System.out.println("conn1 y"); } else { System.out.println("conn1 n"); } stmt.close(); conn1.close(); Connection conn4 = cf.getFreeConnection(); Connection conn5 = cf.getFreeConnection(); stmt = conn5.createStatement(); rs = stmt.executeQuery("select * from requests"); if (rs.next()) { System.out.println("conn5 y"); } else { System.out.println("conn5 n"); } conn2.close(); conn3.close(); conn4.close(); conn5.close(); } catch(Exception e) { e.printStackTrace(); } finally{ try{ cf.close(); } catch(Exception e) { e.printStackTrace(); } } } public static void main(String[] args) { String user = "DevTeam"; String password = "DevTeam"; String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String url = "jdbc:odbc:gfqh2"; ConnectionParam param = new ConnectionParam(driver,url,user,password); ConnectionFactory cf = null;//new ConnectionFactory(param,new FactoryParam()); try{ cf = new ConnectionFactory(param,new FactoryParam()); ConnectionFactory cf1= new ConnectionFactory(param,new FactoryParam()); Connection conn1 = null; long time = System.currentTimeMillis(); for (int i=0; i <10;i++) { conn1 = cf.getFreeConnection(); Statement stmt = conn1.createStatement(); ResultSet rs = stmt.executeQuery("select * from requests"); if (rs.next()) { System.out.println("conn1 y"); } else { System.out.println("conn1 n"); } conn1.close(); } System.out.println("pool:" + (System.currentTimeMillis()-time)); time = System.currentTimeMillis(); Class.forName(param.getDriver()).newInstance(); for (int i=0; i <10;i++) { conn1 = DriverManager.getConnection(param.getUrl(), 　　　　　　param.getUser(), param.getPassword()); Statement stmt = conn1.createStatement(); ResultSet rs = stmt.executeQuery("select * from requests"); if (rs.next()) { System.out.println("conn1 y"); } else { System.out.println("conn1 n"); } conn1.close(); } System.out.println("no pool:" + (System.currentTimeMillis()-time)); } catch(Exception e) { e.printStackTrace(); } finally{ try{ cf.close(); } catch(Exception e) { e.printStackTrace(); } } } }

webasp.net