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
/*
* 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;
}
