快捷導(dǎo)航

MySQL池化框架學(xué)習(xí)接池自定義

更新時(shí)間：2022年07月21日 11:51:41 作者：八音弦

這篇文章主要為大家介紹了MySQL連接池自定義示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

引言

最近在學(xué)習(xí)了通用池化框架commons-pool2實(shí)踐之后，再HTTP性能測(cè)試中進(jìn)行了實(shí)踐，結(jié)果出乎意料，對(duì)于性能提升沒(méi)啥卵用。經(jīng)過(guò)我自己的本地測(cè)試，性能也是足夠好的。

后來(lái)我仔細(xì)想了想，原來(lái)是我用錯(cuò)地方了。本來(lái)想自己寫(xiě)一個(gè)Redis的連接池的沒(méi)想到，jedis的連接池本身就是commons-pool2開(kāi)發(fā)的，讓我有點(diǎn)意外，看來(lái)想的是一樣的。commons-pool2用來(lái)做連接池是非常不錯(cuò)的。

我仔細(xì)找了找，發(fā)現(xiàn)還缺一個(gè)本地的MySQL連接池，而不是springboot那樣需要啟動(dòng)一個(gè)服務(wù)才行。當(dāng)然應(yīng)該也是有的，不過(guò)我非常想自己寫(xiě)一個(gè)然后進(jìn)行各類(lèi)測(cè)試，所以也沒(méi)有仔細(xì)找。

可池化對(duì)象

首先，我們需要一個(gè)可池化對(duì)象，這里我選用了com.funtester.db.mysql.FunMySql，這是一個(gè)我自己寫(xiě)的單鏈接的MySQL對(duì)象。我計(jì)劃用這個(gè)作為基礎(chǔ)可池化對(duì)象。

package?com.funtester.db.mysql;
import?com.funtester.base.interfaces.IMySqlBasic;
import?com.funtester.config.SqlConstant;
import?java.sql.Connection;
import?java.sql.ResultSet;
import?java.sql.Statement;
/**
?*?mysql操作的基礎(chǔ)類(lèi)
?*?<p>用于存儲(chǔ)數(shù)據(jù)，多用于爬蟲(chóng)</p>
?*/
public?class?FunMySql?extends?SqlBase?implements?IMySqlBasic?{
????/**
?????*??{@link?SqlConstant#FUN_SQL_URL}會(huì)替換IP到URL
?????*/
????String?url;
????/**
?????*?庫(kù)
?????*/
????String?database;
????/**
?????*?用戶
?????*/
????String?user;
????/**
?????*?密碼
?????*/
????String?password;
????Connection?connection;
????Statement?statement;
????/**
?????*?私有構(gòu)造方法
?????*
?????*?@param?url??????連接地址,包括端口
?????*?@param?database?庫(kù)
?????*?@param?user?????用戶名
?????*?@param?password?密碼
?????*/
????public?FunMySql(String?url,?String?database,?String?user,?String?password)?{
????????this.url?=?url;
????????this.database?=?database;
????????this.user?=?user;
????????this.password?=?password;
????????getConnection(database);
????}
????/**
?????*?初始化連接
?????*/
????@Override
????public?void?getConnection()?{
????????getConnection(EMPTY);
????}
????/**
?????*?執(zhí)行sql語(yǔ)句，非query語(yǔ)句，并不關(guān)閉連接
?????*
?????*?@param?sql
?????*/
????@Override
????public?void?executeUpdateSql(String?sql)?{
????????SqlBase.executeUpdateSql(connection,?statement,?sql);
????}
????/**
?????*?查詢功能
?????*
?????*?@param?sql
?????*?@return
?????*/
????@Override
????public?ResultSet?executeQuerySql(String?sql)?{
????????return?SqlBase.executeQuerySql(connection,?statement,?sql);
????}
????/**
?????*?關(guān)閉query連接
?????*/
????@Override
????public?void?over()?{
????????SqlBase.close(connection,?statement);
????}
????@Override
????public?void?getConnection(String?database)?{
????????if?(connection?==?null)
????????????connection?=?SqlBase.getConnection(SqlConstant.FUN_SQL_URL.replace("ip",?url).replace("database",?database),?user,?password);
????????if?(statement?==?null)?statement?=?SqlBase.getStatement(connection);
????}
}

池化工廠

相對(duì)連接，創(chuàng)建com.funtester.db.mysql.FunMySql的時(shí)候，順便一起初始化MySQL連接。然后再com.funtester.db.mysql.MysqlPool.FunTester#destroyObject的時(shí)候進(jìn)行連接的回收。

????/**
?????*?池化工廠類(lèi)
?????*/
????private?class?FunTester?extends?BasePooledObjectFactory<FunMySql>?{
????????@Override
????????FunMySql?create()?throws?Exception?{
????????????return?new?FunMySql(url,?database,?user,?password)
????????}
????????@Override
????????PooledObject<FunMySql>?wrap(FunMySql?obj)?{
????????????return?new?DefaultPooledObject<FunMySql>(obj)
????????}
????????@Override
????????void?destroyObject(PooledObject<FunMySql>?p)?throws?Exception?{
????????????p.getObject().over()
????????????super.destroyObject(p)
????????}
????}

對(duì)象池

這里顯得有些冗余，后面再使用過(guò)程中，我會(huì)繼續(xù)優(yōu)化。通過(guò)創(chuàng)建一個(gè)com.funtester.db.mysql.MysqlPool對(duì)象，獲取一個(gè)com.funtester.db.mysql.FunMySql對(duì)象池。

/**
?*?自定義MySQL連接池對(duì)象
?*/
class?MysqlPool?extends?PoolConstant?{
????private?static?final?Logger?logger?=?LogManager.getLogger(MysqlPool.class);
????/**
?????*?{@link?com.funtester.config.SqlConstant#FUN_SQL_URL}會(huì)替換IP到URL*/
????String?url;
????/**
?????*?庫(kù)
?????**/
????String?database;
????/**
?????*?用戶
?????**/
????String?user;
????/**
?????*?密碼
?????**/
????String?password;
????private?GenericObjectPool<FunMySql>?pool
????MysqlPool(String?url,?String?database,?String?user,?String?password)?{
????????this.url?=?url
????????this.database?=?database
????????this.user?=?user
????????this.password?=?password
????????init()
????}
????/**
?????*?初始化連接池
?????*?@return
?????*/
????def?init()?{
????????GenericObjectPoolConfig?poolConfig?=?new?GenericObjectPoolConfig();
????????poolConfig.setMaxTotal(MAX);
????????poolConfig.setMinIdle(MIN_IDLE);
????????poolConfig.setMaxIdle(MAX_IDLE);
????????poolConfig.setMaxWaitMillis(MAX_WAIT_TIME);
????????poolConfig.setMinEvictableIdleTimeMillis(MAX_IDLE_TIME);
????????pool?=?new?GenericObjectPool<FunMySql>(new?FunTester(),?poolConfig);
????}
}

API封裝

自從學(xué)習(xí)了Go語(yǔ)言的gorm框架和Redis框架，我發(fā)現(xiàn)其實(shí)不用把池化相關(guān)信息不用暴露出來(lái)，直接封裝原始的API，暴露給用戶使用，這樣用戶就不用關(guān)心連接的回收問(wèn)題了。

????/**
?????*?借出對(duì)象
?????*?@return
?????*/
????def?borrow()?{
????????try?{
????????????return?pool.borrowObject()
????????}?catch?(e)?{
????????????logger.warn("獲取${JSONObject.class}?失敗",?e)
????????}?finally?{
????????????new?JSONObject()
????????}
????}
????/**
?????*?歸還對(duì)象
?????*?@param?funMySql
?????*?@return
?????*/
????def?back(FunMySql?funMySql)?{
????????pool.returnObject(funMySql)
????}
????/**
?????*?執(zhí)行update?SQL
?????*?@param?sql
?????*?@return
?????*/
????def?execute(def?sql)?{
????????def?driver?=?borrow()
????????try?{
????????????driver.executeUpdateSql(sql)
????????}?catch?(e)?{
????????????logger.warn("執(zhí)行:{}失敗",?sql)
????????}?finally?{
????????????back(driver)
????????}
????}
????/**
?????*?執(zhí)行查詢SQL
?????*?@param?sql
?????*?@return
?????*/
????def?query(def?sql)?{
????????def?driver?=?borrow()
????????try?{
????????????return?driver.executeQuerySql(sql)
????????}?catch?(e)?{
????????????logger.warn("執(zhí)行:{}失敗",?sql)
????????}?finally?{
????????????back(driver)
????????}
????}

以上就是MySQL連接池自定義示例詳解的詳細(xì)內(nèi)容，更多關(guān)于MySQL連接池自定義的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: