數據庫連接池的設計思路及java實現

2019獨角獸企業重金招聘Python工程師標準>>>

connectionPool.DBConnectionManager

[java]?view plain?copy

package?connectionPool;??
??
import?java.sql.Connection;??
import?java.sql.Driver;??
import?java.sql.DriverManager;??
import?java.sql.SQLException;??
import?java.util.ArrayList;??
import?java.util.Enumeration;??
import?java.util.HashSet;??
import?java.util.Hashtable;??
import?java.util.List;??
import?java.util.Map;??
import?java.util.Map.Entry;??
import?java.util.Set;??
??
import?connectionPool.util.Logger;??
import?connectionPool.util.PropertiesMgr;??
??
/**?
?*?連接池的設計思路?
?*?1、?
?*?初始化固定數目的連接（空閑連接與活躍連接在同一池中），建立連接的代理類，添加busy與startTime屬性以便分發與回收連接?
?*?另建立守護線程回收失效連接?
?*?2、?
?*?維護一空閑連接池，初始為空，需要連接時建立，用完的連接回收進入空閑連接池；?
?*?后續所需連接從空閑連接池獲取；activeNum記錄活躍連接數目；?
?*?當空閑連接池為空且活躍連接數達到上限時，請求等待，超時即獲取連接失敗，超時前有連接被釋放方可獲得連接?
?*?第二個設計巧妙優勢明顯，采用第二種方式?
?*??
?*?數據庫連接管理類，單例模式?
?*?可以管理加載多個數據庫驅動，維護多個數據庫連接池?
?*?@author?shijin?
?*?
?*/??
public?class?DBConnectionManager?{??
??????
????private?static?DBConnectionManager?dbm?=?null;??
??????
????//單例模式里的成員變量都相當于是static了？??
????/**?
?????*?客戶數目?
?????*/??
????private?static?int?clients?=?0;??
????/**?
?????*?加載的驅動器集合?
?????*/??
????private?Set<Driver>?drivers?=?new?HashSet<Driver>();??
????/**?
?????*?數據庫連接池字典?
?????*/??
????private?Hashtable<String,DBConnectionPool>?pools?=?new?Hashtable<String,DBConnectionPool>();??
??????
????private?final?Logger?log?=?Logger.getInstance(DBConnectionPool.class);??
??????
????private?DBConnectionManager()?{??
????????loadDrivers();??
????????createPools();??
????}??
??
????/**?
?????*?裝載和注冊所有的JDBC驅動程序?
?????*/??
????private?void?loadDrivers()?{??
????????String?str_drivers?=?PropertiesMgr.getProperty("driver");??
????????for(String?str_driver:str_drivers.split("\\s"))?{??
????????????Driver?driver?=?null;??
????????????try?{??
????????????????driver?=?(Driver)Class.forName(str_driver).newInstance();??
????????????????DriverManager.registerDriver(driver);??
????????????????log.info("成功加載JDBC驅動："?+?str_driver);??
????????????}?catch?(InstantiationException?e)?{??
????????????????log.error("加載JDBC驅動"?+?str_driver?+?"時初始化異常，請檢查配置文件");??
????????????}?catch?(IllegalAccessException?e)?{??
????????????????log.info("加載JDBC驅動"?+?str_driver?+?"時非法訪問，請檢查配置文件");??
????????????}?catch?(ClassNotFoundException?e)?{??
????????????????log.info("未找到JDBC驅動"?+?str_driver?+?"請引入相關包");??
????????????}?catch?(SQLException?e)?{??
????????????????log.info("加載JDBC驅動"?+?str_driver?+?"失敗，請檢查引入包的正確性");??
????????????}??
????????????drivers.add(driver);??
????????}??
????}??
??
????/**?
?????*?根據配置文件創建數據庫連接池?
?????*/??
????private?void?createPools()?{??
????????Enumeration<?>?elements?=?PropertiesMgr.propertiesNames();??
????????while(elements.hasMoreElements())?{??
????????????String?element?=?(String)elements.nextElement();??
????????????if(element.endsWith(".url"))?{??
????????????????String?poolName?=?element.substring(0,?element.lastIndexOf("."));??
????????????????String?url?=?PropertiesMgr.getProperty(poolName?+?".url");??
????????????????if(url?==?null)?{??
????????????????????log.error("無法連接到數據庫"?+?poolName?+?"請檢查配置文件連接字符串");??
????????????????????continue;??
????????????????}??
????????????????String?user?=?PropertiesMgr.getProperty(poolName?+?".user");??
????????????????String?pwd?=?PropertiesMgr.getProperty(poolName?+?".password");??
????????????????String?str_max?=?PropertiesMgr.getProperty(poolName?+?".maxconn",?"0");??
????????????????int?maxConn?=?0;??
????????????????try{??
????????????????????maxConn?=?Integer.parseInt(str_max);??
????????????????}catch(NumberFormatException?e)?{??
????????????????????log.error("數據庫"?+?poolName?+?"最大連接數設置錯誤，默認設為20");??
????????????????????maxConn?=?20;??
????????????????}?????????????????
????????????????DBConnectionPool?pool?=?new?DBConnectionPool(maxConn,url,poolName,user,pwd);??
????????????????pools.put(poolName,?pool);??
????????????????log.info("成功創建數據庫連接池"?+?poolName);??
????????????}??
????????}??
????}??
??
????/**?
?????*?獲得單例?
?????*?@return?DBConnectionManager單例?
?????*/??
????public?synchronized?static?DBConnectionManager?getInstance()?{??
????????if(dbm?==?null)?{??
????????????dbm?=?new?DBConnectionManager();??
????????}??
????????clients++;??
????????return?dbm;??
????}??
??
????/**?
?????*?從指定連接池中獲取可用連接，無等待?
?????*?@param?poolName??要獲取連接的連接池名稱?
?????*?@return??連接池中的一個可用連接或null?
?????*/??
????public?Connection?getConnection(String?poolName)?{??
????????DBConnectionPool?pool?=?(DBConnectionPool)pools.get(poolName);??
????????return?pool.getConnection();??
????}??
??????
????/**?
?????*?從指定的連接池獲取可用連接，有等待超時?
?????*?@param?poolName??要獲取連接的連接池名稱?
?????*?@param?timeout???獲取可用連接的超時時間,單位為秒?
?????*?@return??????????連接池中的一個可用連接或null?
?????*/??
????public?Connection?getConnection(String?poolName,long?timeout)?{??
????????DBConnectionPool??pool?=?(DBConnectionPool)pools.get(poolName);??
????????return?pool.getConnection(timeout);??
????}??
??????
????/**?
?????*?回收指定連接池的連接?
?????*?@param?poolName??連接池名稱?
?????*?@param?conn??????要回收的連接?
?????*/??
????public?void?freeConnection(String?poolName,Connection?conn)?{??
????????DBConnectionPool?pool?=?(DBConnectionPool)pools.get(poolName);??
????????if(pool?!=?null)?{??
????????????pool.freeConnection(conn);??
????????}??
????????log.error("找不到連接池，無法回收，請檢查參數");??
????}??
??????
????/**?
?????*?關閉所有連接，撤銷驅動器的注冊?
?????*/??
????public?synchronized?void?release()?{??
????????//所有客戶連接都關閉時才真正關閉連接撤銷注冊??
????????if(clients--?!=?0)?{??
????????????return;??
????????}??
????????for(Map.Entry<String,DBConnectionPool>?poolEntry:pools.entrySet())?{??
????????????DBConnectionPool?pool?=?poolEntry.getValue();??
????????????pool.releaseAll();??
????????}??
????????log.info("已經關閉所有連接");??
????????for(Driver?driver:drivers)?{??
????????????try?{??
????????????????DriverManager.deregisterDriver(driver);??
????????????????log.info("撤銷JDBC驅動器"?+?driver.getClass().getName()?+?"的注冊");??
????????????}?catch?(SQLException?e)?{??
????????????????log.error("撤銷JDBC驅動器"?+?driver.getClass().getName()?+?"的注冊異常");??
????????????}??
????????}??
????????log.info("驅動器撤銷完成");??
????}??
??????
????/**?
?????*?此內部類定義了一個連接池.?
?????*?它能夠獲取數據庫連接,直到預定的最?大連接數為止?
?????*?在返回連接給客戶程序之前,它能夠驗證連接的有效性?
?????*?@author?shijin?
?????*/??
????private?class?DBConnectionPool?{??
????????private?int?activeNum?=?0;??
????????private?int?maxConn?=?0;??
????????private?String?url?=?null;??
????????private?String?poolName?=?null;??
????????private?String?user?=?null;??
????????private?String?pwd?=?null;??
????????private?List<Connection>?freeConnections?=?new?ArrayList<Connection>();??
??????????
????????/**?
?????????*??
?????????*?@param?maxConn???設定的連接池允許的最大連接數?
?????????*?@param?url???????數據庫連接url?
?????????*?@param?poolName??連接池名稱?
?????????*?@param?user??????數據庫用戶名，或null?
?????????*?@param?pwd???????數據庫用戶密碼，或null?
?????????*/??
????????public?DBConnectionPool(int?maxConn,?String?url,?String?poolName,??
????????????????String?user,?String?pwd)?{??
????????????super();??
????????????this.maxConn?=?maxConn;??
????????????this.url?=?url;??
????????????this.poolName?=?poolName;??
????????????this.user?=?user;??
????????????this.pwd?=?pwd;??
????????}??
??????????
????????/**?
?????????*?獲得一個可用連接，不保證任何情況都能返回一個連接（比如超過最大連接數的時候返回null）?
?????????*?1、若空閑連接池不為空，從空閑連接池取出一個連接后檢查有效性，正常則返回，失效則重新獲取連接?
?????????*?2、若空閑連接池為空且未超過最大連接數限制，新建一個連接并返回?
?????????*?3、空閑連接數為空且超過最大連接數限制，返回null?
?????????*?@return??獲得的可用連接?
?????????*/??
????????public?synchronized?Connection?getConnection()?{??
????????????Connection?conn?=?null;??
????????????//空閑連接池中有空閑連接，直接取??
????????????if(freeConnections.size()?>?0)?{??
????????????????//從空閑連接池中取出一個連接??
????????????????conn?=?freeConnections.get(0);??
????????????????freeConnections.remove(0);??
????????????????//檢測連接有效性??
????????????????try{??
????????????????????if(conn.isClosed())?{??
????????????????????????//由于已經從空閑連接池取出，所以不使用無效連接其就無法重新進入??
????????????????????????//空閑連接池，意味著其已經被刪除了，記入日志即可??
????????????????????????log.info("從連接池"?+?poolName?+?"中取出的連接已關閉，重新獲取連接");??
????????????????????????//繼續從連接池嘗試獲取連接??
????????????????????????conn?=?getConnection();??
????????????????????}??
????????????????}catch(SQLException?e)?{??
????????????????????log.info("從連接池"?+?poolName?+?"中取出的發生服務器訪問錯誤，重新獲取連接");??
????????????????????conn?=?getConnection();??
????????????????}??
????????????}?else?if(activeNum?<?maxConn)?{??
????????????????conn?=?newConnection();??
????????????}?else?{??
????????????????//未獲得連接??
????????????}??
????????????if(conn?!=?null)?{??
????????????????activeNum++;??
????????????}??
????????????return?conn;??
????????}??
??????????
????????/**?
?????????*?當無空閑連接而又未達到最大連接數限制時創建新的連接?
?????????*?@return??新創建的連接?
?????????*/??
????????private?Connection?newConnection()?{??
????????????Connection?conn?=?null;??
????????????try{??
????????????????if(user?==?null)?{??
????????????????????conn?=?DriverManager.getConnection(url);??
????????????????}?else?{??
????????????????????conn?=?DriverManager.getConnection(url,?user,?pwd);??
????????????????}??
????????????????log.info("與數據庫"?+?poolName?+?"創建一個新連接");??
????????????}catch(SQLException?e)?{??
????????????????log.error("無法根據\""?+?url?+?"\"與數據庫"?+?poolName?+?"建立新連接");??
????????????}??
????????????return?conn;??
????????}??
??????????
????????/**?
?????????*?獲得一個可用連接，超過最大連接數時線程等待，直到有有連接釋放時返回一個可用連接或者超時返回null?
?????????*?@param?timeout?等待連接的超時時間，單位為秒?
?????????*?@return?
?????????*/??
????????public?synchronized?Connection?getConnection(long?timeout)?{??
????????????Connection?conn?=?null;??
????????????long?startTime?=?System.currentTimeMillis();??
????????????while((conn?=?getConnection())?==?null)?{??
????????????????try{??
????????????????????//被notify(),notifyALL()喚醒或者超時自動蘇醒??
????????????????????wait(timeout);??
????????????????}catch(InterruptedException?e)?{??
????????????????????log.error("等待連接的線程被意外打斷");??
????????????????}??
????????????????//若線程在超時前被喚醒，則不會返回null，繼續循環嘗試獲取連接??
????????????????if(System.currentTimeMillis()?-?startTime?>?timeout*1000000)??
????????????????????return?null;??
????????????}??
????????????return?conn;??
????????}??
??????????
????????/**?
?????????*?將釋放的空閑連接加入空閑連接池，活躍連接數減一并激活等待連接的線程?
?????????*?@param?conn??釋放的連接?
?????????*/??
????????public?synchronized?void?freeConnection(Connection?conn)?{??
????????????freeConnections.add(conn);??
????????????activeNum--;??
????????????notifyAll();//通知正在由于達到最大連接數限制而wait的線程獲取連接??
????????}??
??????????
????????/**?
?????????*?關閉空閑連接池中的所有連接?
?????????*/??
????????public?synchronized?void?releaseAll()?{??
????????????for(Connection?conn:freeConnections)?{??
????????????????try{??
????????????????????conn.close();??
????????????????????log.info("關閉空閑連接池"?+?poolName?+?"中的一個連接");??
????????????????}catch(SQLException?e)?{??
????????????????????log.error("關閉空閑連接池"?+?poolName?+?"中的連接失敗");??
????????????????}??
????????????}??
????????????freeConnections.clear();??
????????}??
????}??
}??

connectionpool.util.Logger

[java]?view plain?copy

package?connectionPool.util;??
??
import?java.io.FileWriter;??
import?java.io.IOException;??
import?java.io.PrintWriter;??
import?java.util.Date;??
??
/**?
?*?日志文件創建維護類，單例模式?
?*?@author?shijin?
?*?
?*/??
public?class?Logger?{??
??????
????private?static?Logger?logger=?null;??
????private?PrintWriter?log?=?null;??
????private?static?int?level?=?0;??
????private?Class<?>?c?=?null;??
????private?static?final?int?DEBUGLEVEL?=?1;??
????private?static?final?int?INFOLEVEL?=?2;??
????private?static?final?int?ERRORLEVEL?=?3;??
??????
????private?Logger(Class<?>?c)?{??
????????String?logFileName?=?PropertiesMgr.getProperty("logfile","DBlog.txt");??
????????String?str_level?=?PropertiesMgr.getProperty("loglevel",?"3");??
????????this.c?=?c;??
????????level?=?Integer.parseInt(str_level);??
????????try?{??
????????????log?=?new?PrintWriter(new?FileWriter(logFileName),true);??
????????}?catch?(IOException?e)?{??
????????????System.err.println("無法打開日志文件"?+?logFileName);??
????????????log?=?new?PrintWriter(System.err);??
????????}??
????}??
??????
????public?synchronized?static?Logger?getInstance(Class<?>?c)?{??
????????if(logger?==?null)?{??
????????????logger?=?new?Logger(c);??
????????}??
????????return?logger;??
????}??
??????
??????
????public?void?debug(String?msg)?{??
????????if(level?>?DEBUGLEVEL)?{??
????????????msg?=?"DEBUG:"?+?new?Date()?+?"-"?+?msg;??
????????????System.out.println(msg);??
????????????log.println(msg);??
????????}??
??????????????
????}??
??????
????public?void?info(String?msg)?{??
????????if(level?>?INFOLEVEL)?{??
????????????msg?=?"INFO:"?+?new?Date()?+?"-"?+?msg;??
????????????System.out.println(msg);??
????????????log.println(msg);??
????????}??
??????????????
????}??
??????
????public?void?error(String?msg)?{??
????????if(level?>?ERRORLEVEL)?{??
????????????msg?=?"ERROR:"?+?new?Date()?+?"-"?+?c?+?"-"?+?msg;??
????????????System.out.println(msg);??
????????????log.println(msg);??
????????}??
????}??
}??

connection.util.PropertiesMgr

[java]?view plain?copy

package?connectionPool.util;??
??
import?java.io.File;??
import?java.io.IOException;??
import?java.util.Enumeration;??
import?java.util.Properties;??
/**?
?*?屬性文件加載管理類，單例模式?
?*?@author?shijin?
?*?
?*/??
public?class?PropertiesMgr?{??
????private?static?Properties?pro?=?new?Properties();??
????private?PropertiesMgr(){}??
????static?{??
????????try?{??
????????????pro.load(PropertiesMgr.class.getClassLoader().getResourceAsStream("config"?+?File.separator?+?"DB.properties"));??
????????}?catch?(IOException?e)?{??
????????????e.printStackTrace();??
????????}??
????}??
??????
????public?static?String?getProperty(String?key)?{??
????????return?pro.getProperty(key);??
????}??
??????
????public?static?String?getProperty(String?key,String?defaultValue)?{??
????????//找不到key用defaultValue，而不是說后面為空字符串采用defaultValue??
????????return?pro.getProperty(key,?defaultValue);??
????}??
??????
????public?static?Enumeration<?>?propertiesNames()?{??
????????return?pro.propertyNames();??
????}??
}??

DB.properties

[plain]?view plain?copy

driver=com.mysql.jdbc.Driver??
??
mysql.url=jdbc:mysql://127.0.0.1/caiwu?useUnicode=true&characterEncoding=gb2312??
??
mysql.user=root??
??
mysql.password=123??
??
mysql.maxconn=1000??
??
#\u65E5\u5FD7\u6587\u4EF6\u7684\u540D\u79F0??
logfile=??
#\u65E5\u5FD7\u7EA7\u522B??
loglevel=??