[C++]MYSQL 数据库操作封装及连接池实现
发表于2017-08-06
Database类为单例类、线程安全、实现了连接池，并且封装所需要的操作。
本代码在Ubuntu下测试可用，使用MySQL connector c 连接数据库，并启用C 11特性，如果对MYSQL 数据库操作封装及连接池实现感兴趣的同学可以看看。
相关依赖库下载地址：https://dev.mysql.com/downloads/connector/cpp/
基本操作如下：
//数据库配置
DbSetting setting;
setting.host = "tcp://127.0.0.1:3306/dbname";
setting.username = "root";
setting.password = "root";
setting.connpoolsize = 100;//连接池最大大小
setting.reconnmaxsize = 3;//重连次数
//初始化
Database::Create(setting);
//调用,如果有错误会抛出异常
try
｛
//调用封装好的数据库操作
Database::GetInstance()->AddUser("deviceid","username");
｝catch(const exception& e)
｛//异常处理
｝
//销毁
Database::Destory();
干货如下：
头文件：
#ifndef DATABASE_H
#define DATABASE_H
//base
# include <iostream>
# include <pplx/pplx.h>
# include <memory>
# include <map>
# include <functional>
# include <list>
# include <string>
# include <sstream>
//mysql
# include <cppconn/connection.h>
# include <cppconn/driver.h>
# include <cppconn/statement.h>
# include <cppconn/prepared_statement.h>
# include <cppconn/metadata.h>
# include <cppconn/exception.h>
using namespace std;
using namespace sql;
//数据库配置
struct DbSetting
｛
string host;
string username;
string password;
int connpoolsize;//连接池最大大小
int reconnmaxsize;//重连次数
｝;
class Database
: public enable_shared_from_this<Database>
｛
protected:
//单例
static shared_ptr<Database> m_database;
public:
//创建
static void Create(DbSetting setting);
//销毁
static void Destory();
//获取实例
static inline shared_ptr<Database> GetInstance()｛return m_database;｝
public:
Database(DbSetting setting);
~Database();
private:
mutex m_mtDriver;
mutex m_mtPool;
DbSetting m_setting;
Driver* m_driver;
//未使用的连接
list<Connection*> m_disableConn;
//正在使用的连接
list<Connection*> m_enableConn;
private:
//初始化
void Init();
//获取一个连接
Connection* Get();
//释放一个连接
void Release(Connection*& conn);
//创建一个连接
Connection* Create();
//销毁一个连接
void Destory(Connection*& conn);
//销毁所有连接
void DestoryAll();
public:
//封装的数据库操作，对于该数据库的操作，都可按照此方法来实现
void AddUser(string deviceid, string username);
｝;
#endif // DATABASE_H
源文件：
#include "database.h"
shared_ptr<Database> Database::m_database = nullptr;
void Database::Create(DbSetting setting)
｛
try
｛
m_database = make_shared<Database>(setting);
m_database->Init();
｝
catch (const exception& e)
｛
std::stringstream ostr;
ostr << "[db]" << e.what();
throw runtime_error(ostr.str());
｝
｝
void Database::Destory()
｛
m_database.reset();
｝
Database::Database(DbSetting setting)
｛
//get setting
m_setting = setting;
｝
Database::~Database()
｛
DestoryAll();
｝
void Database::Init()
｛
unique_lock<std::mutex> lck(m_mtDriver);
m_driver = get_driver_instance();
｝
Connection *Database::Get()
｛
unique_lock<std::mutex> lck(m_mtPool);
Connection* conn = nullptr;
try
｛
//当前重连次数
int reconnCnt = 0;
do
｛
if (0 < m_disableConn.size())
｛//存在未使用的连接
conn = m_disableConn.front();//获取
m_disableConn.pop_front();//从未使用连接的池中移除
｝
else
｛//没有未使用的连接
conn = Create();//创建一个连接
｝
if (nullptr != conn && conn->isValid())
｛//连接有效
m_enableConn.push_back(conn);//放入正在使用连接的池中
break;
｝
//连接无效，销毁
Destory(conn);
//重连次数增加
reconnCnt++;
｝ while (reconnCnt < m_setting.reconnmaxsize);//判断是否在可重连次数范围内
if (nullptr == conn)
｛//connection is invaild excption
//获取到的连接无效，则抛出异常
throw runtime_error("[db]connection is invaild.");
｝
｝
catch (const exception& e)
｛
//销毁连接
Destory(conn);
std::stringstream ostr;
ostr << "[db]" << e.what();
throw runtime_error(ostr.str());
｝
//返回连接
return conn;
｝
void Database::Release(Connection *&conn)
｛
unique_lock<std::mutex> lck(m_mtPool);
if(nullptr == conn)
｛//连接不为空
return;
｝
if(m_disableConn.size() >= m_setting.connpoolsize)
｛//未使用连接已达连接池上限，则删除
Destory(conn);
return;
｝
if(!conn->isValid())
｛//连接无效，则删除
Destory(conn);
return;
｝
//否则放入未使用连接的池中
m_disableConn.push_back(conn);
｝
Connection *Database::Create()
｛
unique_lock<std::mutex> lck(m_mtDriver);
Connection* conn = nullptr;
try
｛
//创建一个连接
conn = m_driver->connect(m_setting.host, m_setting.username, m_setting.password);
｝
catch (const exception& e)
｛
std::stringstream ostr;
ostr << "[db]" << e.what();
throw runtime_error(ostr.str());
｝
return conn;
｝
void Database::Destory(Connection *&conn)
｛//销毁一个连接
if (nullptr == conn)
｛
return;
｝
delete(conn);
conn = nullptr;
｝
void Database::DestoryAll()
｛//销毁所有连接
unique_lock<std::mutex> lck(m_mtPool);
//销毁未使用连接的池
for(Connection*& conn : m_disableConn)
｛
Destory(conn);
｝
//销毁正在使用连接的池
for(Connection*& conn : m_enableConn)
｛
Destory(conn);
｝
｝
//db operations =====================================================================
//数据库操作范例
void Database::AddUser(string deviceid, string username)
｛
try
｛
shared_ptr<Connection> conn(Get(), [this](Connection* ptr)｛Release(ptr);｝);
shared_ptr<PreparedStatement> stmt;
shared_ptr<ResultSet> res;
stmt.reset(conn->prepareStatement(
"call adduser(?,?)"));
stmt->setString(1, deviceid.c_str());
stmt->setString(2, username.c_str());
res.reset(stmt->executeQuery());
if (1 < res->rowsCount())
｛
throw new runtime_error("call adduser result is more than 1.");
｝
for (;;)
｛
while (res->next()) ｛
int errcode = res->getInt("errcode");
if (errcode != 0)
｛
std::stringstream ostr;
ostr << "call adduser error code : " << errcode;
throw runtime_error(ostr.str());
｝
return;
｝
if (stmt->getMoreResults())
｛
res.reset(stmt->getResultSet());
continue;
｝
break;
｝
｝
catch (const exception& e)
｛
std::stringstream ostr;
ostr << "[db]" << e.what();
throw runtime_error(ostr.str());
｝
｝