C++ ADO的数据库连接池..

此连接池用于管理数据库的多个连接，主要思想如下：

1）按照传入的参数起N个连接来连接数据库（参数中有最小连接数N,最大连接数M）。

2）建立一个空闲队列（初始化的N个连接放到空闲队列），一个使用队列来管理，使用的时候重空闲队列抓取一个连接，丢到使用队列，

如果空闲队列没有可用连接，并且连接数小于允许的最大连接数（<M）则创建一个新的连接，否则等待。

3）起一个守护线程，发现用过的连接，则使用队列中移除，添加到空闲队列，无效的链接则删除。

 

代码如下，

首先是头文件，因为导出的是DLL，文件中的RFIDAPI定义如下

#ifdef _USRDLL
#define RFIDAPI _declspec(dllexport)
#else
#define RFIDAPI _declspec(dllimport)

 

// DBConnPool.h: interface for the DBConnPool class. // // #if !defined(AFX_DBCONNPOOL_H__42089E9A_FD85_4DD4_A973_64A4980332A5__INCLUDED_) #define AFX_DBCONNPOOL_H__42089E9A_FD85_4DD4_A973_64A4980332A5__INCLUDED_ #define SQL_BUFFER_LEN 1024 #import "C:/Program Files/Common Files/System/ado/msado15.dll" rename("EOF","ADOEOF") #include <list> #include <windows.h> #include <string> #include <vector> #include "DBDefine.h" using namespace ADODB; using namespace std; class RFIDAPI DBConnect { public: DBConnect(LPCSTR strDstAddress, LPCSTR strUsername, LPCSTR strPassword, LPCSTR strDBName, BOOL &state); ~DBConnect(); public: // 连接到数据库 int Open(LPCTSTR strDstAddress, LPCTSTR strUsername, LPCTSTR strPassword, LPCTSTR strDBName); // 关闭数据库 int Close(); // 数据库是否已连接 BOOL IsOpen() const; public: private: ADODB::_ConnectionPtr _connection_ptr; //ADO的数据库连接智能指针 bool _isAdoValid; //ADO环境是否已经初化成功标志量 bool m_bDBOpen; LPCSTR _strDstAddress; //数据源地址或服务名 LPCSTR _strUsername; //数据库用户名 LPCSTR _strPassword; //数据库密码 LPCSTR _strDBName; //数据库名称 void VarientToString(_variant_t var, string& str); //对外公共接口 public: int GetSubmitInfo(vector<SOAP_SUBMIT_SMS> &vecsoapSms); int InsertHistory(int id); int UserLogin(LPCSTR name,LPCSTR password); }; typedef std::list<DBConnect*> DBConnectList; class DBConnPool { public: DBConnPool(); virtual ~DBConnPool(); // 获取实例指针 static DBConnPool * Instanse(); // 初始化所有连接 int InitializeAllDBConnections(); // 关闭所有连接 void DestroyAllDBConnections(); // 获取一个空闲连接 DBConnect* GetAConnection(); // 交还连接给空闲队列 int RestoreAConnection(DBConnect* pDBEngine); void SetDBInfo(LPCSTR strDstAddress, LPCSTR strUsername, LPCSTR strPassword, LPCSTR strDBName,int minConn,int maxConn); private: // 创建一个连接 int InitializeAConnection(); // 关闭一个连接 void CloseAConnection(DBConnect* pDBEngine); // 停止工作线程 void StopThread(); // 判断是否需要停止 BOOL IsNeedStop(); BOOL IsNeedConnection(); // 将守卫类作为连接池类的友元类 friend class ConnGuard; // 唯一实例 static DBConnPool *m_pInstanse; // 空闲数据库连接队列 DBConnectList m_listIdleConnection; // 在使用的数据库连接 DBConnectList m_listBusyConnection; // 队列保护的临界区 CRITICAL_SECTION m_csIdleConnList; CRITICAL_SECTION m_csBusyConnList; // 可用连接总数的三个指标：最大、最小 int m_nMaxCount; int m_nMinCount; // // 数据库信息 LPCSTR _strDstAddress; //数据源地址或服务名 LPCSTR _strUsername; //数据库用户名 LPCSTR _strPassword; //数据库密码 LPCSTR _strDBName; //数据库名称 // 维护线程 HANDLE m_hMaintanceThread; // 线程句柄 HANDLE m_hHaveData; // 信号 BOOL m_bNeedStop; // 管理线程起停的标志位 BOOL m_bNeedConnection; // 需要创建连接的标志 static DWORD WINAPI thread_run( LPVOID pdata); }; // 守卫类，利用构造和析构函数保证连接取出和归还必须成对，防止资源泄露 class DBConnGuard { public: DBConnGuard(DBConnect*& DBConn) { DBConn = DBConnPool::Instanse()->GetAConnection(); m_pDBConn = DBConn; } virtual ~DBConnGuard() { DBConnPool::Instanse()->RestoreAConnection(m_pDBConn); } private: DBConnect *m_pDBConn; }; RFIDAPI void InitDBIterface(LPCSTR strDstAddress, LPCSTR strUsername, LPCSTR strPassword, LPCSTR strDBName,int minConn,int maxConn); RFIDAPI DBConnect * GetAConnect(); #endif // !defined(AFX_DBCONNPOOL_H__42089E9A_FD85_4DD4_A973_64A4980332A5__INCLUDED_)

下面是实现文件，

// DBConnPool.cpp: implementation of the DBConnPool class. // // #include "stdafx.h" #include "DBConnPool.h" DBConnect * pDBConn = NULL; void InitDBIterface(LPCSTR strDstAddress, LPCSTR strUsername, LPCSTR strPassword, LPCSTR strDBName,int minConn,int maxConn) { DBConnPool::Instanse()->SetDBInfo(strDstAddress,strUsername,strPassword,strDBName,minConn,maxConn); int cout = DBConnPool::Instanse()->InitializeAllDBConnections(); } DBConnect * GetAConnect() { DBConnGuard oConnGuard(pDBConn); return pDBConn; } //DBConnect/ DBConnect::DBConnect(LPCTSTR strDstAddress, LPCTSTR strUsername, LPCTSTR strPassword, LPCTSTR strDBName,BOOL &state) { m_bDBOpen = false; if (0 == Open(strDstAddress,strUsername,strPassword,strDBName)) { state =true; } else { state = false; } } DBConnect::~DBConnect() { Close(); } int DBConnect::Open(LPCTSTR strDstAddress, LPCTSTR strUsername, LPCTSTR strPassword, LPCTSTR strDBName) { _strDstAddress = strDstAddress; _strDBName = strDBName; _strUsername = strUsername; _strPassword = strPassword; HRESULT comhr = ::CoInitialize(NULL); if (FAILED(comhr)) { return -1; } HRESULT hr = _connection_ptr.CreateInstance(__uuidof(Connection)); if (FAILED(hr)) { return -1; } char szSQL[SQL_BUFFER_LEN] = {0}; memset(szSQL, 0, SQL_BUFFER_LEN); sprintf(szSQL, "Driver=SQL Server;Server=%s;DATABASE=%s", strDstAddress, strDBName); try { // 连接到服务器上数据库 _connection_ptr->Open(szSQL, strUsername, strPassword,adModeUnknown) ; if (FAILED(hr)) return -1; } catch (_com_error &err) { TRACE(_T("数据库操作失败! 错误信息:%s, 文件:%s, 行:%d./n"), err.ErrorMessage(), __FILE__, __LINE__); return -1; } m_bDBOpen = TRUE; return 0; } int DBConnect::Close() { if (m_bDBOpen) { HRESULT hr =_connection_ptr->Close(); if (FAILED(hr)) { return -1; } ::CoUninitialize(); m_bDBOpen = FALSE; } return 0; } int DBConnect::UserLogin(LPCSTR name,LPCSTR password) { if (!m_bDBOpen) { return -1; } // 创建Command对象 _CommandPtr cmd; HRESULT hr = cmd.CreateInstance(__uuidof(Command)); if (FAILED(hr)) { return -1; } char szSQL[SQL_BUFFER_LEN] = {0}; sprintf(szSQL, "select count(*) as count from t_user where name =/'%s/' and password =/'%s/'", name,password); cmd->ActiveConnection = _connection_ptr; cmd->CommandText = _bstr_t(szSQL); cmd->CommandType = adCmdText; try { _RecordsetPtr rs = cmd->Execute(NULL, NULL, adCmdUnknown); if (FAILED(hr)) { return -1; } _variant_t count; while (!rs->ADOEOF) { count = rs->GetCollect("count"); rs->MoveNext() ; } if(count.intVal == 0) return -1; } catch (_com_error &err) { TRACE(_T("数据库操作失败! 错误信息:%s, 文件:%s, 行:%d./n"), err.ErrorMessage(), __FILE__, __LINE__); return -1; } return 0; } int DBConnect::GetSubmitInfo(vector<SOAP_SUBMIT_SMS> &vecsoapSms) { if (!m_bDBOpen) { return -1; } // 创建Command对象 _CommandPtr cmd; HRESULT hr = cmd.CreateInstance(__uuidof(Command)); if (FAILED(hr)) { return -1; } string strSql = "exec SP_SMS_GETSUBMIT"; cmd->ActiveConnection = _connection_ptr; cmd->CommandText = _bstr_t(strSql.c_str()); cmd->CommandType = adCmdText; try { _RecordsetPtr rs = cmd->Execute(NULL, NULL, adCmdUnknown); if (FAILED(hr)) { return -1; } while (!rs->ADOEOF) { _variant_t id; _variant_t recverid; _variant_t recvertel; _variant_t messagecontent; _variant_t recverphonetype; _variant_t inout; _variant_t sendtime; _variant_t cardtime; _variant_t schoolid; _variant_t classid; _variant_t rfidno; id = rs->GetCollect("id"); recverid = rs->GetCollect("recverid"); recvertel = rs->GetCollect("recvertel"); messagecontent = rs->GetCollect("messagecontent"); recverphonetype = rs->GetCollect("recverphonetype"); inout = rs->GetCollect("inout"); sendtime = rs->GetCollect("sendtime"); cardtime = rs->GetCollect("cardtime"); schoolid = rs->GetCollect("schoolid"); classid = rs->GetCollect("classid"); rfidno = rs->GetCollect("rfidno"); SOAP_SUBMIT_SMS submitsms; submitsms.id = (int)(long)id; VarientToString(recverid, submitsms.recverid); VarientToString(recvertel, submitsms.recvertel); VarientToString(messagecontent, submitsms.messagecontent); VarientToString(recverphonetype, submitsms.recverphonetype); submitsms.inout = (int)(long)inout; VarientToString(sendtime, submitsms.sendtime); VarientToString(cardtime, submitsms.cardtime); VarientToString(rfidno, submitsms.rfidno); submitsms.schoolid = (int)(long)schoolid; submitsms.classid = (int)(long)classid; vecsoapSms.push_back(submitsms); rs->MoveNext() ; } } catch (_com_error &err) { TRACE(_T("数据库操作失败! 错误信息:%s, 文件:%s, 行:%d./n"), err.ErrorMessage(), __FILE__, __LINE__); return -1; } return 0; } int DBConnect::InsertHistory(int id) { if (!m_bDBOpen) { return -1; } // 创建Command对象 _CommandPtr cmd; HRESULT hr = cmd.CreateInstance(__uuidof(Command)); if (FAILED(hr)) { return -1; } char szSQL[SQL_BUFFER_LEN] = {0}; sprintf(szSQL, "exec SP_SMS_SUBMITRESULT %d", id); try { cmd->ActiveConnection = _connection_ptr; cmd->CommandText = _bstr_t(szSQL); cmd->CommandType = adCmdText; cmd->Execute(NULL, NULL, adCmdUnknown); } catch (_com_error &err) { TRACE(_T("数据库操作失败! 错误信息:%s, 文件:%s, 行:%d./n"), err.ErrorMessage(), __FILE__, __LINE__); return -1; } return 0; } void DBConnect::VarientToString(_variant_t var, string& str) { if (VT_NULL == var.vt) { str = ""; } else { str = (char*)_bstr_t(var); } } //End// // // Construction/Destruction // DBConnPool* DBConnPool::m_pInstanse = NULL; DBConnPool::DBConnPool() { m_bNeedStop = FALSE; m_bNeedConnection = FALSE; m_hMaintanceThread = INVALID_HANDLE_VALUE; // 线程控制 m_hHaveData = CreateEvent (NULL, TRUE, FALSE, _T("DataConnPool")); InitializeCriticalSection(&m_csIdleConnList); InitializeCriticalSection(&m_csBusyConnList); } void DBConnPool::SetDBInfo(LPCSTR strDstAddress, LPCSTR strUsername, LPCSTR strPassword, LPCSTR strDBName,int minConn,int maxConn) { _strDBName = strDBName; _strDstAddress = strDstAddress; _strPassword = strPassword; _strUsername = strUsername; m_nMaxCount = maxConn; m_nMinCount = minConn; } DBConnPool::~DBConnPool() { m_hMaintanceThread = INVALID_HANDLE_VALUE; m_bNeedStop = TRUE; CloseHandle(m_hHaveData); CloseHandle(m_hMaintanceThread); DeleteCriticalSection(&m_csIdleConnList); DeleteCriticalSection(&m_csBusyConnList); } DBConnPool *DBConnPool::Instanse() { if (NULL == m_pInstanse) { m_pInstanse = new DBConnPool(); } return m_pInstanse; } int DBConnPool::InitializeAllDBConnections() { // 先七公里现有的数据 DestroyAllDBConnections(); // 开始按照最小数量开始创建 DBConnect * pDBConnect = NULL; int nCount = 0; for (int i = 0; i < m_nMinCount; i++) { nCount = InitializeAConnection(); } // 创建一个工作线程，用来进行一些后台维护工作 if (INVALID_HANDLE_VALUE == m_hMaintanceThread) { m_hMaintanceThread = CreateThread(NULL, NULL, thread_run, (LPVOID)this, 0, NULL); } return nCount; } void DBConnPool::DestroyAllDBConnections() { // 销毁数据库连接可以使用大锁 EnterCriticalSection(&m_csIdleConnList); DBConnectList::iterator itIdle = m_listIdleConnection.begin(); DBConnectList::iterator itIdleEnd = m_listIdleConnection.end(); while (itIdle != itIdleEnd) { if (NULL != (*itIdle)) { (*itIdle)->Close(); delete (*itIdle); } // erase本身就会把跌代器指向下一个 itIdle = m_listIdleConnection.erase(itIdle); } LeaveCriticalSection(&m_csIdleConnList); // 还有使用中的连接 EnterCriticalSection(&m_csBusyConnList); DBConnectList::iterator itBusy = m_listBusyConnection.begin(); DBConnectList::iterator itBusyEnd = m_listBusyConnection.end(); while (itBusy != itBusyEnd) { if (NULL != (*itBusy)) { (*itBusy)->Close(); delete (*itBusy); } // erase本身就会把跌代器指向下一个 itBusy = m_listBusyConnection.erase(itBusy); } LeaveCriticalSection(&m_csBusyConnList); } int DBConnPool::InitializeAConnection() { BOOL bSuccess = FALSE; DBConnect * pDBEngine = new DBConnect(_strDstAddress, _strUsername, _strPassword, _strDBName, bSuccess); if (bSuccess) { m_bNeedConnection = FALSE; return RestoreAConnection(pDBEngine); } else { delete pDBEngine; return m_listIdleConnection.size(); } } void DBConnPool::CloseAConnection(DBConnect* pDBEngine) { pDBEngine->Close(); // 从空闲队列将其删除 EnterCriticalSection(&m_csIdleConnList); m_listIdleConnection.remove(pDBEngine); LeaveCriticalSection(&m_csIdleConnList); } DBConnect * DBConnPool::GetAConnection() { DBConnect * pDBEngine = NULL; // 做一个循环，反复尝试五次取连接，每次间隔1秒钟 int nTimes = 0; while ((m_listIdleConnection.size() <= 0) && (nTimes < 5)) { Sleep(1000); nTimes++; } if (5 == nTimes) { // 这样狼狈的进来肯定是没有可用连接了，记录日志退出 // g_pSvrLog->AddRunLog(LL_ERROR, _T("Waiting for a connection for a long time, but failed.")); return pDBEngine; } // 从空闲队列中取出，并且加入到使用队列中 EnterCriticalSection(&m_csIdleConnList); if (m_listIdleConnection.size() > 0) { pDBEngine = m_listIdleConnection.front(); m_listIdleConnection.pop_front(); // 加入使用的连接队列 EnterCriticalSection(&m_csBusyConnList); m_listBusyConnection.push_back(pDBEngine); LeaveCriticalSection(&m_csBusyConnList); } LeaveCriticalSection(&m_csIdleConnList); if (m_listIdleConnection.size() <= 1) { // 剩余空闲连接的数目小于等于1个时候需要检查开始创建 if ((m_listIdleConnection.size() + m_listBusyConnection.size()) < m_nMaxCount) { // 还小于最大限制，可以创建 SetEvent(m_hHaveData); m_bNeedConnection = TRUE; } else { // 超出限制了，做个记录吧 //g_pSvrLog->AddRunLog(LL_ERROR, _T("Database connection reached max count.")); } } return pDBEngine; } int DBConnPool::RestoreAConnection(DBConnect* pDBEngine) { if (NULL != pDBEngine) { // 从使用中的队列取出 EnterCriticalSection(&m_csBusyConnList); m_listBusyConnection.remove(pDBEngine); LeaveCriticalSection(&m_csBusyConnList); // 加入到空闲队列中 EnterCriticalSection(&m_csIdleConnList); m_listIdleConnection.push_back(pDBEngine); LeaveCriticalSection(&m_csIdleConnList); } EnterCriticalSection(&m_csIdleConnList); int nCount = m_listIdleConnection.size(); LeaveCriticalSection(&m_csIdleConnList); return nCount; } void DBConnPool::StopThread() { m_bNeedStop = TRUE; // 因为线程是无限制等待信号的，所以这里先把标志位置为停止，然后发信号让线程检测 SetEvent(m_hHaveData); // 等待退出 WaitForSingleObject(m_hMaintanceThread, INFINITE); CloseHandle(m_hMaintanceThread); } BOOL DBConnPool::IsNeedStop() { return m_bNeedStop; } BOOL DBConnPool::IsNeedConnection() { return m_bNeedConnection; } /************************************************************************/ /* 维护线程 */ /************************************************************************/ DWORD WINAPI DBConnPool::thread_run( LPVOID pdata) { DBConnPool * pConPool = (DBConnPool *) pdata; while (!pConPool->IsNeedStop()) { // 设置事件为无信号, 并且无限制等待 ResetEvent(pConPool->m_hHaveData); WaitForSingleObject(pConPool->m_hHaveData, INFINITE); if (pConPool->IsNeedConnection()) { // g_pSvrLog->AddRunLog(LL_DEBUG, _T("Create a new DB connection.")); pConPool->InitializeAConnection(); } } return 0; }

为了使用方便，使用的时候，自己定义个类来初始化连接以及所使用的接口，

 

例如，，定义个DBinterface的类

头文件中这样写

// DBInterface.h: interface for the DBInterface class. // // #if !defined(AFX_DBINTERFACE_H__E761F9FE_39EE_40A2_88D1_7FCBB97A145A__INCLUDED_) #define AFX_DBINTERFACE_H__E761F9FE_39EE_40A2_88D1_7FCBB97A145A__INCLUDED_ #if _MSC_VER > 1000 #pragma once #endif // _MSC_VER > 1000 #include "DBDefine.h" #include "DBConnPool.h" #if defined(__cplusplus) extern "C" { #endif // Initialize RS Receiver RFIDAPI DWORD InItDBConnPool(LPCSTR strDstAddress, LPCSTR strUsername, LPCSTR strPassword, LPCSTR strDBName,int minConn,int maxConn); RFIDAPI void GetSubmitInfo(vector<SOAP_SUBMIT_SMS> &vecsoapSms); RFIDAPI void InsertHistory(int id); RFIDAPI int UserLogin(LPCSTR name,LPCSTR password); // Initialize Sock Receiver #if defined(__cplusplus) } #endif #endif // !defined(AFX_DBINTERFACE_H__E761F9FE_39EE_40A2_88D1_7FCBB97A145A__INCLUDED_)

实现文件如下

// DBInterface.cpp : Defines the entry point for the DLL application. // */ /************************************************************************/ #include "stdafx.h" #include "DBInterface.h" DWORD InItDBConnPool(LPCSTR strDstAddress, LPCSTR strUsername, LPCSTR strPassword, LPCSTR strDBName,int minConn,int maxConn) { DBConnPool::Instanse()->SetDBInfo(strDstAddress,strUsername,strPassword,strDBName,minConn,maxConn); return DBConnPool::Instanse()->InitializeAllDBConnections(); } void GetSubmitInfo(vector<SOAP_SUBMIT_SMS> &vecsoapSms) { DBConnect *pDBConn = NULL; DBConnGuard oConnGuard(pDBConn); pDBConn->GetSubmitInfo(vecsoapSms); } void InsertHistory(int id) { DBConnect *pDBConn = NULL; DBConnGuard oConnGuard(pDBConn); pDBConn->InsertHistory(id); } int UserLogin(LPCSTR name,LPCSTR password) { DBConnect *pDBConn = NULL; DBConnGuard oConnGuard(pDBConn); return pDBConn->UserLogin(name,password); }

 

这样把头文件包含到工程，，

调用InItDBConnPool初始化连接池

然后就可以直接使用接口了