同步数据库连接池实现(一)

1.介绍

        本文是池式结构的最后一舞，数据连接池，顾名思义数据库连接池是一个容器，用于存放和管理数据库连接对象，它能有效减少数据库连接次数，在高并发场景下能够显著提高相应性能。

        本文实现的同步数据库连接池，它适用于服务器启动或者资源初始化。 

2.MySQL连接 

        在实现数据库连接池前，我们先把mysql连接进行抽象，方边后续同步连接池与异步连接池的实现，代码如下：

// MysqlConnection.h
#pragma once

#include <string>
#include <mutex>
#include <memory>
#include <mysql/mysql.h>

typedef MYSQL*       MysqlHandle;
typedef MYSQL_RES*   MysqlResult;
typedef MYSQL_ROW    MysqlRow;
typedef MYSQL_FIELD* MysqlField;


struct MysqlConnectionInfo {
    explicit MysqlConnectionInfo(std::string const& infoString);

    std::string host;
    std::string port;
    std::string user;
    std::string pass;
    std::string database;
};

class MysqlConnect {
public:
    MysqlConnect(MysqlConnectionInfo const& info);
    ~MysqlConnect();

    bool Open();
    std::string Execute(std::string const& sql);
    bool LockIfReady();
    void Unlock();

private:
    void Close();

private:
    MysqlConnectionInfo  _connectionInfo;
    MysqlHandle          _MysqlHandle;
    std::mutex           _Mutex;
};

// MysqlConnecttion.cpp
#include "MysqlConnection.h"

#include <mysql/mysql.h>
#include <vector>

static std::vector<std::string_view> Tokenize(std::string_view str, char sep, bool keepEmpty)
    {
        std::vector<std::string_view> tokens;

        size_t start = 0;
        for (size_t end = str.find(sep); end != std::string_view::npos; end = str.find(sep, start))
        {
            if (keepEmpty || (start < end))
                tokens.push_back(str.substr(start, end - start));
            start = end+1;
        }

        if (keepEmpty || (start < str.length()))
            tokens.push_back(str.substr(start));

        return tokens;
    }

MysqlConnectionInfo::MysqlConnectionInfo(std::string const& infoString) {
     std::vector<std::string_view> tokens = Tokenize(infoString, ';', true);

    if (tokens.size() != 5 && tokens.size() != 6)
        return;

    host.assign(tokens[0]);
    port.assign(tokens[1]);
    user.assign(tokens[2]);
    pass.assign(tokens[3]);
    database.assign(tokens[4]);
 }

MysqlConnect::MysqlConnect(MysqlConnectionInfo const& infoString) : _connectionInfo(infoString), _MysqlHandle(nullptr) {

}

MysqlConnect::~MysqlConnect() {
    Close();
}

bool MysqlConnect::Open() {
    MYSQL *mysqlInit = nullptr;
    mysqlInit = mysql_init(nullptr);
    if (!mysqlInit)
    {
        return false;
    }

    _MysqlHandle = mysql_real_connect(mysqlInit, _connectionInfo.host.c_str(), _connectionInfo.user.c_str(), _connectionInfo.pass.c_str(), _connectionInfo.database.c_str(), 3306, nullptr, 0);
    if(!_MysqlHandle) {
        return false;
    }

    return true;
}

std::string MysqlConnect::Execute(const std::string &sql) {
    std::string strResult = "";
    if(sql.empty()) {
        return strResult;
    }

    if(mysql_query(_MysqlHandle, sql.c_str())) {
        return strResult;
    }

    MysqlResult Res = mysql_store_result(_MysqlHandle);
    int numFields = mysql_num_fields(Res);
    MysqlRow Row = nullptr;
    while ((Row = mysql_fetch_row(Res))) {
        for(int i = 0; i < numFields; i++) {
            strResult += ((char*)Row[i] ? (char*)Row[i] : "NULL");
            strResult += " ";
        }
    }

    return strResult;
}

bool MysqlConnect::LockIfReady() {
    return _Mutex.try_lock();
}

void MysqlConnect::Unlock() {
    _Mutex.unlock();
}

void MysqlConnect::Close() {
    if(_MysqlHandle) {
        mysql_close(_MysqlHandle);
    }
}

3.同步数据库连接池实现

        对于同步数据库数据库连接池，用户会直接获取数据库应答，它描述了当前最多允许几个线程使用连接，结构如下图：

        实现代码如下：

// DatabasePool.h
#pragma once

#include <string>
#include <memory>
#include <vector>
#include <array>

struct MysqlConnectionInfo;

template <typename T>
class DatabasePool {
private:
    enum InternalIndex {
            IDX_ASYNC,
            IDX_SYNCH,
            IDX_SIZE
        };
public:
    DatabasePool();
    ~DatabasePool();

    void SetConnectionInfo(std::string const& infoString, uint8_t const asyncThreads, uint8_t const synchThreads);

    bool Open();

    std::string Query(char const* sql);

private:
    bool OpenConnections(InternalIndex type, uint8_t numConnections);

    T*   GetFreeConnection();

private:
    uint8_t _asyncThreads;
    uint8_t _synchThreads;

    std::unique_ptr<MysqlConnectionInfo>                   _connectionInfo;
    std::array<std::vector<std::unique_ptr<T>>, IDX_SIZE>  _connections;   
};

// DatabasePool.cpp
#include "DatabasePool.h"
#include "MysqlConnection.h"

template <class T>
DatabasePool<T>::DatabasePool() {

}

template <class T>
DatabasePool<T>::~DatabasePool() {

}

template <class T>
void DatabasePool<T>::SetConnectionInfo(const std::string &infoString, const uint8_t asyncThreads, const uint8_t synchThreads) {
    _connectionInfo = std::make_unique<MysqlConnectionInfo>(infoString);
    _asyncThreads = asyncThreads;
    _synchThreads = synchThreads;
}

template <class T>
bool DatabasePool<T>::Open()
{
    if(!OpenConnections(IDX_ASYNC, _asyncThreads)) {
        return false;
    }

    if(!OpenConnections(IDX_SYNCH, _synchThreads)) {
        return false;
    }
	return true;
}

template <class T>
std::string DatabasePool<T>::Query(char const* sql) {
    auto connection = GetFreeConnection();
    auto ret = connection->Execute(sql);
    connection->Unlock();
    return ret;

}

template <class T>
bool DatabasePool<T>::OpenConnections(InternalIndex type, uint8_t numConnections) {
    for(uint8_t i = 0; i < numConnections; i++) {
        auto connection = [&] {
            switch (type) {
                case IDX_ASYNC:
                    //TODO:后续增加异步连接池
                    return std::make_unique<T>(*_connectionInfo);
                case IDX_SYNCH:
                    return std::make_unique<T>(*_connectionInfo);
                default:
                    exit(0);
            }
        }();

        if(!connection) {
            return false;
        }

        if(connection->Open()) {
            _connections[type].push_back(std::move(connection));
        }
    }

    return true;
}

template <class T>
T* DatabasePool<T>::GetFreeConnection() {
    int ConnSize = _connections[IDX_SYNCH].size();
    int i = 0;
    T* connection = nullptr;
    while(true) {
        connection = _connections[IDX_SYNCH][++i % ConnSize].get();
        if (connection->LockIfReady()) {
            break;
        }           
    }

    return connection;
}

template class DatabasePool<MysqlConnect>;

         ok,到这基本框架就已经搭好，现在来实现一个demo测试看看

// main.cpp
#include "DatabasePool.h"
#include "MysqlConnection.h"
#include <string>
#include <iostream>

template class DatabasePool<MysqlConnect>; 

int main() {
    DatabasePool<MysqlConnect> pool;   
    pool.SetConnectionInfo("127.0.0.1;3306;oyj;123456;test", 1, 1);
    pool.Open();

    std::string strResult = pool.Query("select * from employees");
    std::cout << strResult << std::endl;
}

        编译运行看结果：

        参考学习： 0voice · GitHub