首页 > 代码库 > 一种利用ADO连接池操作MySQL的解决方案(VC++)

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

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

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

数据库连接池

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

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

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

  1、资源复用

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

 2、提高系统响应速度

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

3、避免资源泄漏

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

实现原理

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

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

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

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

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

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

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

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

  1 //==================头文件 =====================//  2 //定义数据库连结基本信息结构  3 typedef struct  4 {  5     char    db_ip[20]; //ip地址  6     uint32  db_port;   //端口  7     char    db_user[20];//用户  8     char    db_pwd[32];//密码  9     char    db_dbname[32];//数据库名 10 }vos_dbxmlinfo_stru; 11  12  13  14 class CXCADOPOOL 15 {     16 protected: 17     CXCADOPOOL(); 18  19 public: 20     virtual ~CXCADOPOOL(void); 21  22     //接口 23 public: 24     void    InitConnection(const int iMin, const int iMax); 25     bool    ExcuteSql(_bstr_t bSql, bool bCheck = true); 26     bool    GetRecordSet(_bstr_t bSql, _RecordsetPtr& pRecord, long lOption = adCmdText, bool bCheck = true); 27  28  29     bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, int& nValue); 30     bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, UINT64& unValue); 31     bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, string& strValue); 32     bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, double& fValue); 33     bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, float& fValue); 34     bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, ULONG & nValue); 35  36     bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, short& nValue); 37     bool    GetItemValue(_RecordsetPtr pRecord, long nIndex, unsigned char& nValue); 38     bool    GetItemValue(_RecordsetPtr pRecord, string fieldname, string& strValue); 39  40     template<class T> 41     bool    GetItemValue(_RecordsetPtr pRecord, string fieldname, T& tValue); 42  43     static CXCADOPOOL *Instance(); 44     _ConnectionPtr *GetTransConnection(); 45     void    SendTransCompMsg(_ConnectionPtr *pConptr); 46     bool    ExecuteTransSql(_ConnectionPtr *pConptr, _bstr_t bSql); 47 private: 48     bool    CreateDBConnection(_ConnectionPtr & conptr); //返回一个连接  49     void    GetConnectionString(string &strConnect); 50     _ConnectionPtr * GetConnectionPtr(); 51     void    ReleaseConnectionPtr(_ConnectionPtr &conptr); 52     void    InitDBConfig(); 53     bool    ExcuteWithoutCheck(_ConnectionPtr &conptr, _bstr_t bSql); 54     bool    GetRecordSetWithoutCheck(_ConnectionPtr &conptr, _bstr_t bSql, _RecordsetPtr& pRecord, long lOption = adCmdText); 55     static DWORD WINAPI IdleConnThreadFunc(LPVOID lParam); 56 private: 57      58     queue<_ConnectionPtr *> m_qConn; 59     int m_MinConNum;    //最小连接数 60     int m_MaxConNum;    //最大连接数 61     int m_CurrentNum;    //当前连接数 62  63     HANDLE          m_Mutex; 64     HANDLE            m_hEvent; 65     HANDLE            m_hThread; 66     DWORD            m_dwThreadId; 67     HANDLE            m_hThreadEvent; 68     string                m_strConnect; 69     static CXCADOPOOL* _instance; 70 public: 71     vos_dbxmlinfo_stru    m_stDBInfo; 72  73 }; 74  75 template<class T> 76 bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, string fieldname, T& tValue) 77 { 78     try 79     { 80         ASSERT_RECORDSET(pRecord); 81         _variant_t vart = pRecord->GetCollect(_variant_t(fieldname.c_str())); 82         (tValue =http://www.mamicode.com/ (T)(vart)); 83     } 84     catch (_com_error &) 85     { 86         return false; 87     } 88     return true; 89 } 90  extern CXCADOPOOL *pAdoPool; 91 //===================.CPP文件=====================// 92  93 bool CXCADOPOOL::GetItemValue( _RecordsetPtr pRecord, long nIndex, int& nValue ) 94 { 95     try 96     { 97         ASSERT_RECORDSET(pRecord); 98  99         nValue = http://www.mamicode.com/(int)(pRecord->GetFields()->GetItem(nIndex)->Value);100     }101     catch (_com_error &)102     {103         return false;104     }105     return true;106 }107 108 bool  CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, UINT64& unValue)109 {110     try111     {112         ASSERT_RECORDSET(pRecord);113 114         unValue = http://www.mamicode.com/(UINT64)pRecord->GetFields()->GetItem(nIndex)->Value;115     }116     catch (_com_error &)117     {118         return false;119     }120     return true;121 }122 123 124 bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, ULONG& nValue)125 {126 127     try128     {129         ASSERT_RECORDSET(pRecord);130 131         nValue = http://www.mamicode.com/(ULONG)pRecord->GetFields()->GetItem(nIndex)->Value;132     }133     catch (_com_error &)134     {135         return false;136     }137     return true;138 139 }140 bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, string& strValue)141 {142     try143     {144         ASSERT_RECORDSET(pRecord);145 146         _variant_t vart = pRecord->GetFields()->GetItem(nIndex)->Value;147         if (vart.vt == VT_NULL)148             return true;149 150         strValue = http://www.mamicode.com/(std::string)(bstr_t)vart;151     }152     catch (_com_error &)153     {154         return false;155     }156 157     return true;158 }159 160 bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, double& fValue)161 {162     try163     {164         ASSERT_RECORDSET(pRecord);165 166         fValue = http://www.mamicode.com/(double)pRecord->GetFields()->GetItem(nIndex)->Value;167     }168     catch (_com_error &)169     {170         return false;171     }172 173     return true;174 175 }176 177 178 bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, float& fValue)179 {180     try181     {182         ASSERT_RECORDSET(pRecord);183 184         fValue = http://www.mamicode.com/(float)pRecord->GetFields()->GetItem(nIndex)->Value;185     }186     catch (_com_error &)187     {188         return false;189     }190     return true;191 }192 193 bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, short &sValue)194 {195     try196     {197         ASSERT_RECORDSET(pRecord);198         sValue = http://www.mamicode.com/(short)pRecord->GetFields()->GetItem(nIndex)->Value;199     }200     catch (_com_error &)201     {202         return false;203     }204     return true;205 }206 207 bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, long nIndex, unsigned char& cValue)208 {209     try210     {211         ASSERT_RECORDSET(pRecord);212         cValue = http://www.mamicode.com/(unsigned char)pRecord->GetFields()->GetItem(nIndex)->Value;213     }214     catch (_com_error &)215     {216         return false;217     }218     return true;219 }220 221 222 CXCADOPOOL *pAdoPool = NULL;223 224 CXCADOPOOL *CXCADOPOOL::_instance = NULL;225 226 227 CXCADOPOOL::CXCADOPOOL()228 {229 230     ::CoInitialize(NULL);231 232     InitDBConfig();233     GetConnectionString(m_strConnect);234     m_Mutex = ::CreateMutex(NULL, FALSE, NULL);235     m_hEvent = ::CreateEvent(NULL, TRUE, FALSE, NULL);236     m_CurrentNum = 0;237 238     m_hThreadEvent = ::CreateEvent(NULL, FALSE, FALSE, NULL);239     m_hThread = ::CreateThread(NULL, 0,(LPTHREAD_START_ROUTINE)IdleConnThreadFunc, this, 0, &m_dwThreadId);240     WaitForSingleObject(m_hThreadEvent, INFINITE);241     CloseHandle(m_hThreadEvent);242    245 }246 247 CXCADOPOOL::~CXCADOPOOL(void)248 {249     ::CoUninitialize();250 }251 252 void CXCADOPOOL::InitConnection(const int iMin, const int iMax)253 {254     static bool bInitial = true;255     if (bInitial)256     {257         m_MinConNum = iMin;258         m_MaxConNum = iMax;259         for (int i = 0; i < iMin; i++)260         {261             _ConnectionPtr *conptr = new _ConnectionPtr;262             if (CreateDBConnection(*conptr))263             {264                 WaitForSingleObject(m_Mutex,INFINITE);265                 m_qConn.push(conptr);266                 m_CurrentNum++;267                 ReleaseMutex(m_Mutex);268             }269         }270         bInitial = false;271     }272 }273         274 bool CXCADOPOOL::CreateDBConnection(_ConnectionPtr & conptr)275 {276     try277     {278         //conptr.CreateInstance("ADODB.Connection");279         conptr.CreateInstance(__uuidof(Connection));280     281         HRESULT hr = conptr->Open(m_strConnect.c_str(), "", "", adModeUnknown);282         if (FAILED(hr))283         {284                         return false;285         }        286     }287     catch (_com_error &e)288     {289                 return false;290     }291     return true;292 }293 294 void CXCADOPOOL::GetConnectionString(string &strConnect)295 {296     USES_CONVERSION;297     CString str;298     str.Format(_T("Driver=MySQL ODBC 5.3 Unicode Driver;SERVER=%s;UID=%s;PWD=%s;DATABASE=%s;PORT=%d"),299         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);300     strConnect = T2A(str);301 302 }303 304 void CXCADOPOOL::InitDBConfig()305 {306     GetPrivateProfileStringA("DBInfo", "host", "localhost", m_stDBInfo.db_ip, 20, ".\\DB.ini");307     m_stDBInfo.db_port = GetPrivateProfileIntA("DBInfo", "port", 3306, ".\\DB.ini");308     GetPrivateProfileStringA("DBInfo", "dbname", "", m_stDBInfo.db_dbname, 32, ".\\DB.ini");309     GetPrivateProfileStringA("DBInfo", "user", "", m_stDBInfo.db_user, 20, ".\\DB.ini");310 311     char pbuf_text[255] = { 0 };312     GetPrivateProfileStringA("DBInfo", "password", "", pbuf_text, 255, ".\\DB.ini");  313 }314 315 bool CXCADOPOOL::ExcuteSql(_bstr_t bSql, bool bCheck)316 {317  326     _ConnectionPtr *conptr = GetConnectionPtr();327     bool bExec = ExcuteWithoutCheck(*conptr, bSql);330     PostThreadMessage(m_dwThreadId, WM_USER_DB_THREAD_MSG, (WPARAM)conptr,NULL);331     return bExec;332 }333 334 _ConnectionPtr *  CXCADOPOOL::GetConnectionPtr()335 {336     //找出空闲连接337     while (1)338     {339         WaitForSingleObject(m_Mutex, INFINITE);340         _ConnectionPtr *conptr;341         if (m_qConn.empty())342         {343             if (m_CurrentNum < m_MaxConNum)344             {345                 conptr = new _ConnectionPtr;346                 if (CreateDBConnection(*conptr))347                 {348                     m_CurrentNum++;349                 }350             }351             else352             {353                 //等待连接释放354                 ResetEvent(m_hEvent);355                 ReleaseMutex(m_Mutex);356                 WaitForSingleObject(m_hEvent, INFINITE);357                 continue;358             }359         }360         else361         {362             conptr = m_qConn.front();363             m_qConn.pop();364         }365 366         ReleaseMutex(m_Mutex);367         return conptr;368     }369     370     371 }372 373 DWORD WINAPI CXCADOPOOL::IdleConnThreadFunc(LPVOID lParam)374 {375     MSG msg;376     PeekMessage(&msg, NULL, WM_USER, WM_USER, PM_NOREMOVE);377     CXCADOPOOL *pCXCADOPOOL = static_cast<CXCADOPOOL *>(lParam);378     SetEvent(pCXCADOPOOL->m_hThreadEvent);379 380     while (1)381     {382         if (GetMessage(&msg, 0, 0, 0))383         {384             switch (msg.message)385             {386             case WM_USER_DB_THREAD_MSG:387             {388                 _ConnectionPtr *conptr = (_ConnectionPtr *) (msg.wParam);389             390                 WaitForSingleObject(pCXCADOPOOL->m_Mutex,INFINITE);391                 pCXCADOPOOL->m_qConn.push(conptr);392                 ReleaseMutex(pCXCADOPOOL->m_Mutex);393                 SetEvent(pCXCADOPOOL->m_hEvent);394                 395             }396             default:397                 break;398             }399         }400     }401     return 0;402 }403 404 void CXCADOPOOL::ReleaseConnectionPtr(_ConnectionPtr &conptr)405 {406     if (conptr != NULL)407     {408         conptr->Close(); //关闭连接409         conptr.Release(); //释放内存410         conptr = NULL;411 412     }413 414 }415 416 bool CXCADOPOOL::ExcuteWithoutCheck(_ConnectionPtr &conptr, _bstr_t bSql)417 {418     int i = 0;419     while (i < 3)420     {421         try422         {423             if (0 != i)424             {425                 ReleaseConnectionPtr(conptr);426                 CreateDBConnection(conptr);427             }428             ++i;429             VARIANT nRecordAffected = { 0 };430             conptr->Execute(bSql, &nRecordAffected, adCmdText);431             //ReleaseMutex(m_Mutex);432 433             if (nRecordAffected.date < 0)434             {435                 return false;436             }437             break;438         }439         catch (_com_error&e)440         {441                     }442         catch (...)443         {444                 445                  }446     }447     if (i == 3)448     {449        return false;450     }451 452     return true;453 }454 455 bool CXCADOPOOL::GetRecordSet(_bstr_t bSql, _RecordsetPtr& pRecord, long lOption /*= adCmdText*/, bool bCheck)456 {     465     _ConnectionPtr *conptr = GetConnectionPtr();466     bool bExec = GetRecordSetWithoutCheck(*conptr, bSql, pRecord,lOption);467     PostThreadMessage(m_dwThreadId, WM_USER_DB_THREAD_MSG, (WPARAM)conptr, NULL);468     return bExec;469 }470 471 bool CXCADOPOOL::GetRecordSetWithoutCheck(_ConnectionPtr &conptr, _bstr_t bSql, _RecordsetPtr& pRecord, long lOption /*= adCmdText*/)472 {473     for (int i = 0; i < 3; ++i)474     {475         try476         {477             if (0 != i)478             {479                 ReleaseConnectionPtr(conptr);480                 CreateDBConnection(conptr);481             }482             HRESULT hr = pRecord.CreateInstance(__uuidof(Recordset));483             if (SUCCEEDED(hr))484             {485                 pRecord->CursorLocation = adUseClient;486                 HRESULT ht = pRecord->Open(bSql, _variant_t((IDispatch *)conptr), adOpenDynamic, adLockOptimistic, lOption);487                 return SUCCEEDED(ht);488             }489             return false;490         }491         catch (_com_error&e)492         {        }493         catch (...)494         {495                 }496     }497     return false;498 }499 500 bool CXCADOPOOL::GetItemValue(_RecordsetPtr pRecord, string fieldname, string& strValue)501 {502     try503     {504         ASSERT_RECORDSET(pRecord);505         _variant_t vart = pRecord->GetCollect(_variant_t(fieldname.c_str()));506         strValue = http://www.mamicode.com/(std::string)(bstr_t)vart;507     }508     catch (_com_error &)509     {510         return false;511     }512     return true;513 }514 515 CXCADOPOOL * CXCADOPOOL::Instance()516 {517     if (NULL == _instance)518     {519         _instance = new CXCADOPOOL;520     }521     return _instance;522 }523 524 _ConnectionPtr * CXCADOPOOL::GetTransConnection()525 {526     _ConnectionPtr *pConptr = this->GetConnectionPtr();527     //执行一个查询语句验证下确保当前连接可用528     if ((*pConptr)->State != adStateOpen)529     {530         ReleaseConnectionPtr(*pConptr);531         CreateDBConnection(*pConptr);532     }533     return pConptr;534 }535 536 void CXCADOPOOL::SendTransCompMsg(_ConnectionPtr *pConptr)537 {538     PostThreadMessage(m_dwThreadId, WM_USER_DB_THREAD_MSG, (WPARAM)pConptr, NULL);539 }540 541 bool CXCADOPOOL::ExecuteTransSql(_ConnectionPtr *pConptr, _bstr_t bSql)542 {543   return ExcuteWithoutCheck(*pConptr, bSql);544 }545 546 547 548 549                     

 

 

 

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