oracle和MysqL数据库连接池不能在一个程序里面同时使用，在编译的时候需要将MysqL和oracle分开，其中oracle采用的是otl进行连接(otl可以直接在网上下载)，oracle12c已经测试可以使用。

本文的数据库连接池大体思路为：

预分配一定数量的数据库连接，使用完一个连接归还一个连接，这样有个弊端就是需要根据配置去分配，优势是能够合理利用资源。和线程池处理逻辑类似。

（需要源码和讲解可联系QQ：123011785）

下面附注连接池的代码（注意根据makefile需要更具当前使用的数据库去创建连接池）。

1、ConnectPool.h代码：

////////////////////////////////////////////////////////////////////////////////
// 版权说明,2010-2020,
////////////////////////////////////////////////////////////////////////////////
// 文件名 : ConnectPool.h
// 作 者 :
// 版 本 : 1.0
// 日 期 : 2010-01-15
// 功能描述: 通过OTL封装连接数据库
// 其他说明:
//
// 修改历史:
// 日期（YYYY-MM-DD） 版本修改人 修改内容
// 2016-07-08 1.0 宋小文初始创建
////////////////////////////////////////////////////////////////////////////////
#if !defined(__CONNECT_POOL_H__)
#define __CONNECT_POOL_H__


#include "TypeDef.h"
#include "MyList.h"
#include "MyThread.h"
#include "SysIf.h"
#include "jthread.h"
#include <unistd.h>
#include <stdlib.h>
#include "Log.h"
#ifdef DATABASE_CONNECT_ORACLE
#define OTL_ORA10G //Compile OTL 4.0/OCI10g
#define OTL_ORA_UTF8 //Enable UTF8 OTL for OCI10g
#include "otlv4.h" //Include the OTL 4.0 header file
#else
#include "MysqL.h"
#endif


#define POLL_OVERFULL_CONNECT_TIME (10 * 60)


#ifdef DATABASE_CONNECT_ORACLE
/* Database connect struct */
typedef struct
{
//OTL database connect handle
otl_connect DBHdl;
//Data buffer to access database
char QueryStr[DB_QUERY_STR_MAX_LEN+1];
//OTL database out stream
otl_stream OS;
//OTL and oracle error code
int ErrorCode;
}DBConn_T;
#else
/* Database connect struct */
typedef struct
{
//MysqL handle
MysqL *pMysqL;
//Data buffer to access database
char QueryStr[DB_QUERY_STR_MAX_LEN+1];


int ErrorCode;
}DBConn_T;
#endif


using namespace jthread;


/* Database connector pool */
class CConnectPool:public JThread
{
public:
CConnectPool(int nMin,int nMax,DBConnectorCfg_T* DBCfg = NULL,DBConnectorCfg_T* StandbyDBCfg = NULL);
~CConnectPool(void);

/* Init database poll */
int DBInit(int num,DBConnectorCfg_T *pDBConnCFG = NULL);
/* Close database poll */
void DBClose();


DBConnectorCfg_T *GetUseDBConnCfg(void);
int ConnectDB(void);

//Conncet Poll operation interfaces
DBConn_T *GetConn(void);
void PutConn(DBConn_T *pDBConn);
void DelOverFullConn(void);

//Get a class instance
static CConnectPool *GetInstance(DBConnectorCfg_T* DBCfg = NULL,DBConnectorCfg_T* StandbyDBCfg = NULL,int minconn = 0,int maxconn = 0)
{
//
if((minconn <= 0) || (maxconn <= 0) || (minconn > maxconn))
{
minconn = 10;
maxconn = 30;
}
static CConnectPool ConnectPool(minconn,maxconn,DBCfg,StandbyDBCfg);

return &ConnectPool;
}

/* Debug Output */
void PrintConn(void);
private:
void PollConnectDB(void);
//Jstream Entry Point
void* Thread(void);
void ThreadQuit(void);


bool ExitFlag;
public:
//To execute sql case and set result
int Execute(DBConn_T*& pDBConn,const char FileName[],const int Line);


int OpenConnCount;
protected:
//Connec lower and upper limit
int MinConn;
int MaxConn;

private:
//Lock operation for connection poll (Note: GetConn() detach DBConn,so it isn't required to lock at outside but lock at inside.)
CCritSec CritSec;
//Multi-connected list
MyList<DBConn_T> ConnList;
DBConnectorCfg_T DBConnCFG;


bool m_bUseStandbyDB;
DBConnectorCfg_T StandbyDBConnCFG;

//Delete overfull timeout
time_t DelConnTime;
};


/* Database connect execute sql function */
#define DBCExec(X) CConnectPool::GetInstance()->Execute(X,__FILE__,__LINE__)


/* User database connect struct */
class CDBUserConn
{
public:
CDBUserConn(void)
{
//Get a connection to database
pDBConn = CConnectPool::GetInstance()->GetConn();
if (NULL == pDBConn)
{
DBGPrint(ERROR_LEVEL,ERROR_LEVEL,"%s %d:Get a connection Failed from connect pool!\n",__LINE__);
}
}
~CDBUserConn(void)
{
if (pDBConn != NULL)
{
//otl_error_code_35: OTL not connected
//ORA-03114: not connected to ORACLE
//if ( (0 == pDBConn->DBHdl.connected) || (otl_error_code_35 == pDBConn->ErrorCode) || (3114 == pDBConn->ErrorCode) )
#if 0
if (pDBConn->ErrorCode != 0)
{
printf("%s %d:Current DBConn was lost! Delete it.\n",__LINE__);

pDBConn->DBHdl.logoff();
delete pDBConn;
}
else
#endif
{

//Put a connection to database
CConnectPool::GetInstance()->PutConn(pDBConn);
}

pDBConn = NULL;
}
}

DBConn_T* pDBConn;
};


#endif //__CONNECT_POOL_H__

2、ConnectPool.cpp代码如下：

//////////////////////////////////////////////////////////////////////////////// // 版权说明,//////////////////////////////////////////////////////////////////////////////// // 文件名 : ConnectPool.cpp // 作 者 : // 版 本 : 1.0 // 日 期 : 2010-01-15 // 功能描述: 通过OTL封装连接数据库 // 其他说明: // // 修改历史: // 日期（YYYY-MM-DD）版本修改人修改内容 //2016-07-08 1.0宋小文初始创建 //////////////////////////////////////////////////////////////////////////////// #include <iostream> #include <unistd.h> #include "ConnectPool.h" CConnectPool::CConnectPool(int nMin,DBConnectorCfg_T* DBCfg,DBConnectorCfg_T* StandbyDBCfg) { #ifdef DATABASE_CONNECT_ORACLE ExitFlag = false; m_bUseStandbyDB = false; MinConn = nMin; MaxConn = nMax; OpenConnCount = MinConn; // if(DBCfg != NULL) { DBConnCFG = *DBCfg; } if(StandbyDBCfg != NULL) { StandbyDBConnCFG = *StandbyDBCfg; m_bUseStandbyDB = StandbyDBConnCFG.bUseStandbyDB; } char *pOracleHome = getenv("ORACLE_HOME"); char OracleHome[256]; //检查ORACLE_HOME的环境变量。1.首先用系统的Oracle环境变量。2.其次用配置的Oracle环境变量。3.如果系统和配置都为空，程序挂起。 if (NULL == pOracleHome) { DBGPrint(ERROR_LEVEL,"%s: Error: pOracleHome is null pointer at %s:%d! \n",__FUNCTION__,__LINE__); exit(-1); } snprintf(OracleHome,128,"ORACLE_HOME=%s",pOracleHome); //putenv(const_cast<char*>("NLS_LANG=SIMPLIFIEDCHINESE_CHINA.ZHS16GBK") ); putenv(const_cast<char*>("NLS_LANG=.AL32UTF8") ); //Initialize OCI environment,1--多线程模式 otl_connect::otl_initialize(1); ConnectDB(); //Note: Connect Pool can create when a http connection is incoming. so never mind fail here. DelConnTime = (time_t)GetClockTime(); //启动线程去定时连接数据库 if(m_bUseStandbyDB) { Start(); } #else MinConn = nMin; MaxConn = nMax; OpenConnCount = MinConn; // if(DBCfg != NULL) { DBConnCFG = *DBCfg; } //Init Connect Pool DBInit(MinConn,DBCfg); //Note: Connect Pool can create when a http connection is incoming. so never mind fail here. DelConnTime = (time_t)GetClockTime(); #endif } CConnectPool::~CConnectPool(void) { #ifdef DATABASE_CONNECT_ORACLE if(m_bUseStandbyDB) { ThreadQuit(); } //Close Connector Pool DBClose(); #else DBClose(); #endif } /****************************************************************************** * 函数介绍: 初始化连接数据库接口，默认初始化10个线程接口 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ int CConnectPool::DBInit(int num,DBConnectorCfg_T *pDBConnCFG) { #ifdef DATABASE_CONNECT_ORACLE //Lock connect list CAutoLock lock(&this->CritSec); for (int i=0; i<num; i++) { DBConn_T *pDBConn = new DBConn_T; if (NULL == pDBConn) { DBGPrint(ERROR_LEVEL,"%s %d:Failed to new DBConn_T!",__LINE__); return -1; } try { char DBLinkUrl[256+1]; if(pDBConnCFG != NULL) { snprintf(DBLinkUrl,256,"%s/%s@%s:%d/%s",pDBConnCFG->username,pDBConnCFG->passwd,pDBConnCFG->host,pDBConnCFG->port,pDBConnCFG->DBName); } else { snprintf(DBLinkUrl,DBConnCFG.username,DBConnCFG.passwd,DBConnCFG.host,DBConnCFG.port,DBConnCFG.DBName); } pDBConn->DBHdl.rlogon(DBLinkUrl); } catch (otl_exception& p) { cerr<<p.msg<<endl; //print out error message cerr<<p.stm_text<<endl; //print out sql that caused the error cerr<<p.var_info<<endl; //print out the variable that caused the error delete pDBConn; return -1; } pDBConn->ErrorCode = 0; if(pDBConnCFG != NULL) { DBGPrint(BREAK_LEVEL,BREAK_LEVEL,"%s %d:<%d> Succ to connect to database <%s:%d %s>.",__LINE__,i+1,pDBConnCFG->DBName); } else { DBGPrint(BREAK_LEVEL,DBConnCFG.DBName); } //Insert at the header if (-1 == ConnList.insert(pDBConn,0) ) { DBGPrint(ERROR_LEVEL,"%s %d:Failed to insert connect list!\n",__LINE__); pDBConn->DBHdl.logoff(); delete pDBConn; return -1; } } DBGPrint(BREAK_LEVEL,"%s %d:Create succ for connect pool of datebase,num=%d,OpenConnCount=%d.",num,OpenConnCount); #else //Lock connect list CAutoLock lock(&this->CritSec); for (int i=0; i<num; i++) { MysqL *pMysqL = MysqL_init(NULL); if (NULL == pMysqL) { printf("Failed to init MysqL handle!\n"); return -1; } //Set reconnection option bool ReconnFlag = 1; //1 - reconnect if (MysqL_options(pMysqL,MysqL_OPT_RECONNECT,&ReconnFlag) != 0) { printf("Failed to set MysqL_OPT_RECONNECT for MysqL_options,Error: %s!\n",MysqL_error(pMysqL) ); MysqL_close(pMysqL); return -1; } //Connect database UINT32 ClientFlag = CLIENT_MULTI_STATEMENTS; if(pDBConnCFG != NULL) { if (MysqL_real_connect(pMysqL,pDBConnCFG->DBName,NULL,ClientFlag) == NULL) { printf("Failed to connect to database,MysqL_error(pMysqL) ); MysqL_close(pMysqL); return -1; } } else { if (MysqL_real_connect(pMysqL,DBConnCFG.DBName,MysqL_error(pMysqL) ); MysqL_close(pMysqL); return -1; } } printf("<%d> Succ to connect to database <%s:%d %s>.\n",pDBConnCFG->DBName); DBConn_T *pDBConn = new DBConn_T; if (NULL == pDBConn) { printf("Failed to new DBConn_T!\n"); MysqL_close(pMysqL); return -1; } pDBConn->pMysqL = pMysqL; MysqL_set_character_set(pDBConn->pMysqL,"utf8"); //Insert at the header if (-1 == ConnList.insert(pDBConn,0) ) { printf("%s:Failed to insert connect list!\n",__FUNCTION__); MysqL_close(pMysqL); delete pDBConn; return -1; } } printf("%s %d:Create succ for connect pool of datebase,OpenConnCount=%d.\n",OpenConnCount); #endif return 0; } /****************************************************************************** * 函数介绍: 断开数据库的所有连接 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ void CConnectPool::DBClose() { #ifdef DATABASE_CONNECT_ORACLE //Lock connect list CAutoLock lock(&this->CritSec); for (int i=0; i<ConnList.size(); i++) { DBConn_T *pDBConn = ConnList.getElement(i); if (pDBConn != NULL) { pDBConn->OS.close(); DBGPrint(BREAK_LEVEL,"Close database connection at <%d>. \n",i+1); pDBConn->DBHdl.logoff(); } } //Clear the list ConnList.clear(); #else //Lock connect list CAutoLock lock(&this->CritSec); for (int i=0; i<ConnList.size(); i++) { DBConn_T *pDBConn = ConnList.getElement(i); if (pDBConn != NULL) { if (pDBConn->pMysqL != NULL) { printf("Close database connection at <%d>. \n",i+1); MysqL_close(pDBConn->pMysqL); } } } //Clear the list ConnList.clear(); #endif } /****************************************************************************** * 函数介绍: 获取当前使用的数据库配置 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ DBConnectorCfg_T *CConnectPool::GetUseDBConnCfg(void) { #ifdef DATABASE_CONNECT_ORACLE if(!m_bUseStandbyDB || DBConnCFG.bUse) { return &DBConnCFG; } if(StandbyDBConnCFG.bUse) { return &StandbyDBConnCFG; } DBGPrint(ERROR_LEVEL,"%s:get conn dbcfg is error!!!\n",__FUNCTION__); #endif return NULL; } /****************************************************************************** * 函数介绍: 重新连接数据库，在主数据库和备数据库之间选择 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ int CConnectPool::ConnectDB(void) { //优先选择连接主数据库 int iRet = -1; #ifdef DATABASE_CONNECT_ORACLE iRet = DBInit(MinConn,&DBConnCFG); //表示主数据库连接失败; 需要去连接备用数据库 if(m_bUseStandbyDB && (iRet < 0)) { //需要关闭之前的所有连接 DBClose(); iRet = DBInit(MinConn,&StandbyDBConnCFG); //如果连接备用数据库也失败；需要重新连接主数据库 if(iRet < 0) { iRet = DBInit(MinConn,&DBConnCFG); } else { DBConnCFG.bUse = false; StandbyDBConnCFG.bUse = true; return iRet; } } DBConnCFG.bUse = true; StandbyDBConnCFG.bUse = false; #endif return iRet; } /****************************************************************************** * 函数介绍: 获取一个数据库连接 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ DBConn_T *CConnectPool::GetConn(void) { #ifdef DATABASE_CONNECT_ORACLE DBConn_T *pDBConn = NULL; int WaitTimes = 2; while (true) { CritSec.Lock(); pDBConn = ConnList.getElement(0); if (NULL == pDBConn) { DBGPrint(ERROR_LEVEL,"%s %d: getElement fail,WaitTimes:%d size:%d,MaxConn:%d!\n",WaitTimes,ConnList.size(),MaxConn); if (WaitTimes == 1) { WaitTimes--; if (OpenConnCount <= MaxConn) { CritSec.Unlock(); //Increase Connect Pool DBInit(5,GetUseDBConnCfg()); OpenConnCount += 5; usleep(200*1000); DBGPrint(ERROR_LEVEL,"%s %d: reInit,OpenConnCount:%d,OpenConnCount,MaxConn); continue; } } else if (0 == WaitTimes) { DBGPrint(ERROR_LEVEL,"%s %d:No database handle in Connector Pool,%d/%d!\n",MaxConn); //Exit while dead loop,get DBConn element fail! CritSec.Unlock(); break; } WaitTimes--; DBGPrint(ERROR_LEVEL,"%s %d: No database handle in Connector Pool,used up,please waiting %d/%d ...!\n",MaxConn); CritSec.Unlock(); usleep(200*1000); } else { ConnList.remove(pDBConn); //printf("%s %d: pDBConn:%p,remove over size:%d!\n",pDBConn,ConnList.size()); CritSec.Unlock(); break; } } return pDBConn; #else DBConn_T *pDBConn = NULL; int WaitTimes = 4; while (true) { CritSec.Lock(); pDBConn = ConnList.getElement(0); if (NULL == pDBConn) { printf("%s %d: getElement fail,MaxConn); if (WaitTimes == 1) { WaitTimes--; if (OpenConnCount <= MaxConn) { CritSec.Unlock(); //Increase Connect Pool DBInit(5); OpenConnCount += 5; usleep(200*1000); printf("%s %d: reInit,MaxConn); continue; } } else if (0 == WaitTimes) { printf("%s %d:No database handle in Connector Pool,get DBConn element fail! CritSec.Unlock(); break; } WaitTimes--; printf("%s %d: No database handle in Connector Pool,ConnList.size()); CritSec.Unlock(); break; } } return pDBConn; #endif } /****************************************************************************** * 函数介绍: 放入一个数据库连接的连接 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ void CConnectPool::PutConn(DBConn_T *pDBConn) { #ifdef DATABASE_CONNECT_ORACLE //Check input parameters exception if (NULL == pDBConn) { DBGPrint(ERROR_LEVEL,"%s %d:input params(pDBConn = 0x%ld)!",(long)pDBConn); return; } //Close stream pDBConn->OS.close(); //Lock connect list CAutoLock lock(&this->CritSec); //Insert at the header if (-1 == ConnList.insert(pDBConn,"%s %d:Failed to insert connect list!",__LINE__); pDBConn->DBHdl.logoff(); delete pDBConn; return; } #else //Check input parameters exception if (NULL == pDBConn) { printf("%s %d:input params(pDBConn = 0x%ld)!\n",(long)pDBConn); return; } //Lock connect list CAutoLock lock(&this->CritSec); //Insert at the header if (-1 == ConnList.insert(pDBConn,0) ) { printf("%s:Failed to insert connect list,pDBConn!\n",__FUNCTION__); if (pDBConn->pMysqL != NULL) MysqL_close(pDBConn->pMysqL); delete pDBConn; return; } #endif } /****************************************************************************** * 函数介绍: 打印当前总的数据库连接数 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ void CConnectPool::PrintConn(void) { //Lock connect list CAutoLock lock(&this->CritSec); printf("Total connection number is %d in the pool.",ConnList.size()); } /****************************************************************************** * 函数介绍: 通过数据库连接 执行数据库sql语句 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ int CConnectPool::Execute(DBConn_T*& pDBConn,const int Line) { //Check input parameters exception if (NULL == pDBConn) { DBGPrint(ERROR_LEVEL,(long)pDBConn); return -1; } #ifdef DATABASE_CONNECT_ORACLE //提交sql语句去执行，捕捉错误信息。 try { pDBConn->OS.close(); pDBConn->OS.open(1,pDBConn->QueryStr,pDBConn->DBHdl); //pDBConn->OS.set_flush(false); //pDBConn->OS.set_commit(0); pDBConn->DBHdl.commit(); } catch (otl_exception& e) { #if 0 cerr<<e.msg<<endl; //print out error message cerr<<e.stm_text<<endl; //print out sql that caused the error cerr<<e.var_info<<endl; //print out the variable that caused the error #endif pDBConn->ErrorCode = e.code; //错误为连接oracle失败 需要去连接备数据库 DBGPrint(FATAL_LEVEL,FATAL_LEVEL,"Error: Call %s from Connect Pool at %s:%d,\nError.Msg:%s Error.stm_text:%s \nError.var_info:%s \nError.Code:%d\n",FileName,Line,e.msg,e.stm_text,e.var_info,pDBConn->ErrorCode); return -1; } #else pDBConn->ErrorCode = MysqL_real_query(pDBConn->pMysqL,strlen(pDBConn->QueryStr) ); if (pDBConn->ErrorCode != 0) { DBGPrint(M_DBTunnel,"%s:%d DBOper Failed,QueryStr = %s,Error: %s!",MysqL_error(pDBConn->pMysqL) ); return -1; } #endif return 0; } /****************************************************************************** * 函数介绍: 删除所有后面派生的数据库连接 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ void CConnectPool::DelOverFullConn(void) { #ifdef DATABASE_CONNECT_ORACLE time_t now = (time_t)GetClockTime(); if (now - DelConnTime >= POLL_OVERFULL_CONNECT_TIME) { DelConnTime = now; //Lock connect list CAutoLock lock(&this->CritSec); if (ConnList.size() <= MinConn) return; //Delete overfull connections int DelNum = ConnList.size() - MinConn; for (int i=0; i<DelNum; i++) { printf("ma%s %d:delect a sql connection from the pool,its number is %d.",ConnList.size() ); DBConn_T *pDBConn = ConnList.getElement(0); if (pDBConn != NULL) { pDBConn->DBHdl.logoff(); ConnList.erase(0); } } } #else time_t now = (time_t)GetClockTime(); if (now - DelConnTime >= POLL_OVERFULL_CONNECT_TIME) { DelConnTime = now; //Lock connect list CAutoLock lock(&this->CritSec); if (ConnList.size() <= MinConn) return; //Delete overfull connections int DelNum = ConnList.size() - MinConn; for (int i=0; i<DelNum; i++) { printf("%s: delect a sql connection from the pool,its number is %d.\n",ConnList.size() ); DBConn_T *pDBConn = ConnList.getElement(0); if (pDBConn != NULL) { if (pDBConn->pMysqL != NULL) MysqL_close(pDBConn->pMysqL); ConnList.erase(0); } } } #endif } /****************************************************************************** * 函数介绍: 定时连接数据库 如果失败直接退出程序 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ void CConnectPool::PollConnectDB(void) { #ifdef DATABASE_CONNECT_ORACLE DBConn_T DBConnect; char DBLinkUrl[256+1] = ""; try { snprintf(DBLinkUrl,GetUseDBConnCfg()->username,GetUseDBConnCfg()->passwd,GetUseDBConnCfg()->host,GetUseDBConnCfg()->port,GetUseDBConnCfg()->DBName); DBConnect.DBHdl.rlogon(DBLinkUrl); } catch (otl_exception& p) { cerr<<p.msg<<endl;//print out error message cerr<<p.stm_text<<endl; //print out sql that caused the error cerr<<p.var_info<<endl; //print out the variable that caused the error //错误为连接oracle失败需要去连接备数据库 int pid = getpid(); printf("xxxxxxxxxError: Call %s from Connect Pool Failed pid<%d>\n",pid); //连接失败 直接退出程序 char killpid[128] = ""; sprintf(killpid,"kill -9 %d",pid); system(killpid); exit(0); } //需要断开此连接 DBConnect.DBHdl.logoff(); #endif } /****************************************************************************** * 函数介绍: JThread构造的线程开始接口 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ void* CConnectPool::Thread(void) { #ifdef DATABASE_CONNECT_ORACLE ThreadStarted(); while (!ExitFlag) { PollConnectDB(); //每隔5秒 连接一次数据库 判断是否连接成功 sleep(5); } #endif return NULL; } /****************************************************************************** * 函数介绍: JThread的线程退出接口 * 输入参数: * * * 输出参数: 无 * 返回值 : 无 *****************************************************************************/ void CConnectPool::ThreadQuit(void) { #ifdef DATABASE_CONNECT_ORACLE ExitFlag = true; int times = 5; while ( JThread::IsRunning() && times-- > 0 ) { usleep(100000); } if (times < 0) { printf("JSalarm::ThreadQuit Fail.\n"); } #endif }