数据库连接池范例

在有大量节点访问的数据库设计中,经常要使用到连接池来管理所有的连接.
一般方法是:建立两个连接句柄队列,空闲的等待使用的队列和正在使用的队列.
当要查询时先从空闲队列中获取一个句柄,插入到正在使用的队列,再用这个句柄做数据库操作,完毕后一定要从使用队列中删除,再插入到空闲队列.
代码如下：

MySQLMan.h
// MySQLMan.h: interface for the CMySQLMan class.
//
//////////////////////////////////////////////////////////////////////
#include <mysql.h>
#pragma comment(lib,"libmySQL.lib")

#include <list>

typedef std::list<MYSQL *> CONNECTION_HANDLE_LIST;
typedef std::list<MYSQL *>::iterator ITER_CONNECTION_HANDLE_LIST;

#define CONNECTION_NUM 10 //同时打开的连接数

class CMySQLMan 
{
public:
CMySQLMan();
CMySQLMan(const char *host, const char *user, const char *password, const char *db, unsigned int port=3306);
virtual ~CMySQLMan();

public:
bool ConnectDB();       //连接数据库
MYSQL_RES* SelectRecord(const char *szSql); //选择记录，返回结果集
bool SelectDB(const char *szDB);   //选择数据库
bool UpdateRecord(const char *szSql); //更新记录
bool InsertRecord(const char *szSql); //插入记录
bool DelRecord(const char *szSql);   //删除记录

BOOL IsEnd(MYSQL_RES *myquery);        //是否最后
void SeekData(MYSQL_RES *myquery, int offset);     //查找指定数据
void FreeRecord(MYSQL_RES *myquery);       //释放结果集
unsigned int GetFieldNum(MYSQL_RES *myquery);     //得到字段数
MYSQL_ROW GetRecord(MYSQL_RES *myquery);      //得到结果（一个记录）
my_ulonglong GetRowNum(MYSQL_RES *myquery);     //得到记录数
char* OutErrors(MYSQL* pMySql);       //输出错误信息

char* GetState();       //服务器状态
char* GetServerInfo();      //服务器信息
int GetProtocolInfo();      //协议信息
char* GetHostInfo();      //主机信息
char* GetClientInfo();      //客户机信息
char* GetFieldName(MYSQL_RES *myquery, int FieldNum);   //字段名

bool LockTable(const char *TableName, const char *Priority); //对特定表加锁
bool UnlockTable();       //解锁
bool SetCharset();
//int CreateDB(char *db);     //创建数据库，返回错误信息
//int DropDB(char *db);      //删除数据库,返回错误信息

MYSQL* GetIdleMySql();      //提取一个空闲句柄供使用
void SetIdleMysql(MYSQL* pMySql);   //从使用队列中释放一个使用完毕的句柄，插入到空闲队列

public:
//MYSQL   m_mysql;       //数据库连接句柄
MYSQL_ROW m_row;        //记录集(单行)
MYSQL_FIELD *m_field;       //字段信息（结构体）

//创建两个队列
CONNECTION_HANDLE_LIST m_lsBusyList;                //正在使用的连接句柄
CONNECTION_HANDLE_LIST m_lsIdleList;                //未使用的连接句柄

CRITICAL_SECTION m_csList;

public:
char m_host[20];    //主机
char m_user[20];    //用户名
char m_password[20];   //密码
char m_db[20];     //数据库名
unsigned int m_port;   //端口
};

MySQLMan.cpp
// MySQLMan.cpp: implementation of the MySQLMan class.
//
//////////////////////////////////////////////////////////////////////
#include "StdAfx.h"
#include "MySQLMan.h"

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CMySQLMan::CMySQLMan()
{

}

CMySQLMan::CMySQLMan(const char *host, const char *user, const char *password, const char *db, unsigned int port/* =3306 */)
{
strcpy(m_host, host);
strcpy(m_user, user);
strcpy(m_password, password);
strcpy(m_db, db);
m_port = port;

InitializeCriticalSection(&m_csList);
}

CMySQLMan::~CMySQLMan()
{
for (ITER_CONNECTION_HANDLE_LIST iter=m_lsBusyList.begin(); iter != m_lsBusyList.end(); iter++)
{
   mysql_close((*iter));
}

for (ITER_CONNECTION_HANDLE_LIST iter=m_lsIdleList.begin(); iter != m_lsIdleList.end(); iter++)
{
   mysql_close((*iter));
}

DeleteCriticalSection(&m_csList);
}

bool CMySQLMan::ConnectDB()
{
//同时打开CONNECTION_NUM个连接
try
{
   for (int i=0; i<CONNECTION_NUM; ++i)
   {
    MYSQL *pMySql = mysql_init((MYSQL*)NULL);
    if (pMySql != NULL)
    {
     if (!mysql_real_connect(pMySql,m_host,m_user,m_password,m_db,m_port,NULL,0))
     {
      OutErrors(pMySql);
      return false;
     }
     m_lsIdleList.push_back(pMySql);
    }
   }
}
catch (...)
{
   return false;
}
return true;
}

MYSQL* CMySQLMan::GetIdleMySql()
{
MYSQL* pMySql = NULL;
EnterCriticalSection(&m_csList);
if (m_lsIdleList.size() > 0)
{
   pMySql = m_lsIdleList.front();
   m_lsIdleList.pop_front();
   m_lsBusyList.push_back(pMySql);
}
else
{
   pMySql = NULL;
}
LeaveCriticalSection(&m_csList);

return pMySql;
}

void CMySQLMan::SetIdleMysql(MYSQL* pMySql)
{
EnterCriticalSection(&m_csList);
m_lsBusyList.remove(pMySql);
m_lsIdleList.push_back(pMySql);
LeaveCriticalSection(&m_csList);
}

MYSQL_RES* CMySQLMan::SelectRecord(const char *szSql)
{
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return NULL;
}
if(mysql_query(pMySql,szSql))
   return NULL;
MYSQL_RES *myquery = NULL;
myquery = mysql_store_result(pMySql);
SetIdleMysql(pMySql);

return myquery;
}

bool CMySQLMan::InsertRecord(const char *szSql)
{
bool bRet = false;
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return false;
}
if(mysql_query(pMySql,szSql))
{
   bRet = true;
}
SetIdleMysql(pMySql);

return bRet;
}

bool CMySQLMan::UpdateRecord(const char *szSql)
{
bool bRet = false;
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return false;
}
if(mysql_query(pMySql,szSql))
{
   bRet = true;
}
SetIdleMysql(pMySql);

return bRet;
}

bool CMySQLMan::DelRecord(const char *szSql)
{
bool bRet = false;
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return false;
}
if(mysql_query(pMySql,szSql))
{
   bRet = true;
}
SetIdleMysql(pMySql);

return bRet;
}

bool CMySQLMan::SelectDB(const char *szDB)
{
bool bRet = false;
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return false;
}
if (mysql_select_db(pMySql,szDB))
   bRet = false; 
else
   bRet = true;
SetIdleMysql(pMySql);

return bRet;
}

my_ulonglong CMySQLMan::GetRowNum(MYSQL_RES *myquery)
{
return mysql_num_rows(myquery);
}

MYSQL_ROW CMySQLMan::GetRecord(MYSQL_RES *myquery)
{
m_row = mysql_fetch_row(myquery);

return m_row;
}

unsigned int CMySQLMan::GetFieldNum(MYSQL_RES *myquery)
{
return mysql_num_fields(myquery);
}

void CMySQLMan::FreeRecord(MYSQL_RES *myquery)
{
mysql_free_result(myquery);
}

//int CMySQLMan::CreateDB(char *db)
//{
// return mysql_create_db(&m_mysql,db);
//}

void CMySQLMan::SeekData(MYSQL_RES *myquery, int offset)
{
mysql_data_seek(myquery,offset);
}


char * CMySQLMan::OutErrors(MYSQL *pMySql)
{
return const_cast<char *>(mysql_error(pMySql));
}

BOOL CMySQLMan::IsEnd(MYSQL_RES *myquery)
{
return mysql_eof(myquery);
}

char* CMySQLMan::GetFieldName(MYSQL_RES *myquery, int FieldNum)
{
m_field = mysql_fetch_field_direct(myquery, FieldNum);

return m_field->name;
}

char * CMySQLMan::GetClientInfo()
{
return const_cast<char *>(mysql_get_client_info());
}

char* CMySQLMan::GetHostInfo()
{
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return NULL;
}
return const_cast<char *>(mysql_get_host_info(pMySql));
}

int CMySQLMan::GetProtocolInfo()
{
int iRet = 0;
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return NULL;
}
iRet = mysql_get_proto_info(pMySql);
SetIdleMysql(pMySql);

return iRet;
}

char* CMySQLMan::GetServerInfo()
{
static char szRet[1024];
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return NULL;
}
_tcscpy(szRet, const_cast<char *>(mysql_get_server_info(pMySql)));
SetIdleMysql(pMySql);

return szRet;
}

char* CMySQLMan::GetState()
{
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return NULL;
}
static char szRet[1024];
_tcscpy(szRet,const_cast<char *>(mysql_stat(pMySql)));
SetIdleMysql(pMySql);

return szRet;
}

bool CMySQLMan::SetCharset()
{
bool bRet = false;
char szSql[50];
strcpy(szSql, "set names gb2312");
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return false;
}
if (mysql_query(pMySql, szSql))
   bRet = true;
SetIdleMysql(pMySql);

return bRet;
}

//LOCK TABLES tbl1 READ, tbl2 WRITE
bool CMySQLMan::LockTable(const char *TableName, const char *Priority)
{
bool bRet = false;
char szSql[50];
sprintf(szSql, "LOCK TABLES %s %s", TableName, Priority);
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return false;
}
if (mysql_query(pMySql, szSql))
   bRet = true;
SetIdleMysql(pMySql);

return bRet;
}

bool CMySQLMan::UnlockTable()
{
bool bRet = false;
MYSQL *pMySql = GetIdleMySql();
if (pMySql == NULL)
{
   return false;
}
if(mysql_query(pMySql,"UNLOCK TABLES"))
   bRet = true;
SetIdleMysql(pMySql);

return bRet;

}

 

实现是基于mysql connector C++ api的

connpool.h文件

**
  *数据库连接池(单例模式)
  *
  *@authour libingxiang
  *
  *@email lbxthinker@gmail.com
  **/
#ifndef CONN_POOL_H
#define CONN_POOL_H

#include <mysql_connection.h>
#include <mysql_driver.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include "push.h"
#include "mutex.h"
    
using namespace std;

class ConnPool{
    private:
        deque<sql::Connection *> conns;//连接队列
        int curSize;//当前队列中路连接数目
        int maxSize;//最大连接数目
        sql::ConnectOptionsMap connectionProperties;
        Mutex *lock;//连接队列互斥锁
        static ConnPool * connPool;
        sql::Driver * driver;//mysql connector C++ driver
        sql::Connection * CreateConnection();//创建一个连接
        void TerminateConnection(sql::Connection * conn);//终止一个连接
        void Init(int initialSize);//初始化连接池
        void Destroy();//销毁连接池
    protected:
        ConnPool(string host,string user,string password,int maxSize);
    public:
        ~ConnPool();
        sql::Connection * GetConnection();//获取一个连接
        void ReleaseConnection(sql::Connection * conn);//释放一个连接
        sql::Connection * GetConnectionTry(int maxNum);//GetConnection的加强版，maxNum代表重试次数
        static ConnPool * GetInstance();//获取一个ConnPool对象实例
};

#endif
   

connpool.cpp文件

/**
  *数据库连接池
  *
  *@authour libingxiang
  *
  *@email lbxthinker@gmail.com
  **/
#include <stdexcept>
#include "connpool.h"
#include "config.h"
            
using namespace std;
extern Config *config;

ConnPool * ConnPool::connPool = NULL;

ConnPool::ConnPool(string host,string user,string password,int maxSize){
    connectionProperties["hostName"] = host;
    connectionProperties["userName"] = user;
    connectionProperties["password"] = password;
    connectionProperties["OPT_CONNECT_TIMEOUT"] = 600;
    connectionProperties["OPT_RECONNECT"] = true;
    
    this->maxSize = maxSize;
    this->lock = new Mutex();
    this->curSize = 0;
    //初始化driver
    try{
        this->driver = sql::mysql::get_driver_instance();  //这里不是线程安全的
    }
    catch(sql::SQLException &e){
        string errorMsg = string("SQLException: ") + e.what() + string(" MySQL error code: ") + int_to_string(e.getErrorCode()) + string(" SQLState ") +  e.getSQLState();
        Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
    }
    catch(std::runtime_error &e){
        string errorMsg = string("runtime_error: ") + e.what(); 
        Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
    }
    //初始化连接池
    this->Init(maxSize/2);
}

ConnPool::~ConnPool(){    
    this->Destroy();
    delete lock;
}

ConnPool *ConnPool::GetInstance(){
    if(connPool == NULL) {
        connPool = new ConnPool(config->GetVar("db_host"),config->GetVar("db_user"),config->GetVar("db_password"),string_to_int(config->GetVar("max_db_conn_size")));
    }
    
    return connPool;
}

void ConnPool::Init(int size){
    sql::Connection * conn ;
    lock->Lock();
    
    for(int i = 0; i < size ;){
        conn = this->CreateConnection();
        if(conn){
            i++;            
            conns.push_back(conn);
            ++curSize;
        }
        else{
            Log::Write(__FILE__,__FUNCTION__,__LINE__,"Init connpooo fail one");
        }
    }
    
    lock->UnLock();    
}

void ConnPool::Destroy(){
    deque<sql::Connection *>::iterator pos;
    
    lock->Lock();
    
    for(pos = conns.begin(); pos != conns.end();++pos){
        this->TerminateConnection(*pos);
    }
    
    curSize = 0;
    conns.clear();
    
    lock->UnLock();    
}

sql::Connection * ConnPool::CreateConnection(){//这里不负责curSize的增加
    sql::Connection *conn;
    
    try{
        conn = driver->connect(connectionProperties);
        Log::Write(__FILE__,__FUNCTION__,__LINE__,"create a mysql conn");
        return conn;
    }
    catch(sql::SQLException &e){
        string errorMsg = string("SQLException:") + e.what() + string(" MySQL error code: ") + int_to_string(e.getErrorCode()) + string(" SQLState ") +  e.getSQLState();
        Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
        return NULL;
    }
    catch(std::runtime_error &e){
        string errorMsg = string("runtime_error: ") + e.what(); 
        Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
        return NULL;
    }
}

void ConnPool::TerminateConnection(sql::Connection * conn){
    if(conn){
        try{
            conn->close();
        }
        catch(sql::SQLException &e){
            string errorMsg = string("SQLException:") + e.what() + string(" MySQL error code: ") + int_to_string(e.getErrorCode()) + string(" SQLState ") +  e.getSQLState();
            Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
        }
        catch(std::runtime_error &e){
            string errorMsg = string("runtime_error: ") + e.what(); 
            Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
        }
        
        delete conn;
    }
}

sql::Connection * ConnPool::GetConnection(){
    sql::Connection * conn;
    
    lock->Lock();
    
    if(conns.size() > 0){//有空闲连接,则返回
        conn = conns.front();
        conns.pop_front();
        
        if(conn->isClosed()){ //如果连接关闭,则重新打开一个连接
            Log::Write(__FILE__,__FUNCTION__,__LINE__,"a mysql conn has been closed");
            delete conn;
            conn = this->CreateConnection();
        }
        
        if(conn == NULL){ //创建连接不成功
            --curSize;
        }
        lock->UnLock();
        
        return conn;
    }
    else{
        if(curSize < maxSize){//还可以创建新的连接
            conn = this->CreateConnection();
            if(conn){
                ++curSize;
                lock->UnLock();
                return conn;
            }
            else{
                lock->UnLock();
                return NULL;
            }
        }
        else{//连接池已经满了
            lock->UnLock();
            return NULL;
        }
    }    
}

void ConnPool::ReleaseConnection(sql::Connection * conn){
    if(conn){
        lock->Lock();
        
        conns.push_back(conn);
        
        lock->UnLock();
    }
}

sql::Connection * ConnPool::GetConnectionTry(int maxNum){
    sql::Connection * conn;
    
    for(int i = 0; i < maxNum; ++i){
        conn = this->GetConnection();
        if(conn){
            return conn;
        }
        else {
            sleep(2);
        }
    }
    
    return NULL;
}

 

现是基于mysql connector C++ api的

magicdb.h文件

 

/**
  *mysql connector C++ 封装
  *
  *@authour libingxiang
  *
  *@email lbxthinker@gmail.com
  *
  **/
#ifndef MAGICDB_H
#define MAGICDB_H

#include <mysql_connection.h>
#include <mysql_driver.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include "push.h"
#include "connpool.h"

using namespace std;

class MagicDB {
    private:
        ConnPool *connPool;
    public:
        MagicDB();
        ~MagicDB();
        sql::ResultSet *Query(const string &database,const string &sql);
        int Execute(const string &database,const string &sql);
};

#endif

magicdb.cpp文件

/**
  *mysql connector C++ 封装
  *
  *@authour libingxiang
  *
  *@email lbxthinker@gmail.com
  *
  **/
#include <stdexcept>
#include "magicdb.h"
#include "log.h"
#include "util.h"

using namespace std;

MagicDB::MagicDB(){
    this->connPool = ConnPool::GetInstance();
}

sql::ResultSet * MagicDB::Query(const string &database,const string &sql){
    sql::Connection *conn;
    sql::Statement *stmt;
    sql::ResultSet *res;
    
    try{
        conn = connPool->GetConnection();  //从连接池中获取连接
        if(conn == NULL){
            conn = connPool->GetConnectionTry(2);
            if(conn == NULL){
                Log::Write(__FILE__,__FUNCTION__,__LINE__,"query get conn error");
                return NULL;
            }
        }

        conn->setSchema(database);
        stmt = conn->createStatement();
        res = stmt->executeQuery(sql.c_str());
        
        connPool->ReleaseConnection(conn);//将连接放入连接池中
        
        delete stmt;
        return res;
    }
    catch(sql::SQLException &e){
        string errorMsg = string("SQLException:") + e.what() + string(" MySQL error code: ") + int_to_string(e.getErrorCode()) + string(" SQLState ") +  e.getSQLState();
        Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
        
        connPool->ReleaseConnection(conn);//将连接放入连接池中
        
        delete stmt;
        delete res;
        return NULL;
    }
    catch(std::runtime_error &e){
        string errorMsg = string("runtime_error: ") + e.what(); 
        Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
        
        connPool->ReleaseConnection(conn);//将连接放入连接池中
        
        delete stmt;
        delete res;
        return NULL;
    }
}

int MagicDB::Execute(const string &database,const string &sql){
    sql::Statement * stmt;
    sql::Connection *conn;
    
    try{
        conn = connPool->GetConnection();  //从连接池中获取连接
        if(conn == NULL){
            conn = connPool->GetConnectionTry(2);
            if(conn == NULL){
                Log::Write(__FILE__,__FUNCTION__,__LINE__,"query get conn error");
                return -3;
            }
        }
        
        conn->setSchema(database);
        stmt = conn->createStatement();
        stmt->execute(sql.c_str());
        
        connPool->ReleaseConnection(conn);//将连接放入连接池中
        
        delete stmt;
        return 0;
    }
    catch(sql::SQLException &e){
        string errorMsg = string("SQLException:") + e.what() + string(" MySQL error code: ") + int_to_string(e.getErrorCode()) + string(" SQLState ") +  e.getSQLState();
        Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
        
        connPool->ReleaseConnection(conn);//将连接放入连接池中
        
        delete stmt;
        return -1;
    }
    catch(std::runtime_error &e){
        string errorMsg = string("runtime_error: ") + e.what(); 
        Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
        
        connPool->ReleaseConnection(conn);//将连接放入连接池中
        
        delete stmt;
        return -2;
    }
}

MagicDB::~MagicDB(){
    delete connPool;
}