使用Pro C++实现数据库连接池采用多线程方式访问oracle数据库

数据库连接作为一种资源，我们的应用必须对之进行行之有效的管理。我们在访问数据库的时候，一般传统上采用先建立连接，然后使用该连接访问数据库，在使用完毕后，关闭该连接。这是我们经常采用的方法。该方法的好处是使用简单，不用对连接进行任何管理。但随之带来的缺点也就出现了，在应用需要频繁访问数据库的时候，这种方法就会使程序的效率十分低下，甚至有时候是不能满足应用的需要的。随着数据库连接池技术出现了，我们的应用根据访问数据库的频率动态决定创建连接的数量，以及决定在适当的时刻可以关闭一些数据库连接，用以节省这种资源。笔者最近在参考了网上大量代码的同时，实现了如何利用Pro C++使用数据库连接池完成了多线程对oracle数据库的访问。本着来源于网络，共享与网络的精神，和大家共同探讨，其中笔者的大部分代码来源是黄剑锋先生的共享代码，在此深表感谢。实现的基本功能如下：
1：可以设定最小的数据库连接数。
2：可以设定最大的数据库连接数。
3：当数据库某个连接空闲时间多长时间后，该连接池会自动断开连接以节省数据库连接资源。
4：提供了每个连接被使用的次数接口，方便统计和分析各个连接的情况。
5：提供了每个连接从上次访问完毕，懂查看的时候为止，已经空闲的时长，以秒为单位。
6：可以动态访问数据库存储过程，即存储过程的名字可以动态变化的。
 
CConnection：
#pragma once

#include <string>
#include <ace/OS.h>
#include <ace/Task.h>

#define RT_OK   0
#define RT_NG  -1

#define DB_CORE	  0
#define DB_CORE_1 DB_CORE

using namespace std;

/*数据库错误原因*/
#define		RT_CONNECT_FAILED		-2
#define		RT_SOURCE_BUSY 			-3
#define		RT_NO_DATA_FOUND 		-4 		/*when fetch cursor over or cursor is empty*/
#define		RT_DOUBLE_KEY 			-5  	/*unique constraint violated*/
#define		RT_OTHER_ERROR 			-99


/****************************************数据库单连接******************************************/

class CConnection
{
public:
	CConnection();
	CConnection(std::string db_string, std::string db_name);
	virtual ~CConnection();
	void settimerid(int timerid);
	int gettimerid();
	void addinvoke();
	void set(std::string db_string, std::string db_name);
	void set_db_string(std::string db_string);
	void set_db_name(std::string db_name);
	string get_db_string();
	string get_db_name();
	void output();
	void setidletimepoint();
	void* getctctx();
	static int multi_thread();
	int start();
	int stop();
	int connect();
	int disconnect();
	int reconnect();
	bool is_connected();
	/*示例*/
	int DML_DEMO_1();
	int DML_DEMO_2(int i);
	int DML_DEMO_3(int i);
	int DML_DEMO_4(char* inputparam, char* outputparam);
	int DML_DEMO_5(char* spname, char* inputparam, char* outputparam);
private:
	bool db_connected_;
	int  invoke_;
	time_t  idletimepoint_;
	int timerid_;
	/*数据库连接上下文*/
	void * ctx_;
	std::string db_string_; /*数据库连接串 user/pwd@sid*/
	std::string db_name_;   /*数据库连接别名*/
	int SqlError(void *_sqlca, const char *_FunName);
};


/*==================================================================================================
 * 项目名称: Proc++程序模板
 *     功能: 实现Proc预编译程序设计模式
 *     作者: huangjf
 *     联系: [email protected]
 * 最近修改: 2010-7-15
 *     版本: v2.0.1
 ==================================================================================================*/

#include <list>
#include <vector>
#include <algorithm>
#include <ace/OS.h>
#include <ace/FILE_Addr.h>
#include <ace/FILE_Connector.h>
#include <ace/FILE_IO.h>
#include "Logger.h"
#include "Connection.h"

CConnection::CConnection()
{ 
	CLogger::createinstance()->logdebugmsg("(+)connection address=%08x\n", this);
	db_connected_ = false;
	ctx_ = NULL;
	db_string_ = "NULL";
	db_name_ = "NULL";
	invoke_ = 0;
	idletimepoint_ = time(NULL);
	timerid_ = -1;
}

CConnection::CConnection(std::string db_string, std::string db_name)
{
	CLogger::createinstance()->logdebugmsg("(+)connection address=%08x\n", this);
	db_connected_ = false;
	ctx_ = NULL;
	db_string_ = db_string;
	db_name_ = db_name;
	invoke_ = 0;
	idletimepoint_ = time(NULL);
	timerid_ = -1;
}

CConnection::~CConnection()
{
	CLogger::createinstance()->logdebugmsg("(-)connection address=%08x\n", this);
	db_connected_ = false;
}


void CConnection::settimerid(int timerid)
{
	timerid_ = timerid;
}

int CConnection::gettimerid()
{
	return timerid_;
}

void CConnection::addinvoke()
{
	invoke_++;
}

void CConnection::set(std::string db_string, std::string db_name)
{
	db_string_ = db_string;
	db_name_   = db_name;
}

void CConnection::set_db_string(std::string db_string)
{
	db_string_ = db_string;
}

void CConnection::set_db_name(std::string db_name)
{
	db_name_ = db_name;
}

string CConnection::get_db_string()
{
	return db_string_;
}

void CConnection::setidletimepoint()
{
	time(&idletimepoint_);
}

void CConnection::output()
{
	CLogger::createinstance()->logdebugmsg("connection=%08x invoke=%d idletime=%d\n", this, invoke_, time(NULL) - idletimepoint_);
}

void*  CConnection::getctctx()
{
	return ctx_;
}

string CConnection::get_db_name()
{
	return db_name_;
}

int CConnection::multi_thread()
{
	#undef SQLCA
	EXEC SQL INCLUDE SQLCA;

	EXEC SQL WHENEVER SQLERROR GOTO sqlerr;

	EXEC SQL ENABLE THREADS;
	
	CLogger::createinstance()->logdebugmsg("start database muti thread mode：success\n");
	return RT_OK;
	
sqlerr:
	CLogger::createinstance()->logdebugmsg("start database muti thread mode：failure\n");
	return RT_NG;
}

int CConnection::start()
{
	#undef SQLCA
	EXEC SQL INCLUDE SQLCA;

	EXEC SQL WHENEVER SQLERROR GOTO sqlerr;

	EXEC SQL BEGIN DECLARE SECTION;
		sql_context ctx = NULL;
	EXEC SQL END DECLARE SECTION;

	EXEC SQL CONTEXT ALLOCATE :ctx;

	this->ctx_ = ctx;

	if (this->ctx_ != NULL)
	{
		CLogger::createinstance()->logdebugmsg("create database context：success\n");
		return RT_OK;
	}
	else
	{
		CLogger::createinstance()->logdebugmsg("create database context：failure\n");
		return RT_NG;
	}

sqlerr:
	return (SqlError(&sqlca, "start()"));
}

int CConnection::stop()
{
	#undef SQLCA
	EXEC SQL INCLUDE SQLCA;

	EXEC SQL WHENEVER SQLERROR GOTO sqlerr;

	EXEC SQL BEGIN DECLARE SECTION;
		sql_context ctx = NULL;
	EXEC SQL END DECLARE SECTION;

	ctx = this->ctx_;
	
	EXEC SQL CONTEXT FREE :ctx;

	CLogger::createinstance()->logdebugmsg("destroy database context：success\n");
	return RT_OK;
	
sqlerr:
	CLogger::createinstance()->logdebugmsg("destroy database context：failure\n");
	return (SqlError(&sqlca, "stop()"));
}

int CConnection::connect()
{
    #undef SQLCA
    EXEC SQL INCLUDE SQLCA;

	EXEC SQL WHENEVER SQLERROR GOTO sqlerr;

	if (this->ctx_ == NULL) return RT_NG;
	if (this->db_connected_ == true) return RT_OK;

	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR vcConnStr[512]; /*数据库连接串 user/pwd@sid*/
		VARCHAR vcLinkName[64]; /*数据库连接别名*/
		
		sql_context ctx = NULL; /*数据库连接上下文*/
	EXEC SQL END DECLARE SECTION;

	ACE_OS::memset(&vcConnStr, 0x00, sizeof(vcConnStr));
	ACE_OS::memset(&vcLinkName, 0x00, sizeof(vcLinkName));

	ACE_OS::strncpy((char *)vcConnStr.arr, this->db_string_.c_str(), this->db_string_.length());
	vcConnStr.len = ACE_OS::strlen((char *)vcConnStr.arr);

	ACE_OS::strncpy((char *)vcLinkName.arr, this->db_name_.c_str(), this->db_name_.length());
	vcLinkName.len = ACE_OS::strlen((char *)vcLinkName.arr);

	ctx = this->ctx_;
	EXEC SQL CONTEXT USE :ctx;

	EXEC SQL CONNECT :vcConnStr AT :vcLinkName;

	this->db_connected_ = true;

	CLogger::createinstance()->logdebugmsg("connect to database [%s]：success\n", this->db_name_.c_str());
	return RT_OK;

sqlerr:
	this->db_connected_ = false;
	CLogger::createinstance()->logdebugmsg("connect to database [%s]：failure\n", this->db_name_.c_str());
	return (SqlError(&sqlca, "connect()"));
}

int CConnection::disconnect()
{
    #undef SQLCA
    EXEC SQL INCLUDE SQLCA;

	EXEC SQL WHENEVER SQLERROR GOTO sqlerr;

	if (this->ctx_ == NULL) return RT_NG;
	if (this->db_connected_ == false) return RT_OK;

	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR vcLinkName[64]; /*数据库连接别名*/
		
		sql_context ctx = NULL; /*数据库连接上下文*/
	EXEC SQL END DECLARE SECTION;

	ACE_OS::memset(&vcLinkName, 0x00, sizeof(vcLinkName));

	ACE_OS::strncpy((char *)vcLinkName.arr, this->db_name_.c_str(), this->db_name_.length());
	vcLinkName.len = ACE_OS::strlen((char *)vcLinkName.arr);

	ctx = this->ctx_;
	EXEC SQL CONTEXT USE :ctx;

	/*EXEC SQL AT :vcLinkName ROLLBACK WORK RELEASE;*/
	EXEC SQL AT :vcLinkName COMMIT WORK RELEASE;

	this->db_connected_ = false;


	CLogger::createinstance()->logdebugmsg("disconnect to database [%s]：success\n", this->db_name_.c_str());
	return RT_OK;

sqlerr:
	this->db_connected_ = false;
	CLogger::createinstance()->logdebugmsg("disconnect to database [%s]：failure\n", this->db_name_.c_str());
	return (SqlError(&sqlca, "disconnect()"));
}

int CConnection::reconnect()
{
	this->disconnect();
	
	this->connect();
	
	return this->db_connected_==true ? RT_OK : RT_NG;
}

bool CConnection::is_connected()
{
	return this->db_connected_;
}

int CConnection::SqlError(void *_sqlca, const char *_FunName)
{
	#undef SQLCA
	EXEC SQL INCLUDE SQLCA;

	EXEC SQL WHENEVER SQLERROR GOTO sqlerr;

	if (this->ctx_ == NULL) return RT_NG;
	if (this->db_connected_ == false) return RT_NG;

	int	iRet;

	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR vcLinkName[64]; /*数据库连接别名*/
		
		sql_context ctx = NUL