[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()); } }