一种利用ADO连接池操作MySQL的解决方案（VC++）

 VC++连接MySQL数据库 常用的方式有三种:ADO、mysql++,mysql API ; 本文只讲述ADO的连接方式。

      为什么要使用连接池? 对于简单的数据库应用，完全可以先创建一个常连接（此连接永远不关闭，直接数进程退出），但是这样做至少会引起两个问题:(1)资源竞争，多个数据库请求操作不能同时进行，后一请求必须要等到前一请求完成后才能进行；(2)多线程情况下容易出现混乱，甚至出现资源异常释放。还有一种方法，就是使用数据库时创建连接，使用完后关闭连接回收资源。这种方式在数据库操作频繁的情况下会出现严重的效率问题。

数据库连接池

     百度百科给出的解释说明如下:

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

   使用数据库连接池至少带来以下几个好处:

  1、资源复用

    数据库连接得到复用，避免了频繁创建、释放引起的系统性能开销。减少了内存碎片以及数据库线程（甚至是进程）的数量。

 2、提高系统响应速度

     由于数据库连接资源得到复用，这毫无疑问会提高系统的整体响应速度。

3、避免资源泄漏

    所有的连接都集中在连接池中统一管理，这可以避免使用单一连接带来的两个问题。

实现原理

  一个较为完备的数据库连接池应具备以下几个条件：

    (1)实始化时创建一定数据量的连接对象放于连接池中。

    (2)连接池对象要有上限。

    (3)连接使用完毕后要放回连接池而不是直接释放掉。

    (4)长期处于空闲态的连接要释放。

最为完整的实现原理请参考百度百科:数据库连接池。

   下面给出一个简单的ADO数据库连接池实现代码:

   (说明:以下代码没有考虑到上述原理的第(4)点，读者请根据自身需要自行实现之。)

//==================头文件 =====================//
//定义数据库连结基本信息结构
typedef struct
{
    char    db_ip[20]; //ip地址
    uint32  db_port;   //端口
    char    db_user[20];//用户
    char    db_pwd[32];//密码
    char    db_dbname[32];//数据库名
}vos_dbxmlinfo_stru;



class CXCADOPOOL
{    
protected:
    CXCADOPOOL();

public:
    virtual ~CXCADOPOOL(void);

    //接口
public:
    void    InitConnection(const int iMin, const int iMax);
    bool    ExcuteSql(_bstr_t bSql, bool bCheck = true);
    bool    GetRecordSet(_bstr_t bSql, _RecordsetPtr& pRecord, long lOption = adCmdText, bool bCheck = true);


    bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, int& nValue);
    bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, UINT64& unValue);
    bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, string& strValue);
    bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, double& fValue);
    bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, float& fValue);
    bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, ULONG & nValue);

    bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, short& nValue);
    bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, unsigned char& nValue);
    bool    GetItemValue(_RecordsetPtr pRecord, string fieldname, string& strValue);

    template<class T>
    bool    GetItemValue(_RecordsetPtr pRecord, string fieldname, T& tValue);

    static CXCADOPOOL *Instance();
    _ConnectionPtr *GetTransConnection();
    void    SendTransCompMsg(_ConnectionPtr *pConptr);
    bool    ExecuteTransSql(_ConnectionPtr *pConptr, _bstr_t bSql);
private:
    bool    CreateDBConnection(_ConnectionPtr & conptr); //返回一个连接 
    void    GetConnectionString(string &strConnect);
    _ConnectionPtr * GetConnectionPtr();
    void    ReleaseConnectionPtr(_ConnectionPtr &conptr);
    void    InitDBConfig();
    bool    ExcuteWithoutCheck(_ConnectionPtr &conptr, _bstr_t bSql);
    bool    GetRecordSetWithoutCheck(_ConnectionPtr &conptr, _bstr_t bSql, _RecordsetPtr& pRecord, long lOption = adCmdText);
    static DWORD WINAPI IdleConnThreadFunc(LPVOID lParam);
private:
    
    queue<_ConnectionPtr *> m_qConn;
    int m_MinConNum;    //最小连接数
    int m_MaxConNum;    //最大连接数
    int m_CurrentNum;    //当前连接数

    HANDLE          m_Mutex;
    HANDLE            m_hEvent;
    HANDLE            m_hThread;
    DWORD            m_dwThreadId;
    HANDLE            m_hThreadEvent;
    string                m_strConnect;
    static CXCADOPOOL* _instance;
public:
    vos_dbxmlinfo_stru    m_stDBInfo;

};

template<class T>
bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, string fieldname, T& tValue)
{
    try
    {
        ASSERT_RECORDSET(pRecord);
        _variant_t vart = pRecord->GetCollect(_variant_t(fieldname.c_str()));
        (tValue = (T)(vart));
    }
    catch (_com_error &)
    {
        return false;
    }
    return true;
}
 extern CXCADOPOOL *pAdoPool;
//===================.CPP文件=====================//

bool CXCADOPOOL::GetItemValue( _RecordsetPtr pRecord, long nIndex, int& nValue )
{
    try
    {
        ASSERT_RECORDSET(pRecord);

        nValue = (int)(pRecord->GetFields()->GetItem(nIndex)->Value);
    }
    catch (_com_error &)
    {
        return false;
    }
    return true;
}

bool  CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, UINT64& unValue)
{
    try
    {
        ASSERT_RECORDSET(pRecord);

        unValue = (UINT64)pRecord->GetFields()->GetItem(nIndex)->Value;
    }
    catch (_com_error &)
    {
        return false;
    }
    return true;
}


bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, ULONG& nValue)
{

    try
    {
        ASSERT_RECORDSET(pRecord);

        nValue = (ULONG)pRecord->GetFields()->GetItem(nIndex)->Value;
    }
    catch (_com_error &)
    {
        return false;
    }
    return true;

}
bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, string& strValue)
{
    try
    {
        ASSERT_RECORDSET(pRecord);

        _variant_t vart = pRecord->GetFields()->GetItem(nIndex)->Value;
        if (vart.vt == VT_NULL)
            return true;

        strValue = (std::string)(bstr_t)vart;
    }
    catch (_com_error &)
    {
        return false;
    }

    return true;
}

bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, double& fValue)
{
    try
    {
        ASSERT_RECORDSET(pRecord);

        fValue = (double)pRecord->GetFields()->GetItem(nIndex)->Value;
    }
    catch (_com_error &)
    {
        return false;
    }

    return true;

}


bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, float& fValue)
{
    try
    {
        ASSERT_RECORDSET(pRecord);

        fValue = (float)pRecord->GetFields()->GetItem(nIndex)->Value;
    }
    catch (_com_error &)
    {
        return false;
    }
    return true;
}

bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, short &sValue)
{
    try
    {
        ASSERT_RECORDSET(pRecord);
        sValue = (short)pRecord->GetFields()->GetItem(nIndex)->Value;
    }
    catch (_com_error &)
    {
        return false;
    }
    return true;
}

bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, unsigned char& cValue)
{
    try
    {
        ASSERT_RECORDSET(pRecord);
        cValue = (unsigned char)pRecord->GetFields()->GetItem(nIndex)->Value;
    }
    catch (_com_error &)
    {
        return false;
    }
    return true;
}


CXCADOPOOL *pAdoPool = NULL;

CXCADOPOOL *CXCADOPOOL::_instance = NULL;


CXCADOPOOL::CXCADOPOOL()
{

    ::CoInitialize(NULL);

    InitDBConfig();
    GetConnectionString(m_strConnect);
    m_Mutex = ::CreateMutex(NULL, FALSE, NULL);
    m_hEvent = ::CreateEvent(NULL, TRUE, FALSE, NULL);
    m_CurrentNum = 0;

    m_hThreadEvent = ::CreateEvent(NULL, FALSE, FALSE, NULL);
    m_hThread = ::CreateThread(NULL, 0,(LPTHREAD_START_ROUTINE)IdleConnThreadFunc, this, 0, &m_dwThreadId);
    WaitForSingleObject(m_hThreadEvent, INFINITE);
    CloseHandle(m_hThreadEvent);
   
}

CXCADOPOOL::~CXCADOPOOL(void)
{
    ::CoUninitialize();
}

void CXCADOPOOL::InitConnection(const int iMin, const int iMax)
{
    static bool bInitial = true;
    if (bInitial)
    {
        m_MinConNum = iMin;
        m_MaxConNum = iMax;
        for (int i = 0; i < iMin; i++)
        {
            _ConnectionPtr *conptr = new _ConnectionPtr;
            if (CreateDBConnection(*conptr))
            {
                WaitForSingleObject(m_Mutex,INFINITE);
                m_qConn.push(conptr);
                m_CurrentNum++;
                ReleaseMutex(m_Mutex);
            }
        }
        bInitial = false;
    }
}
        
bool CXCADOPOOL::CreateDBConnection(_ConnectionPtr & conptr)
{
    try
    {
        //conptr.CreateInstance("ADODB.Connection");
        conptr.CreateInstance(__uuidof(Connection));
    
        HRESULT hr = conptr->Open(m_strConnect.c_str(), "", "", adModeUnknown);
        if (FAILED(hr))
        {
                        return false;
        }        
    }
    catch (_com_error &e)
    {
                return false;
    }
    return true;
}

void CXCADOPOOL::GetConnectionString(string &strConnect)
{
    USES_CONVERSION;
    CString str;
    str.Format(_T("Driver=MySQL ODBC 5.3 Unicode Driver;SERVER=%s;UID=%s;PWD=%s;DATABASE=%s;PORT=%d"),
        A2T((char*)m_stDBInfo.db_ip), A2T((char*)m_stDBInfo.db_user), A2T((char*)m_stDBInfo.db_pwd), A2T((char*)m_stDBInfo.db_dbname), m_stDBInfo.db_port);
    strConnect = T2A(str);

}

void CXCADOPOOL::InitDBConfig()
{
    GetPrivateProfileStringA("DBInfo", "host", "localhost", m_stDBInfo.db_ip, 20, ".\DB.ini");
    m_stDBInfo.db_port = GetPrivateProfileIntA("DBInfo", "port", 3306, ".\DB.ini");
    GetPrivateProfileStringA("DBInfo", "dbname", "", m_stDBInfo.db_dbname, 32, ".\DB.ini");
    GetPrivateProfileStringA("DBInfo", "user", "", m_stDBInfo.db_user, 20, ".\DB.ini");

    char pbuf_text[255] = { 0 };
    GetPrivateProfileStringA("DBInfo", "password", "", pbuf_text, 255, ".\DB.ini");  
}

bool CXCADOPOOL::ExcuteSql(_bstr_t bSql, bool bCheck)
{
 
    _ConnectionPtr *conptr = GetConnectionPtr();
    bool bExec = ExcuteWithoutCheck(*conptr, bSql);
    PostThreadMessage(m_dwThreadId, WM_USER_DB_THREAD_MSG, (WPARAM)conptr,NULL);
    return bExec;
}

_ConnectionPtr *  CXCADOPOOL::GetConnectionPtr()
{
    //找出空闲连接
    while (1)
    {
        WaitForSingleObject(m_Mutex, INFINITE);
        _ConnectionPtr *conptr;
        if (m_qConn.empty())
        {
            if (m_CurrentNum < m_MaxConNum)
            {
                conptr = new _ConnectionPtr;
                if (CreateDBConnection(*conptr))
                {
                    m_CurrentNum++;
                }
            }
            else
            {
                //等待连接释放
                ResetEvent(m_hEvent);
                ReleaseMutex(m_Mutex);
                WaitForSingleObject(m_hEvent, INFINITE);
                continue;
            }
        }
        else
        {
            conptr = m_qConn.front();
            m_qConn.pop();
        }

        ReleaseMutex(m_Mutex);
        return conptr;
    }
    
    
}

DWORD WINAPI CXCADOPOOL::IdleConnThreadFunc(LPVOID lParam)
{
    MSG msg;
    PeekMessage(&msg, NULL, WM_USER, WM_USER, PM_NOREMOVE);
    CXCADOPOOL *pCXCADOPOOL = static_cast<CXCADOPOOL *>(lParam);
    SetEvent(pCXCADOPOOL->m_hThreadEvent);

    while (1)
    {
        if (GetMessage(&msg, 0, 0, 0))
        {
            switch (msg.message)
            {
            case WM_USER_DB_THREAD_MSG:
            {
                _ConnectionPtr *conptr = (_ConnectionPtr *) (msg.wParam);
            
                WaitForSingleObject(pCXCADOPOOL->m_Mutex,INFINITE);
                pCXCADOPOOL->m_qConn.push(conptr);
                ReleaseMutex(pCXCADOPOOL->m_Mutex);
                SetEvent(pCXCADOPOOL->m_hEvent);
                
            }
            default:
                break;
            }
        }
    }
    return 0;
}

void CXCADOPOOL::ReleaseConnectionPtr(_ConnectionPtr &conptr)
{
    if (conptr != NULL)
    {
        conptr->Close(); //关闭连接
        conptr.Release(); //释放内存
        conptr = NULL;

    }

}

bool CXCADOPOOL::ExcuteWithoutCheck(_ConnectionPtr &conptr, _bstr_t bSql)
{
    int i = 0;
    while (i < 3)
    {
        try
        {
            if (0 != i)
            {
                ReleaseConnectionPtr(conptr);
                CreateDBConnection(conptr);
            }
            ++i;
            VARIANT nRecordAffected = { 0 };
            conptr->Execute(bSql, &nRecordAffected, adCmdText);
            //ReleaseMutex(m_Mutex);

            if (nRecordAffected.date < 0)
            {
                return false;
            }
            break;
        }
        catch (_com_error&e)
        {
                    }
        catch (...)
        {
                
                 }
    }
    if (i == 3)
    {
       return false;
    }

    return true;
}

bool CXCADOPOOL::GetRecordSet(_bstr_t bSql, _RecordsetPtr& pRecord, long lOption /*= adCmdText*/, bool bCheck)
{     465     _ConnectionPtr *conptr = GetConnectionPtr();
    bool bExec = GetRecordSetWithoutCheck(*conptr, bSql, pRecord,lOption);
    PostThreadMessage(m_dwThreadId, WM_USER_DB_THREAD_MSG, (WPARAM)conptr, NULL);
    return bExec;
}

bool CXCADOPOOL::GetRecordSetWithoutCheck(_ConnectionPtr &conptr, _bstr_t bSql, _RecordsetPtr& pRecord, long lOption /*= adCmdText*/)
{
    for (int i = 0; i < 3; ++i)
    {
        try
        {
            if (0 != i)
            {
                ReleaseConnectionPtr(conptr);
                CreateDBConnection(conptr);
            }
            HRESULT hr = pRecord.CreateInstance(__uuidof(Recordset));
            if (SUCCEEDED(hr))
            {
                pRecord->CursorLocation = adUseClient;
                HRESULT ht = pRecord->Open(bSql, _variant_t((IDispatch *)conptr), adOpenDynamic, adLockOptimistic, lOption);
                return SUCCEEDED(ht);
            }
            return false;
        }
        catch (_com_error&e)
        {        }
        catch (...)
        {
                }
    }
    return false;
}

bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, string fieldname, string& strValue)
{
    try
    {
        ASSERT_RECORDSET(pRecord);
        _variant_t vart = pRecord->GetCollect(_variant_t(fieldname.c_str()));
        strValue = (std::string)(bstr_t)vart;
    }
    catch (_com_error &)
    {
        return false;
    }
    return true;
}

CXCADOPOOL * CXCADOPOOL::Instance()
{
    if (NULL == _instance)
    {
        _instance = new CXCADOPOOL;
    }
    return _instance;
}

_ConnectionPtr * CXCADOPOOL::GetTransConnection()
{
    _ConnectionPtr *pConptr = this->GetConnectionPtr();
    //执行一个查询语句验证下确保当前连接可用
    if ((*pConptr)->State != adStateOpen)
    {
        ReleaseConnectionPtr(*pConptr);
        CreateDBConnection(*pConptr);
    }
    return pConptr;
}

void CXCADOPOOL::SendTransCompMsg(_ConnectionPtr *pConptr)
{
    PostThreadMessage(m_dwThreadId, WM_USER_DB_THREAD_MSG, (WPARAM)pConptr, NULL);
}

bool CXCADOPOOL::ExecuteTransSql(_ConnectionPtr *pConptr, _bstr_t bSql)
{
  return ExcuteWithoutCheck(*pConptr, bSql);
}