手把手教你寫一個數(shù)據(jù)庫連接池！
你知道的越多，不知道的就越多，業(yè)余的像一棵小草！
你來，我們一起精進！你不來，我和你的競爭對手一起精進！
編輯：業(yè)余草
juejin.cn/post/6951343274946723870
推薦：https://www.xttblog.com/?p=5319
文章目錄
術語介紹 實現(xiàn)原理 代碼實現(xiàn) 連接池配置文件 連接池管理 連接池接口 連接池實現(xiàn) 演示代碼 演示效果說明
關于數(shù)據(jù)庫連接池原理，什么雜七雜八的，本文不再重復啰嗦。有什么不理解的參考如下文章。
面試官：數(shù)據(jù)庫連接池為什么都要用Threadlocal呢？
真相讓你吃驚，數(shù)據(jù)庫連接池到底應該設多大？
閱讀本文手寫一套數(shù)據(jù)庫連接池，您可能需要了解如下幾個知識點：
數(shù)據(jù)庫連接池的原理及作用 并發(fā)隊列介紹及使用 配置文件properties信息映射到Java對象
在我們配置連接池的時候，會配置一些數(shù)據(jù)，比如最小空閑連接數(shù)，最大空閑連接數(shù)等等，本文中，您需要理解如下幾個概念。
術語介紹
「空閑連接池」：用來存放已經(jīng)被創(chuàng)建，但是未被使用的連接的容器。
「活動連接池」：用來存放已經(jīng)被創(chuàng)建，并且被使用的連接的容器。
「最大空閑數(shù)」：空閑連接池中，最多存在的空閑連接數(shù)量。
「初始化連接數(shù)」：第一次加載的時候，需要創(chuàng)建的連接數(shù)量，一般大于最小空閑數(shù)，小于最大空閑數(shù)。
「最大連接數(shù)」：空閑連接和活動連接之和。
實現(xiàn)原理
「1、初始化」：第一次加載的時候，根據(jù)配置的初始化連接數(shù)，創(chuàng)建連接，將創(chuàng)建的連接放入到空閑連接池；
「2、獲取連接」：優(yōu)先從空閑池獲取，如果空閑池沒有，就創(chuàng)建一個新的連接
3、「釋放連接」：將需要釋放的連接從活動連接池中移除，如果空閑連接池沒有滿，放將移除的連接放入到空閑連接池，如果空閑連接池已經(jīng)滿了，則關閉此鏈接。
代碼實現(xiàn)
代碼結構如下
連接池配置文件
##驅動名稱：不推薦com.mysql.jdbc.Driver，而是推薦使用com.mysql.cj.jdbc.Driver
driverName?=?com.mysql.cj.jdbc.Driver
##數(shù)據(jù)庫連接地址
url?=?jdbc:mysql://127.0.0.1:3306/test
userName?=?root
passWord?=?root
##連接池名字
poolName?=?Hutao?Connection?Pool
##空閑池，最小連接數(shù)
minFreeConnections?=?1
##初始化空閑池連接數(shù)
initFreeConnections?=?3
##空閑池，最大連接數(shù)
maxFreeConnections?=?5
##最大允許的連接數(shù)，一般小于數(shù)據(jù)庫總連接數(shù)
maxConnections?=?15
##重試獲得連接的頻率??一秒
retryConnectionTimeOut?=?1000
##連接超時時間，默認20分鐘??1000?*?60?*?20
connectionTimeOut?=?1200000
/**
?*?@Description:數(shù)據(jù)庫連接池屬性信息
?*/
public?class?DbProperties?{
????/*?鏈接屬性?*/
????private?String?driverName;
????private?String?url;
????private?String?userName;
????private?String?passWord;
????private?String?poolName;
????/**
?????*?空閑池，最小連接數(shù)
?????*/
????private?int?minFreeConnections;
????/**
?????*?空閑池，最大連接數(shù)
?????*/
????private?int?maxFreeConnections;
????/**
?????*?初始連接數(shù)
?????*/
????private?int?initFreeConnections;
????/**
?????*?重試獲得連接的頻率??毫秒
?????*/
????private?long?retryConnectionTimeOut;
????/**
?????*?最大允許的連接數(shù)
?????*/
????private?int?maxConnections;
????/**
?????*?連接超時時間
?????*/
????private?long?connectionTimeOut;
????public?String?getDriverName()?{
????????????return?driverName;
????}
????public?void?setDriverName(String?driverName)?{
????????????this.driverName?=?driverName;
????}
????public?String?getUrl()?{
????????????return?url;
????}
????public?void?setUrl(String?url)?{
????????????this.url?=?url;
????}
????public?String?getUserName()?{
????????????return?userName;
????}
????public?void?setUserName(String?userName)?{
????????????this.userName?=?userName;
????}
????public?String?getPassWord()?{
????????????return?passWord;
????}
????public?void?setPassWord(String?passWord)?{
????????????this.passWord?=?passWord;
????}
????public?String?getPoolName()?{
????????????return?poolName;
????}
????public?void?setPoolName(String?poolName)?{
????????????this.poolName?=?poolName;
????}
????public?int?getMinFreeConnections()?{
????????????return?minFreeConnections;
????}
????public?void?setMinFreeConnections(int?minFreeConnections)?{
????????????this.minFreeConnections?=?minFreeConnections;
????}
????public?int?getMaxFreeConnections()?{
????????????return?maxFreeConnections;
????}
????public?void?setMaxFreeConnections(int?maxFreeConnections)?{
????????????this.maxFreeConnections?=?maxFreeConnections;
????}
????public?int?getInitFreeConnections()?{
????????????return?initFreeConnections;
????}
????public?void?setInitFreeConnections(int?initFreeConnections)?{
????????????this.initFreeConnections?=?initFreeConnections;
????}
????public?long?getRetryConnectionTimeOut()?{
????????????return?retryConnectionTimeOut;
????}
????public?void?setRetryConnectionTimeOut(long?retryConnectionTimeOut)?{
????????????this.retryConnectionTimeOut?=?retryConnectionTimeOut;
????}
????public?int?getMaxConnections()?{
????????????return?maxConnections;
????}
????public?void?setMaxConnections(int?maxConnections)?{
????????????this.maxConnections?=?maxConnections;
????}
????public?long?getConnectionTimeOut()?{
????????????return?connectionTimeOut;
????}
????public?void?setConnectionTimeOut(long?connectionTimeOut)?{
????????????this.connectionTimeOut?=?connectionTimeOut;
????}
????@Override
????public?String?toString()?{
????????????return?"DbProperties?[driverName="?+?driverName?+?",?url="?+?url?+?",?userName="?+?userName?+?",?passWord="
????????????????????????????+?passWord?+?",?poolName="?+?poolName?+?",?minFreeConnections="?+?minFreeConnections
????????????????????????????+?",?maxFreeConnections="?+?maxFreeConnections?+?",?initFreeConnections="?+?initFreeConnections
????????????????????????????+?",?retryConnectionTimeOut="?+?retryConnectionTimeOut?+?",?maxConnections="?+?maxConnections
????????????????????????????+?",?connectionTimeOut="?+?connectionTimeOut?+?"]";
????}
}
連接池管理
import?java.lang.reflect.Field;
import?java.lang.reflect.Type;
import?java.sql.Connection;
import?java.util.Enumeration;
import?java.util.ResourceBundle;
import?com.hutao.pool.database.pojo.DbProperties;
import?com.hutao.pool.database.service.DbPoolService;
import?com.hutao.pool.database.service.impl.DbPoolServiceImpl;
/**
?*?@Description:數(shù)據(jù)庫連接池管理
?*/
public?class?DbPoolManager?{
????private?static?String?sourcePath?=?"com/hutao/resources/database";
????/**
?????*?數(shù)據(jù)庫連接池配置屬性
?????*/
????private?static?DbProperties?properties?=?null;
????/**
?????*?數(shù)據(jù)庫連接池接口
?????*/
????private?static?DbPoolService?connectionPool?=?null;
????/**
?????*?雙重檢查機制靜態(tài)加載連接池
?????*/
????static?{
????????????try?{
????????????????????if(properties?==?null)?{
????????????????????????????synchronized(DbPoolManager.class)?{
????????????????????????????????????if(properties?==?null)?{
????????????????????????????????????????????properites2Object();
????????????????????????????????????????????connectionPool?=?new?DbPoolServiceImpl(properties);
????????????????????????????????????}
????????????????????????????}
????????????????????}
????????????}?catch?(Exception?e)?{
????????????????????e.printStackTrace();
????????????}
????}
????/**
?????*?@Description:數(shù)據(jù)庫連接池database配置文件映射到java對象
?????*/
????private?static?void?properites2Object()?throws?NoSuchFieldException,?IllegalAccessException?{
????????????properties?=?new?DbProperties();
????????????ResourceBundle?resourceBundle?=?ResourceBundle.getBundle(sourcePath);
????????????//獲取資源文件中所有的key
????????????Enumeration?keys?=?resourceBundle.getKeys();
????????????while?(keys.hasMoreElements())?{
????????????????????String?key?=?(String)?keys.nextElement();
????????????????????//反射獲取類中的屬性字段
????????????????????Field?field=?DbProperties.class.getDeclaredField(key);
????????????????????//屬性字段的類型
????????????????????Type?genericType?=?field.getGenericType();
????????????????????//屬性設置可訪問
????????????????????field.setAccessible(true);
????????????????????//根據(jù)key讀取對應的value值
????????????????????String?value?=?resourceBundle.getString(key);
????????????????????if("int".equals(genericType.getTypeName()))?{
????????????????????????????//反射給屬性賦值
????????????????????????????field.set(properties,?Integer.parseInt(value));
????????????????????}else?if("long".equals(genericType.getTypeName()))?{
????????????????????????????field.set(properties,?Long.parseLong(value));
????????????????????}else?if("java.lang.String".equals(genericType.getTypeName()))?{
????????????????????????????field.set(properties,value);
????????????????????}
????????????}
????}
????/**
?????*?@Description:獲取連接
?????*/
????public?static?Connection?getConnection()?{
????????????return?connectionPool.getConnection();
????}
????/**
?????*?@Description:釋放連接
?????*/
????public?static?void?releaseConnection(Connection?connection)?{
????????????connectionPool.releaseConnection(connection);
????}
連接池接口
import?java.sql.Connection;
/**
?*?@Description:數(shù)據(jù)庫連接池
?*/
public?interface?DbPoolService?{
?
????/**
?????*?@Description:判斷連接是否可用，可用返回true
?????*/
????public?boolean?isAvailable(Connection?connection);
????/**
?????*?@Description:使用重復利用機制獲取連接
?????*/
????public?Connection?getConnection();
????/**
?????*?@Description:使用可回收機制釋放連接
?????*/
????public?void?releaseConnection(Connection?connection);
}
連接池實現(xiàn)
import?java.sql.Connection;
import?java.sql.DriverManager;
import?java.util.concurrent.BlockingQueue;
import?java.util.concurrent.LinkedBlockingQueue;
import?com.hutao.pool.database.pojo.DbProperties;
import?com.hutao.pool.database.service.DbPoolService;
/**
?*?@Description:數(shù)據(jù)庫連接池實現(xiàn)
?*/
public?class?DbPoolServiceImpl?implements?DbPoolService?{
?
????/**
?????*?存放空閑連接的容器，除了可以使用并發(fā)隊列，也可以使用線程安全的集合Vector
?????*/
????private?BlockingQueue?freeConnection?=?null;
????/**
?????*?存放活動連接的容器，除了可以使用并發(fā)隊列，也可以使用線程安全的集合Vector
?????*/
????private?BlockingQueue?activeConnection?=?null;
????/**
?????*?存放映射的屬性配置文件
?????*/
????private?DbProperties?dDbProperties;
????public?DbPoolServiceImpl(DbProperties?dDbProperties)?throws?Exception?{
????????//?獲取配置文件信息
????????this.dDbProperties?=?dDbProperties;
????????freeConnection?=??new?LinkedBlockingQueue<>(dDbProperties.getMaxFreeConnections());
????????activeConnection?=?new?LinkedBlockingQueue<>(dDbProperties.getMaxConnections());
????????init();
????}
????/**
?????*?@Description:初始化空閑線程池
?????*/
????private?void?init()?throws?Exception?{
????????System.out.println("初始化線程池開始，線程池配置屬性："+dDbProperties);
????????if?(dDbProperties?==?null)?{
????????????????throw?new??Exception("連接池配置屬性對象不能為空");
????????}
????????//獲取連接池配置文件中初始化連接數(shù)
????????for?(int?i?=?0;?i?????????????????//創(chuàng)建Connection連接
????????????????Connection?newConnection?=?newConnection();
????????????????if?(newConnection?!=?null)?{
????????????????????????//將創(chuàng)建的新連接放入到空閑池中
????????????????????????freeConnection.add(newConnection);
????????????????}
????????}
????????System.out.println("初始化線程池結束，初始化線程數(shù)："+dDbProperties.getInitFreeConnections());
????}
????private?synchronized?Connection?newConnection()?{
????????try?{
????????????????Class.forName(dDbProperties.getDriverName());
????????????????return?DriverManager.getConnection(dDbProperties.getUrl(),?dDbProperties.getUserName(),dDbProperties.getPassWord());
????????}?catch?(Exception?e)?{
????????????????e.printStackTrace();
????????????????return?null;
????????}
????}
????/**
?????*?@Description:判斷連接是否可用，可用返回true
?????*/
????@Override
????public?boolean?isAvailable(Connection?connection)?{
????????try?{
????????????????if?(connection?==?null?||?connection.isClosed())?{
????????????????????????return?false;
????????????????}
????????}?catch?(Exception?e)?{
????????????????e.printStackTrace();
????????}
????????return?true;
????}
????/**
?????*?@Description:使用重復利用機制獲取連接：如果總連接未超過最大連接，則從空閑連接池獲取連接或者創(chuàng)建一個新的連接，如果超過最大連接，則等待一段時間以后，繼續(xù)獲取連接
?????*/
????@Override
????public?synchronized?Connection?getConnection()?{
????????Connection?connection?=?null;
????????//空閑連接和活動連接的總數(shù)加起來?小于?最大配置連接
????????System.out.println("當前空閑連接總數(shù)："+freeConnection.size()+"?當前活動連接總數(shù)"+activeConnection.size()+", 配置最大連接數(shù)："+?dDbProperties.getMaxConnections());
????????if?(freeConnection.size()+activeConnection.size()?????????????????//空閑連接池，是否還有還有連接，有就取出來，沒有就創(chuàng)建一個新的。
????????????????if?(freeConnection.size()?>?0)?{
????????????????????????connection?=?freeConnection.poll();
????????????????????????System.out.println("從空閑線程池取出線程："+connection+"當前空閑線程總數(shù)："+freeConnection.size());
????????????????}?else?{
????????????????????????connection?=?newConnection();
????????????????????????System.out.println("空閑連接池沒有連接，創(chuàng)建連接"+connection);
????????????????}
????????????????//拿到的連接可用，就添加活動連接池，否則就遞歸繼續(xù)找下一個
????????????????boolean?available?=?isAvailable(connection);
????????????????if?(available)?{
????????????????????????activeConnection.add(connection);
????????????????}?else?{
????????????????????????connection?=?getConnection();
????????????????}
????????}?else?{
????????????????System.out.println("當前連接數(shù)已達到最大連接數(shù)，等待"+dDbProperties.getRetryConnectionTimeOut()+"ms以后再試");
????????????????try?{
????????????????????????wait(dDbProperties.getRetryConnectionTimeOut());
????????????????}?catch?(InterruptedException?e)?{
????????????????????????e.printStackTrace();
????????????????}
????????????????connection?=?getConnection();
????????}
????????return?connection;
????}
????/**
?????*?@Description:使用可回收機制釋放連接：如果連接可用，并且空閑連接池沒有滿，則把連接歸還到空閑連接池，否則關閉連接
?????*/
????@Override
????public?synchronized?void?releaseConnection(Connection?connection)?{
????????try?{
????????????if?(isAvailable(connection)?&&?freeConnection.size()?????????????????????freeConnection.add(connection);
????????????????????System.out.println("空閑線程池未滿，歸還連接"+connection);
????????????}?else?{
????????????????????connection.close();
????????????????????System.out.println("空閑線程池已滿，關閉連接"+connection);
????????????}
????????????activeConnection.remove(connection);
????????????notifyAll();
????????}?catch?(Exception?e)?{
????????????????e.printStackTrace();
????????}
????}
}
演示代碼
創(chuàng)建20個線程，每個線程操作數(shù)據(jù)庫20次，每次操作往數(shù)據(jù)庫寫入一條數(shù)據(jù)。
創(chuàng)建表結構。
CREATE?TABLE?`test`?(
??`name`?varchar(255)?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?DEFAULT?NULL
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?COLLATE=utf8_bin;
public?class?Test001?{
????public?static?void?main(String[]?args)?{
????????ThreadConnection?threadConnection?=?new?ThreadConnection();
????????for?(int?i?=?0;?i?20;?i++)?{
????????????????Thread?thread?=?new?Thread(threadConnection,?"線程:"?+?i);
????????????????thread.start();
????????}
????}
}
class?ThreadConnection?implements?Runnable?{
????public?void?run()?{
????????for?(int?i?=?0;?i?20;?i++)?{
????????????Connection?connection?=?DbPoolManager.getConnection();
????????????System.out.println(Thread.currentThread().getName()?+?",connection:"?+?connection);
????????????Statement?statement;
????????????try?{
????????????????????statement?=?connection.createStatement();
????????????????????String?selectsql?=?"select?*?from?test";
????????????????????statement.execute(selectsql);
????????????????????String?insertsql?=?"insert?into?test(name)?VALUES('"+Thread.currentThread().getName()+connection+"')";
????????????????????statement.execute(insertsql);
????????????????????statement.close();
????????????}?catch?(SQLException?e)?{
????????????????????e.printStackTrace();
????????????}
????????????DbPoolManager.releaseConnection(connection);
????????}
????}
}
演示效果說明
如果每個連接都能正常工作，總共20個并發(fā)線程，每個線程執(zhí)行20次數(shù)據(jù)庫查詢，插入操作，執(zhí)行完畢后，數(shù)據(jù)庫應該有400條數(shù)據(jù)。
「初始化階段」
「達到最大連接數(shù)，進入重試階段」
「連接的獲取和歸還到空閑池」
「空閑連接池已滿，關閉連接」
至此，完畢！
評論
圖片
表情