基于MySQL Connector/C++的数据库连接池

本文来自：http://www.oschina.net/code/snippet_583625_19818

/*
 *File: connection_pool.h
 *Author: csc
 */
 
#ifndef _CONNECTION_POOL_H
#define _CONNECTION_POOL_H
 
#include <mysql_connection.h>
#include <mysql_driver.h>
#include <cppconn/exception.h>
#include <cppconn/driver.h>
#include <cppconn/connection.h>
#include <cppconn/resultset.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/statement.h>
#include <pthread.h>
#include <list>
 
using namespace std;
using namespace sql;
 
class ConnPool {
private:
    int curSize; //当前已建立的数据库连接数量
    int maxSize; //连接池中定义的最大数据库连接数
    string username;
    string password;
    string url;
    list<Connection*> connList; //连接池的容器队列
    pthread_mutex_t lock; //线程锁
    static ConnPool *connPool;
    Driver*driver;
 
    Connection*CreateConnection(); //创建一个连接
    void InitConnection(int iInitialSize); //初始化数据库连接池
    void DestoryConnection(Connection *conn); //销毁数据库连接对象
    void DestoryConnPool(); //销毁数据库连接池
    ConnPool(string url, string user, string password, int maxSize); //构造方法
public:
    ~ConnPool();
    Connection*GetConnection(); //获得数据库连接
    void ReleaseConnection(Connection *conn); //将数据库连接放回到连接池的容器中
    static ConnPool *GetInstance(); //获取数据库连接池对象
};
#endif  /*_CONNECTION_POOL_H */

/*
 * connection_pool.cpp
 *
 *  Created on: 2013-3-29
 *      Author: csc
 */
 
#include <stdexcept>
#include <exception>
#include <stdio.h>
#include "connection_pool.h"
 
using namespace std;
using namespace sql;
 
ConnPool *ConnPool::connPool = NULL;
 
//连接池的构造函数
ConnPool::ConnPool(string url, string userName, string password, int maxSize) {
    this->maxSize = maxSize;
    this->curSize = 0;
    this->username = userName;
    this->password = password;
    this->url = url;
    try {
        this->driver = sql::mysql::get_driver_instance();
    } catch (sql::SQLException&e) {
        perror("驱动连接出错;\n");
    } catch (std::runtime_error&e) {
        perror("运行出错了\n");
    }
    this->InitConnection(maxSize / 2);
}
 
//获取连接池对象，单例模式
ConnPool*ConnPool::GetInstance() {
    if (connPool == NULL) {
        connPool = new ConnPool("tcp://127.0.0.1:3306", "root", "123456", 50);
    }
    return connPool;
}
 
//初始化连接池，创建最大连接数的一半连接数量
void ConnPool::InitConnection(int iInitialSize) {
    Connection*conn;
    pthread_mutex_lock(&lock);
    for (int i = 0; i < iInitialSize; i++) {
        conn = this->CreateConnection();
        if (conn) {
            connList.push_back(conn);
            ++(this->curSize);
        } else {
            perror("创建CONNECTION出错");
        }
    }
    pthread_mutex_unlock(&lock);
}
 
//创建连接,返回一个Connection
Connection* ConnPool::CreateConnection() {
    Connection*conn;
    try {
        conn = driver->connect(this->url, this->username, this->password); //建立连接
        return conn;
    } catch (sql::SQLException&e) {
        perror("创建连接出错");
        return NULL;
    } catch (std::runtime_error&e) {
        perror("运行时出错");
        return NULL;
    }
}
 
//在连接池中获得一个连接
Connection*ConnPool::GetConnection() {
    Connection*con;
    pthread_mutex_lock(&lock);
 
    if (connList.size() > 0) {   //连接池容器中还有连接
        con = connList.front(); //得到第一个连接
        connList.pop_front();   //移除第一个连接
        if (con->isClosed()) {   //如果连接已经被关闭，删除后重新建立一个
            delete con;
            con = this->CreateConnection();
        }
        //如果连接为空，则创建连接出错
        if (con == NULL) {
            --curSize;
        }
        pthread_mutex_unlock(&lock);
        return con;
    } else {
        if (curSize < maxSize) { //还可以创建新的连接
            con = this->CreateConnection();
            if (con) {
                ++curSize;
                pthread_mutex_unlock(&lock);
                return con;
            } else {
                pthread_mutex_unlock(&lock);
                return NULL;
            }
        } else { //建立的连接数已经达到maxSize
            pthread_mutex_unlock(&lock);
            return NULL;
        }
    }
}
 
//回收数据库连接
void ConnPool::ReleaseConnection(sql::Connection * conn) {
    if (conn) {
        pthread_mutex_lock(&lock);
        connList.push_back(conn);
        pthread_mutex_unlock(&lock);
    }
}
 
//连接池的析构函数
ConnPool::~ConnPool() {
    this->DestoryConnPool();
}
 
//销毁连接池,首先要先销毁连接池的中连接
void ConnPool::DestoryConnPool() {
    list<Connection*>::iterator icon;
    pthread_mutex_lock(&lock);
    for (icon = connList.begin(); icon != connList.end(); ++icon) {
        this->DestoryConnection(*icon); //销毁连接池中的连接
    }
    curSize = 0;
    connList.clear(); //清空连接池中的连接
    pthread_mutex_unlock(&lock);
}
 
//销毁一个连接
void ConnPool::DestoryConnection(Connection* conn) {
    if (conn) {
        try {
            conn->close();
        } catch (sql::SQLException&e) {
            perror(e.what());
        } catch (std::exception&e) {
            perror(e.what());
        }
        delete conn;
    }
}

/*
 * main.cpp
 *
 *  Created on: 2013-3-26
 *      Author: holy
 */
 
#include "connection_pool.h"
 
namespace ConnectMySQL {
 
//初始化连接池
ConnPool *connpool = ConnPool::GetInstance();
 
void run() {
 
    Connection *con;
    Statement *state;
    ResultSet *result;
 
    // 从连接池中获取mysql连接
    con = connpool->GetConnection();
 
    state = con->createStatement();
    state->execute("use holy");
 
    // 查询
    result = state->executeQuery("select * from student where id < 1002");
 
    // 输出查询
    while (result->next()) {
        int id = result->getInt("id");
        string name = result->getString("name");
        cout << id << " : " << name << endl;
    }
    delete state;
    connpool->ReleaseConnection(con);
}
}
 
int main(int argc, char* argv[]) {
    ConnectMySQL::run();
    return 0;
}