线程安全的Mysql数据库连接池

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。

本文中数据库连接池采用单例模式，数据结构采用list，当然你也可以用queue等数据结构。

主要支持预分配连接，获得连接，释放连接，执行数据库语句等操作。

值得注意的是，释放连接是客户端释放不用的连接，归还给线程池，而不是线程池释放连接。

代码如下：

头文件：

#ifndef _MYSQL_POOL_H
#define _MYSQL_POOL_H

#include "mutex.h"
#include "condition.h"

#include <mysql/mysql.h>
#include <list>
#include <string>

namespace db {

class mysql_pool {
/*
*   date: 2016.11.29
*   function: a myconn connection pool which makes connection with mysql more effective.
*/
public:
    static mysql_pool* get_instance() {
        static mysql_pool instance;
        return &instance;
    }   
public:
    bool init(const char* host, const char* name, const char* passwd, 
                            const char* db, unsigned int port, const int num);
    bool select_db(MYSQL *conn, const char *db);
    
    MYSQL* get_connection();
    void   release_connection(MYSQL* conn);   

    MYSQL_RES* get_query(MYSQL *conn, const char* query);
    void release_query(MYSQL_RES* res);

    bool query(MYSQL* conn, const char* query);
    int  water_level() const;
private:
    mysql_pool();
    ~mysql_pool();
private:
    std::list<MYSQL*>         conn_list_;    //connection list
    mutable util::mutex_lock  mutex_;
};

}

#endif

cpp文件：

#include "mysql_pool.h"

using namespace db; 

mysql_pool::mysql_pool() 
    : mutex_() 
{
}

mysql_pool::~mysql_pool()
{
    while(conn_list_.size()){    //if the mysql connection free list not empty, release them
        mysql_close(conn_list_.front());
        conn_list_.pop_front();
    }   
    mysql_library_end();    
}

bool mysql_pool::init(const char* host, const char* name, const char* passwd, 
                            const char* db, unsigned int port, const int num)
{
    assert(num > 0); 
    
    for(int i=0; i<num; ++i){
        MYSQL* ms; 
        ms = mysql_init((MYSQL*)NULL);  //mysql_init: if the input param is NULL, "mysql" return an assigned object.
                                        //see also mysql_init(ms), it is another usage.
        assert(ms != NULL);

        if(mysql_real_connect(ms, host, name, passwd, db, port, NULL, 0)){
            conn_list_.push_back(ms);
        }   
        else{
            fprintf(stdout, "mysql pool init error.\n");
            return false;
        }   
    }   
    return true;
}

bool mysql_pool::select_db(MYSQL *conn, const char *db)
{
    return mysql_select_db(conn, db) ? false : true;    //if success mysql_select_db return 0
}

MYSQL* mysql_pool::get_connection()
{
    if(conn_list_.size()){
        util::mutex_lock_guard lock(mutex_);
        MYSQL *conn = conn_list_.front();
        conn_list_.pop_front();
        return conn;
    }
    return NULL;
}

//return the conection, like garbage collection.
void mysql_pool::release_connection(MYSQL* conn)
{
    util::mutex_lock_guard lock(mutex_);
    conn_list_.push_back(conn);    //push back
}

MYSQL_RES* mysql_pool::get_query(MYSQL *conn, const char* query)
{
    if(mysql_query(conn, query) == 0){    //if success mysql_query return 0.
         return mysql_store_result(conn);
    }
    else{
        std::cout<<"[mysql_pool.cpp]:mysql query error:"<<mysql_error(conn)<<std::endl;
        return NULL;
    }
}

void mysql_pool::release_query(MYSQL_RES* res)   //mysql_res release, avoid memory leak
{
    mysql_free_result(res);
}

bool mysql_pool::query(MYSQL* conn, const char* query)
{
    return mysql_query(conn, query) ? false : true;
}

int mysql_pool::water_level() const
{
    util::mutex_lock_guard lock(mutex_);
    return conn_list_.size();
}

下面是测试用例：

#include "mysql_pool.h"

#include <string>
#include <vector>

using namespace std;
using namespace db; 

int main()
{

    mysql_pool *mp = db::mysql_pool::get_instance();
    mp->init("127.0.0.1", "root", "vagrant", "smart_meter", 3306, 5); 

    MYSQL *conn = mp->get_connection();
        
    string id = "4747";
    string passwd = "9876";
    string name = "MaoZeDong";
    unsigned int degree = 100;
    char query[256];
    sprintf(query, "insert into smart_meter.electricity values ('%s', '%s', '%s', %d)", 
                   id.c_str(), passwd.c_str(), name.c_str(), degree);

    MYSQL_RES* res = mp->get_query(conn, query);
        
    int ret1 = mp->water_level();
    assert(ret1 == 4);     //use one, and left four

    string query2 = "select * from smart_meter.electricity";
    MYSQL_RES *res1 = mp->get_query(conn, query2.c_str());
    assert(res1 != NULL);
        
    MYSQL_ROW row;
    vector<vector<string> > final;
    while((row = mysql_fetch_row(res1))){
        vector<string> vec;
        for(int i=0; i<mysql_num_fields(res1); ++i)    //it is res1 no res !!!!
            vec.push_back(row[i]);
        final.push_back(vec);
    }   

    typedef vector<vector<string> >::iterator iterator1;
    typedef vector<string>::iterator iterator2;

    for(iterator1 iter=final.begin(); iter!=final.end(); ++iter){
        for(iterator2 it=(*iter).begin(); it!=(*iter).end(); ++it){
            std::cout<<*it<<std::endl;
        }
    }


    mp->release_connection(conn);    //release conn
    assert(mp->water_level() == 5);

    return 0;
}

这里有一点需要注意，例如INSERT语句不会返回结果集，所以insert后判断MYSQL_RES的指针是否为空是无用行为。

参考：http://www.open-open.com/lib/view/open1392943072997.html