码海拾遗：基于MySQL Connector/C++的MySQL操作(连接池)

1、MySQL安装及简单设置

　　(1)安装：在OSX系统下，可以使用万能的“brew install”命令来进行安装：brew isntall mysql(默认安装最新版的MySQL)

　　(2)启动：brew services start mysql

　　(3)修改密码：update user set authentication_string = password('password'), password_expired = 'N', password_last_changed = now() where user = 'root';

　　　　-->flush privileges;(让修改后的密码生效)

　　(4)允许远程访问：update mysql.user set host = '%' where user = 'root';

2、MySQL Connector/C++安装

　　(1)下载：MySQL Connector/C++源码可以从这里下载

　　(2)安装：解压后将“include”目录下的文件复制到“/usr/local/include”目录下，“lib”目录下的文件复制到“/usr/local/lib”目录下即可

3、示例代码（基于单例模式的懒汉模型）

CConnPool.h

/*
 * CConnPool.h
 *
 *  Created on: Mar 15, 2018
 *      Author: root
 */
 
#ifndef SRC_CCONNPOOL_H_
#define SRC_CCONNPOOL_H_
 
#include <list>
#include <string>
 
#include <pthread.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>
 
using namespace sql;
using namespace std;
 
class CConnPool {
public:
    ~CConnPool();
    void InitConnpool(string url, string user, string password, int maxSize);
    Connection* GetConnection();
    void ReleaseConnection(Connection* conn);
    static CConnPool *GetInstance();
 
private:
    CConnPool();
    Connection*CreateConnection(); //创建一个连接
    void InitConnection(int iInitialSize); //初始化数据库连接池
    void DestoryConnection(Connection *conn); //销毁数据库连接对象
    void DestoryConnPool(); //销毁数据库连接池
    CConnPool(string url, string user, string password, int maxSize); //构造方法
 
private:
    int curSize; //当前已建立的数据库连接数量
    int maxSize; //连接池中定义的最大数据库连接数
    string user;
    string password;
    string url;
    list<Connection*> connList; //连接池的容器队列  STL list 双向链表
    pthread_mutex_t lock; //线程锁
    static CConnPool *connPool;
    Driver*driver;
};
 
#endif /* SRC_CCONNPOOL_H_ */

CConnPool.cpp

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

/*
 * CConnPool.cpp
 *
 *  Created on: Mar 15, 2018
 *      Author: root
 */
 
#include <stdexcept>
#include <exception>
#include <cstdio>
 
#include "CConnPool.h"
 
CConnPool *CConnPool::connPool = NULL;
//CConnPool* CConnPool::connPool = new CConnPool();
 
CConnPool::CConnPool()
{
    // TODO Auto-generated constructor stub
}
 
void CConnPool::InitConnpool(string url, string user, string password,
                             int maxSize)
{
    this->maxSize = maxSize;
    this->curSize = 0;
    this->user = user;
    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);
    pthread_mutex_init(&lock, NULL);
}
 
CConnPool::CConnPool(string url, string user, string password, int maxSize)
{
    this->maxSize = maxSize;
    this->curSize = 0;
    this->user = user;
    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);
    pthread_mutex_init(&lock, NULL);
}
 
CConnPool *CConnPool::GetInstance()
{
    if (connPool == NULL)
        connPool = new CConnPool("tcp://127.0.0.1:3306", "root", "123456",
                                 10);
    return connPool;
}
 
void CConnPool::InitConnection(int num)
{
    Connection *conn;
    pthread_mutex_lock(&lock);
    for (int i = 0; i < num; ++i)
    {
        conn = CreateConnection();
        if (conn)
        {
            connList.push_back(conn);
            ++curSize;
        }
        else
        {
            perror("创建CONNECTION出错");
        }
    }
    pthread_mutex_unlock(&lock);
}
 
Connection *CConnPool::CreateConnection()
{
    Connection *conn;
    try
    {
        conn = driver->connect(url, user, password); //建立连接
        return conn;
    }
    catch (sql::SQLException &e)
    {
        perror(e.what());
        return NULL;
    }
    catch (std::runtime_error &e)
    {
        perror(e.what());
        return NULL;
    }
}
 
Connection *CConnPool::GetConnection()
{
    Connection *conn;
    pthread_mutex_lock(&lock);
 
    if (connList.size() > 0)
    {
        conn = connList.front();
        connList.pop_front();
        if (conn->isClosed())
        {
            delete conn;
            conn = CreateConnection();
        }
        if (conn == NULL)
            --curSize;
        pthread_mutex_unlock(&lock);
        return conn;
    }
    else
    {
        if (curSize < maxSize)
        {
            conn = CreateConnection();
            if (conn)
            {
                ++curSize;
                pthread_mutex_unlock(&lock);
                return conn;
            }
            else
            {
                pthread_mutex_unlock(&lock);
                return NULL;
            }
        }
        else
        {
            pthread_mutex_unlock(&lock);
            return NULL;
        }
    }
}
 
void CConnPool::ReleaseConnection(Connection *conn)
{
    if (conn)
    {
        pthread_mutex_lock(&lock);
        connList.push_back(conn);
        pthread_mutex_unlock(&lock);
    }
}
 
CConnPool::~CConnPool()
{
    this->DestoryConnPool();
}
 
void CConnPool::DestoryConnPool()
{
    list<Connection *>::iterator iter;
    pthread_mutex_lock(&lock);
    for (iter = connList.begin(); iter != connList.end(); ++iter)
        this->DestoryConnection(*iter);
    curSize = 0;
    connList.clear();
    pthread_mutex_unlock(&lock);
}
 
void CConnPool::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

#include <iostream>
#include <string>
 
#include "CConnPool.h"
 
using std::cout;
using std::endl;
using std::string;
 
CConnPool *connpool = CConnPool::GetInstance();
 
int main(int argc, char *argv[])
{
    Connection *conn;
    Statement *state;
    ResultSet *result;
 
    conn = connpool->GetConnection();
    state = conn->createStatement();
    state->execute("use mysql");
 
    result = state->executeQuery("select host,user from user");
    while (result->next())
    {
        try
        {
            string user = result->getString("user");
            string host = result->getString("host");
            cout << user << "@" << host << endl;
        }
        catch (sql::SQLException &e)
        {
            cout << e.what() << endl;
        }
    }
 
    delete result;
    delete state;
    connpool->ReleaseConnection(conn);
 
    getchar();
    return 0;
}