今天心血来潮,想用C++封装一套通用的Mysql连接池,支持单线程和多线程并发调用,下面是我的分享。
连接池有3个部分组成分别是:1.MySQL连接实例;2.MySQL连接管理;3.操作MySQL客户端实例。
MySQL连接实例类命名为MysqlConn,包含了数据库连接,数据库重连,管理Statement和预处理PreparedStatement等功能。
MySQL连接管理类命名为MysqlConnMgr,管理所有的连接实例,对每个实例做定时检测,确保实例可用。提供实例获取和回收接口,并且支持多线程调用。
MySQL客户端实例有两个,MysqlClient支持普通的sql调用,MysqlPreClient支持预处理语句调用。构造函数和析构函数分别实现实例获取和回收的功能。另外客户端实例类负责查询结果的内存回收,简化外部接口的调用。
以下是整个连接池的源码,都是干货,可以随手拿去用吧。
MysqlApi.h文件:
#pragma once
#include <iostream>
#include <string>
#include <sstream>
#include <list>
#include <map>
#include <mutex>
#include <mysql_connection.h>
#include <mysql_driver.h>
#include <cppconn/exception.h>
#include <cppconn/driver.h>
#include <cppconn/connection.h>
#include <cppconn/resultset.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/statement.h>
namespace MysqlApi
{
typedef int IDType;
class MysqlClient;
class MysqlPreClient;
class MysqlConnMgr;
class MysqlConn
{
private:
MysqlConn(){}
~MysqlConn(){ FreeAll(); }
bool ConnectTo(const std::string& url, const std::string& user, const std::string& passwd);
bool Reconnect();
bool AddPreStatement(IDType id, const std::string& stmts);
sql::Connection* GetConn() { return m_conn; }
sql::Statement* GetStatement();
sql::PreparedStatement* GetPreStatement(IDType id);
uint64_t GetConnectedTime() const { return m_connectedTime; }
uint64_t GetTraceTime() const { return m_traceTime; }
int GetId() const { return m_id; }
void SetId(int id){ m_id = id; }
void FreeAll();
friend class MysqlClient;
friend class MysqlPreClient;
friend class MysqlConnMgr;
private:
int m_id;
std::string m_url;
std::string m_user;
std::string m_passwd;
uint64_t m_traceTime = 0;
uint64_t m_connectedTime = 0;
sql::Driver* m_driver = nullptr;
sql::Connection* m_conn = nullptr;
sql::Statement* m_statement = nullptr;
std::map<IDType, std::string> m_strs;
std::map<IDType, sql::PreparedStatement*> m_stmts;
};
class MysqlConnMgr
{
public:
static MysqlConnMgr& Instance() { static MysqlConnMgr obj; return obj; }
bool ConnectTo(IDType id, int conn_num, const std::string& url, const std::string& user,
const std::string& passwd, std::map<IDType, std::string>& stmts);
void CheckConns(uint64_t curTime=0);
void SetHasLock(bool hasLock) { m_hasLock = hasLock;}
~MysqlConnMgr();
private:
MysqlConnMgr();
MysqlConn* PopConn(IDType id);
void PushConn(IDType id, MysqlConn* conn);
friend class MysqlClient;
friend class MysqlPreClient;
struct LockData
{
LockData(bool valid, std::mutex& val):valid_val(valid),mutex_val(val)
{
if(valid_val){mutex_val.unlock();}
}
~LockData(){ if(valid_val){mutex_val.unlock();} }
bool valid_val = false;
std::mutex& mutex_val;
};
private:
std::mutex m_mutex;
bool m_hasLock = false;
uint64_t m_traceTime = 0;
std::map<IDType, std::list<MysqlConn*> > m_conns;
};
class MysqlClient
{
public:
MysqlClient(IDType id);
~MysqlClient();
bool Execute(const std::string& sql);
sql::ResultSet * ExecuteQuery(const std::string& sql);
int ExecuteUpdate(const std::string& sql);
uint64_t GetUpdateCount();
private:
IDType m_id;
MysqlConn* m_conn = nullptr;
sql::Statement* m_statement = nullptr;
std::list<sql::ResultSet*> m_resList;
};
class MysqlPreClient
{
public:
MysqlPreClient(IDType id, IDType preId);
~MysqlPreClient();
bool Execute();
sql::ResultSet *ExecuteQuery();
int ExecuteUpdate();
void ClearParameters();
void SetBigInt(const std::string& value);
void SetBlob(std::istream * blob);
void SetBoolean(bool value);
void SetDateTime(const std::string& value);
void SetDouble(double value);
void SetInt(int32_t value);
void SetUInt(uint32_t value);
void SetInt64(int64_t value);
void SetUInt64(uint64_t value);
void SetNull(int sqlType);
void SetString(const std::string& value);
private:
IDType m_id;
IDType m_preId;
unsigned int m_index = 0;
MysqlConn * m_conn = nullptr;
sql::PreparedStatement* m_preState = nullptr;
std::list<sql::ResultSet*> m_resList;
};
}
MysqlApi.cc文件:
#include "MysqlApi.h"
namespace MysqlApi
{
//
bool MysqlConn::ConnectTo(const std::string& url, const std::string& user, const std::string& passwd)
{
m_url = url;
m_user = user;
m_passwd = passwd;
m_driver = sql::mysql::get_driver_instance();
if (m_driver)
{
m_conn = m_driver->connect(m_url, m_user, m_passwd);
if (m_conn == nullptr)
{
//TODO LOG
return false;
}
m_statement = m_conn->createStatement();
m_connectedTime = (uint64_t)time(NULL);
return true;
}
else
{
//TODO LOG
return false;
}
}
bool MysqlConn::Reconnect()
{
FreeAll();
if ( ConnectTo(m_url, m_user, m_passwd) )
{
std::map<IDType, std::string> copy_strs;
copy_strs.swap(m_strs);
for(auto &it : copy_strs)
{
AddPreStatement(it.first, it.second);
}
return true;
}
return false;
}
bool MysqlConn::AddPreStatement(IDType id, const std::string& stmts)
{
sql::PreparedStatement* tVal = m_conn->prepareStatement(stmts);
if (!tVal)
{
//TODO LOG
return false;
}
auto it = m_stmts.find(id);
if (it != m_stmts.end())
{
delete it->second;
it->second = tVal;
}
else
{
m_stmts[id] = tVal;
}
m_strs[id] = stmts;
return true;
}
sql::Statement* MysqlConn::GetStatement()
{
m_traceTime = (uint64_t)time(NULL);
return m_statement;
}
sql::PreparedStatement* MysqlConn::GetPreStatement(IDType id)
{
auto it = m_stmts.find(id);
if (it != m_stmts.end())
{
it->second->clearParameters();
m_traceTime = time(NULL);
return it->second;
}
return nullptr;
}
void MysqlConn::FreeAll()
{
for(auto &it : m_stmts)
{
delete it.second;
it.second = nullptr;
}
m_stmts.clear();
if (m_statement)
{
delete m_statement;
m_statement = nullptr;
}
if (m_conn)
{
m_conn->close();
delete m_conn;
m_conn = nullptr;
}
}
///
MysqlConnMgr::~MysqlConnMgr()
{
LockData temp_lock(m_hasLock, m_mutex);
for(auto &it : m_conns)
{
for (auto it_list = it.second.begin(); it_list != it.second.end(); it_list++)
{
delete (*it_list);
(*it_list) = nullptr;
}
it.second.clear();
}
m_conns.clear();
}
MysqlConnMgr::MysqlConnMgr()
{
m_traceTime = (uint64_t)time(NULL);
}
bool MysqlConnMgr::ConnectTo(IDType id, int conn_num, const std::string& url, const std::string& user,
const std::string& passwd, std::map<IDType, std::string>& stmts)
{
for(int i = 0; i < conn_num; i++)
{
MysqlConn* newConn = new MysqlConn();
if ( !newConn->ConnectTo(url, user, passwd) )
{
delete newConn;
newConn = nullptr;
return false;
}
for(auto &it : stmts)
{
if( !newConn->AddPreStatement(it.first, it.second) )
{
delete newConn;
newConn = nullptr;
return false;
}
}
newConn->SetId(i + 1);
LockData temp_lock(m_hasLock, m_mutex);
std::list<MysqlConn*>& list_data = m_conns[id];
list_data.push_back(newConn);
}
return true;
}
MysqlConn* MysqlConnMgr::PopConn(IDType id)
{
LockData temp_lock(m_hasLock, m_mutex);
auto it = m_conns.find(id);
if( it != m_conns.end() )
{
if( !it->second.empty())
{
MysqlConn* conn = it->second.front();
it->second.pop_front();
return conn;
}
}
return nullptr;
}
void MysqlConnMgr::PushConn(IDType id, MysqlConn* conn)
{
if(!conn){
return;
}
LockData temp_lock(m_hasLock, m_mutex);
auto it = m_conns.find(id);
if( it != m_conns.end() )
{
for (auto it_list = it->second.begin(); it_list != it->second.end(); it_list++)
{
if( conn->GetId() == (*it_list)->GetId() ){
return;
}
}
it->second.push_back(conn);
}
else
{
delete conn;
conn = nullptr;
}
}
void MysqlConnMgr::CheckConns(uint64_t curTime)
{
if (0 == curTime)
{
curTime = (uint64_t)time(NULL);
}
if(m_traceTime + 60 > curTime)
{
return;
}
m_traceTime = curTime;
static uint64_t check_reconnect_time = 3600 * 7;
LockData temp_lock(m_hasLock, m_mutex);
for(auto it = m_conns.begin(); it != m_conns.end(); it++)
{
for(auto it_list = it->second.begin(); it_list != it->second.end(); it_list++)
{
if( (*it_list)->GetTraceTime() + check_reconnect_time < m_traceTime)
{
(*it_list)->Reconnect();
}
}
}
}
/
MysqlClient::MysqlClient(IDType id)
{
m_id = id;
m_conn = MysqlConnMgr::Instance().PopConn(m_id);
if (m_conn)
{
m_statement = m_conn->GetStatement();
}
}
MysqlClient::~MysqlClient()
{
for (auto it = m_resList.begin(); it != m_resList.end(); it++)
{
delete (*it);
(*it) = nullptr;
}
m_resList.clear();
if(m_conn)
{
MysqlConnMgr::Instance().PushConn(m_id, m_conn);
}
}
bool MysqlClient::Execute(const std::string& sql)
{
if (m_statement)
{
return m_statement->execute(sql);
}
return false;
}
sql::ResultSet* MysqlClient::ExecuteQuery(const std::string& sql)
{
if (m_statement)
{
sql::ResultSet* retRes = m_statement->executeQuery(sql);
if (retRes)
{
m_resList.push_back(retRes);
}
return retRes;
}
return nullptr;
}
int MysqlClient::ExecuteUpdate(const std::string& sql)
{
if (m_statement)
{
return m_statement->executeUpdate(sql);
}
return -1;
}
uint64_t MysqlClient::GetUpdateCount()
{
if (m_statement)
{
return m_statement->getUpdateCount();
}
return 0;
}
/
MysqlPreClient::MysqlPreClient(IDType id, IDType preId)
{
m_id = id;
m_preId = preId;
m_conn = MysqlConnMgr::Instance().PopConn(m_id);
if (m_conn)
{
m_preState = m_conn->GetPreStatement(m_preId);
if(m_preState)
{
ClearParameters();
}
}
}
MysqlPreClient::~MysqlPreClient()
{
for (auto it = m_resList.begin(); it != m_resList.end(); it++)
{
delete (*it);
(*it) = nullptr;
}
m_resList.clear();
if(m_conn)
{
MysqlConnMgr::Instance().PushConn(m_id, m_conn);
}
}
void MysqlPreClient::ClearParameters()
{
m_index = 0;
m_preState->clearParameters();
}
bool MysqlPreClient::Execute()
{
if (m_preState)
{
return m_preState->execute();
}
return false;
}
sql::ResultSet *MysqlPreClient::ExecuteQuery()
{
if (m_preState)
{
sql::ResultSet* retRes = m_preState->executeQuery();
if (retRes)
{
m_resList.push_back(retRes);
}
return retRes;
}
return nullptr;
}
int MysqlPreClient::ExecuteUpdate()
{
if (m_preState)
{
return m_preState->executeUpdate();
}
return -1;
}
void MysqlPreClient::SetBigInt(const std::string& value)
{
if (m_preState)
{
m_preState->setBigInt(m_index++, value);
}
}
void MysqlPreClient::SetBlob(std::istream * blob)
{
if (m_preState)
{
m_preState->setBlob(m_index++, blob);
}
}
void MysqlPreClient::SetBoolean(bool value)
{
if (m_preState)
{
m_preState->setBoolean(m_index++, value);
}
}
void MysqlPreClient::SetDateTime(const std::string& value)
{
if (m_preState)
{
m_preState->setDateTime(m_index++, value);
}
}
void MysqlPreClient::SetDouble(double value)
{
if (m_preState)
{
m_preState->setDouble(m_index++, value);
}
}
void MysqlPreClient::SetInt(int32_t value)
{
if (m_preState)
{
m_preState->setInt(m_index++, value);
}
}
void MysqlPreClient::SetUInt(uint32_t value)
{
if (m_preState)
{
m_preState->setUInt(m_index++, value);
}
}
void MysqlPreClient::SetInt64(int64_t value)
{
if (m_preState)
{
m_preState->setInt64(m_index++, value);
}
}
void MysqlPreClient::SetUInt64(uint64_t value)
{
if (m_preState)
{
m_preState->setUInt64(m_index++, value);
}
}
void MysqlPreClient::SetNull(int sqlType)
{
if (m_preState)
{
m_preState->setNull(m_index++, sqlType);
}
}
void MysqlPreClient::SetString(const std::string& value)
{
if (m_preState)
{
m_preState->setString(m_index++, value);
}
}
}