c++ 连接mysql类

文章来源：http://hi.baidu.com/lbxthinker/blog/item/9b837b0f8a306fdb7acbe113.html

 

connpool.h文件

/**
*数据库连接池(单例模式)
*
*@authour libingxiang
*
*@email [e-mail]
**/
#ifndef CONN_POOL_H
#define CONN_POOL_H

#include <mysql_connection.h>
#include <mysql_driver.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include "push.h"
#include "mutex.h"

using namespace std;

class ConnPool{
private:
deque<sql::Connection *> conns;//连接队列
int curSize;//当前队列中路连接数目
int maxSize;//最大连接数目
sql::ConnectOptionsMap connectionProperties;
Mutex *lock;//连接队列互斥锁
static ConnPool * connPool;
sql::Driver * driver;//mysql connector C++ driver
sql::Connection * CreateConnection();//创建一个连接
void TerminateConnection(sql::Connection * conn);//终止一个连接
void Init(int initialSize);//初始化连接池
void Destroy();//销毁连接池
protected:
ConnPool(string host,string user,string password,int maxSize);
public:
~ConnPool();
sql::Connection * GetConnection();//获取一个连接
void ReleaseConnection(sql::Connection * conn);//释放一个连接
sql::Connection * GetConnectionTry(int maxNum);//GetConnection的加强版，maxNum代表重试次数
static ConnPool * GetInstance();//获取一个ConnPool对象实例
};

#endif

connpool.cpp文件

/**
*数据库连接池
*
*@authour libingxiang
*
*@email [e-mail]
**/
#include <stdexcept>
#include "connpool.h"
#include "config.h"

using namespace std;
extern Config *config;

ConnPool * ConnPool::connPool = NULL;

ConnPool::ConnPool(string host,string user,string password,int maxSize){
connectionProperties["hostName"] = host;
connectionProperties["userName"] = user;
connectionProperties["password"] = password;
connectionProperties["OPT_CONNECT_TIMEOUT"] = 600;
connectionProperties["OPT_RECONNECT"] = true;

this->maxSize = maxSize;
this->lock = new Mutex();
this->curSize = 0;
//初始化driver
try{
this->driver = sql::mysql::get_driver_instance(); //这里不是线程安全的
}
catch(sql::SQLException &e){
string errorMsg = string("SQLException: ") + e.what() + string(" MySQL error code: ") + int_to_string(e.getErrorCode()) + string(" SQLState ") + e.getSQLState();
Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
}
catch(std::runtime_error &e){
string errorMsg = string("runtime_error: ") + e.what(); 
Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
}
//初始化连接池
this->Init(maxSize/2);
}

ConnPool::~ConnPool(){ 
this->Destroy();
delete lock;
}

ConnPool *ConnPool::GetInstance(){
if(connPool == NULL) {
connPool = new ConnPool(config->GetVar("db_host"),config->GetVar("db_user"),config->GetVar("db_password"),string_to_int(config->GetVar("max_db_conn_size")));
}

return connPool;
}

void ConnPool::Init(int size){
sql::Connection * conn ;
lock->Lock();

for(int i = 0; i < size ;){
conn = this->CreateConnection();
if(conn){
i++; 
conns.push_back(conn);
++curSize;
}
else{
Log::Write(__FILE__,__FUNCTION__,__LINE__,"Init connpooo fail one");
}
}

lock->UnLock(); 
}

void ConnPool::Destroy(){
deque<sql::Connection *>::iterator pos;

lock->Lock();

for(pos = conns.begin(); pos != conns.end();++pos){
this->TerminateConnection(*pos);
}

curSize = 0;
conns.clear();

lock->UnLock(); 
}

sql::Connection * ConnPool::CreateConnection(){//这里不负责curSize的增加
sql::Connection *conn;

try{
conn = driver->connect(connectionProperties);
Log::Write(__FILE__,__FUNCTION__,__LINE__,"create a mysql conn");
return conn;
}
catch(sql::SQLException &e){
string errorMsg = string("SQLException:") + e.what() + string(" MySQL error code: ") + int_to_string(e.getErrorCode()) + string(" SQLState ") + e.getSQLState();
Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
return NULL;
}
catch(std::runtime_error &e){
string errorMsg = string("runtime_error: ") + e.what(); 
Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
return NULL;
}
}

void ConnPool::TerminateConnection(sql::Connection * conn){
if(conn){
try{
conn->close();
}
catch(sql::SQLException &e){
string errorMsg = string("SQLException:") + e.what() + string(" MySQL error code: ") + int_to_string(e.getErrorCode()) + string(" SQLState ") + e.getSQLState();
Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
}
catch(std::runtime_error &e){
string errorMsg = string("runtime_error: ") + e.what(); 
Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);
}

delete conn;
}
}

sql::Connection * ConnPool::GetConnection(){
sql::Connection * conn;

lock->Lock();

if(conns.size() > 0){//有空闲连接,则返回
conn = conns.front();
conns.pop_front();

if(conn->isClosed()){ //如果连接关闭,则重新打开一个连接
Log::Write(__FILE__,__FUNCTION__,__LINE__,"a mysql conn has been closed");
delete conn;
conn = this->CreateConnection();
}

if(conn == NULL){ //创建连接不成功
--curSize;
}
lock->UnLock();

return conn;
}
else{
if(curSize < maxSize){//还可以创建新的连接
conn = this->CreateConnection();
if(conn){
++curSize;
lock->UnLock();
return conn;
}
else{
lock->UnLock();
return NULL;
}
}
else{//连接池已经满了
lock->UnLock();
return NULL;
}
} 
}

void ConnPool::ReleaseConnection(sql::Connection * conn){
if(conn){
lock->Lock();

conns.push_back(conn);

lock->UnLock();
}
}

sql::Connection * ConnPool::GetConnectionTry(int maxNum){
sql::Connection * conn;

for(int i = 0; i < maxNum; ++i){
conn = this->GetConnection();
if(conn){
return conn;
}
else {
sleep(2);
}
}

return NULL;
}

 

 

实现是基于mysql connector C++ api的

magicdb.h文件

/**
*mysql connector C++ 封装
*
*@authour libingxiang
*
*@email [e-mail]
*
**/
#ifndef MAGICDB_H
#define MAGICDB_H

#include <mysql_connection.h>
#include <mysql_driver.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include "push.h"
#include "connpool.h"

using namespace std;

class MagicDB {
private:
ConnPool *connPool;
public:
MagicDB();
~MagicDB();
sql::ResultSet *Query(const string &database,const string &sql);
int Execute(const string &database,const string &sql);
};

#endif

magicdb.cpp文件

/**
*mysql connector C++ 封装
*
*@authour libingxiang
*
*@email [e-mail]
*
**/
#include <stdexcept>
#include "magicdb.h"
#include "log.h"
#include "util.h"

using namespace std;

MagicDB::MagicDB(){
this->connPool = ConnPool::GetInstance();
}

sql::ResultSet * MagicDB::Query(const string &database,const string &sql){
sql::Connection *conn;
sql::Statement *stmt;
sql::ResultSet *res;

try{
conn = connPool->GetConnection(); //从连接池中获取连接
if(conn == NULL){
conn = connPool->GetConnectionTry(2);
if(conn == NULL){
Log::Write(__FILE__,__FUNCTION__,__LINE__,"query get conn error");
return NULL;
}
}

conn->setSchema(database);
stmt = conn->createStatement();
res = stmt->executeQuery(sql.c_str());

connPool->ReleaseConnection(conn);//将连接放入连接池中

delete stmt;
return res;
}
catch(sql::SQLException &e){
string errorMsg = string("SQLException:") + e.what() + string(" MySQL error code: ") + int_to_string(e.getErrorCode()) + string(" SQLState ") + e.getSQLState();
Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);

connPool->ReleaseConnection(conn);//将连接放入连接池中

delete stmt;
delete res;
return NULL;
}
catch(std::runtime_error &e){
string errorMsg = string("runtime_error: ") + e.what(); 
Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);

connPool->ReleaseConnection(conn);//将连接放入连接池中

delete stmt;
delete res;
return NULL;
}
}

int MagicDB::Execute(const string &database,const string &sql){
sql::Statement * stmt;
sql::Connection *conn;

try{
conn = connPool->GetConnection(); //从连接池中获取连接
if(conn == NULL){
conn = connPool->GetConnectionTry(2);
if(conn == NULL){
Log::Write(__FILE__,__FUNCTION__,__LINE__,"query get conn error");
return -3;
}
}

conn->setSchema(database);
stmt = conn->createStatement();
stmt->execute(sql.c_str());

connPool->ReleaseConnection(conn);//将连接放入连接池中

delete stmt;
return 0;
}
catch(sql::SQLException &e){
string errorMsg = string("SQLException:") + e.what() + string(" MySQL error code: ") + int_to_string(e.getErrorCode()) + string(" SQLState ") + e.getSQLState();
Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);

connPool->ReleaseConnection(conn);//将连接放入连接池中

delete stmt;
return -1;
}
catch(std::runtime_error &e){
string errorMsg = string("runtime_error: ") + e.what(); 
Log::Write(__FILE__,__FUNCTION__,__LINE__,errorMsg);

connPool->ReleaseConnection(conn);//将连接放入连接池中

delete stmt;
return -2;
}
}

MagicDB::~MagicDB(){
delete connPool;
}