[C++]MYSQL 数据库操作封装及连接池实现

发表于2017-08-06
评论0 3.6k浏览
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());
    }
}

如社区发表内容存在侵权行为,您可以点击这里查看侵权投诉指引

标签: