#ifndef _MY_SQL_POOL_HPP_
#define _MY_SQL_POOL_HPP_
#ifdef _WIN32
#include <winsock2.h>
#endif // _WIN32
#ifdef _WIN32
#pragma comment(lib, "libmysql.lib")
#endif // _WIN32
#include<iostream>
#include<mysql/mysql.h>
#include<queue>
#include<map>
#include<vector>
#include<string>
#include<mutex>
#include<thread>
typedef struct Node
{
int id;
MYSQL conn;
bool isUsed;
};
class MySqlPool
{
public:
static MySqlPool& GetInstance();
protected:
MySqlPool();
~MySqlPool();
private:
MySqlPool(const MySqlPool& rhs) {}
MySqlPool& operator = (const MySqlPool& rhs) {}
public:
typedef std::vector<std::vector<std::string>> _resVec;
public:
std::string Getmysql_get_client_info();
bool InitConnect(int count,std::string const&host,std::string const&userName,
std::string const&passWord,std::string const&dbName, unsigned int port);
bool BackConnect(MYSQL &conn);
bool TestCloseConn();
bool AddConnect();
MYSQL& GetOneConnect(bool &IsSuccess);
Node& GetConnect(bool &IsSuccess);
bool BackConnect(Node& node);
//数据库增加、删除、修改、查询
bool Query(std::string const& sql, _resVec & result,int &id);
//数据库存储过程
bool ExecProc(std::string const& sql, std::string const& outsql, _resVec &result, std::vector<std::string> &out);
private:
std::vector<Node> m_NodeVec;
std::queue<int> m_IdQueue;
int m_MaxConnectCount;
int m_CurConnectCount;
unsigned int m_Port;
std::string m_host;
std::string m_userName;
std::string m_passWord;
std::string m_dbName;
std::mutex m_Lock;
std::mutex m_PrintLock;
int m_count;
};
MySqlPool::MySqlPool()
{
}
MySqlPool::~MySqlPool()
{
for (size_t i = 0; i < m_NodeVec.size(); i++)
{
Node &n = m_NodeVec[i];
mysql_close(&n.conn);
}
std::cout << "析构了" << std::endl;
}
inline std::string MySqlPool::Getmysql_get_client_info()
{
// TODO: insert return statement here
//std::cout << "MySQL client version:"<<mysql_get_client_info() << std::endl;
return mysql_get_client_info();
}
Node& MySqlPool::GetConnect(bool &IsSuccess)
{
std::lock_guard<std::mutex> lck(m_Lock);
IsSuccess = false;
if (m_NodeVec.size()>0)
{
bool used = true;
int j = 0;
int size = m_NodeVec.size();
for (size_t i = 0; i < m_NodeVec.size(); i++)
{
Node &node = m_NodeVec[i];
if (!node.isUsed)
{
used = false;
j = i;
break;
}
}
if (!used)
{
Node &node = m_NodeVec[j];
int ret = mysql_ping(&node.conn);
if (ret==0)
{
IsSuccess = true;
node.isUsed = true;
return node;
}
else
{
mysql_close(&node.conn);
m_IdQueue.push(node.id);
m_NodeVec.erase(std::begin(m_NodeVec) + j);
m_CurConnectCount--;
}
}
}
else
{
AddConnect();
if (m_NodeVec.size()>0)
{
bool used = true;
int j = 0;
int size = m_NodeVec.size();
for (size_t i = 0; i < m_NodeVec.size(); i++)
{
Node &node = m_NodeVec[i];
if (!node.isUsed)
{
used = false;
j = i;
break;
}
}
if (!used)
{
Node &node = m_NodeVec[j];
int ret = mysql_ping(&node.conn);
if (ret == 0)
{
IsSuccess = true;
node.isUsed = true;
return node;
}
else
{
mysql_close(&node.conn);
m_IdQueue.push(node.id);
m_NodeVec.erase(std::begin(m_NodeVec) + j);
m_CurConnectCount--;
}
}
}
}
}
bool MySqlPool::BackConnect(Node& node)
{
std::lock_guard<std::mutex> lck(m_Lock);
for (size_t i = 0; i < m_NodeVec.size(); i++)
{
Node &n = m_NodeVec[i];
if (n.id == node.id)
{
n.isUsed = false;
break;
}
}
return true;
}
bool MySqlPool::ExecProc(std::string const& sql,std::string const& outsql, _resVec &result, std::vector<std::string> &out)
{
result.clear();
out.clear();
bool IsSuccess = false;
Node& node = GetConnect(IsSuccess);
if (IsSuccess)
{
int ret = mysql_real_query(&node.conn, sql.data(), sql.length());
if (ret == 0)
{
MYSQL_RES *res = mysql_store_result(&node.conn);
MYSQL_ROW row;
if (res)
{
unsigned int num_fields = mysql_num_fields(res);//有多少列
std::vector<std::string> vec;
while ((row = mysql_fetch_row(res))) {
vec.clear();
unsigned long *lengths = mysql_fetch_lengths(res);
for (size_t i = 0; i < num_fields; i++)
{
std::string str = ((int)lengths[i], row[i] ? row[i] : "");
vec.push_back(str);
}
result.push_back(vec);
}
mysql_free_result(res);
ret = mysql_next_result(&node.conn);
}
/*
0成功并有多个结果。-1成功但没有多个结果。>0出错
*/
if (ret == 0)
{
do
{
ret = mysql_next_result(&node.conn);//遍历完所有结果集
} while (ret == 0);
}
{
if (outsql.length()>0)
{
//获取存储过程输出参数
ret = mysql_real_query(&node.conn, outsql.data(), outsql.length());
if (ret == 0)
{
MYSQL_RES *res = mysql_store_result(&node.conn);
if (res)
{
unsigned int num_fields = mysql_num_fields(res);//有多少列
while ((row = mysql_fetch_row(res))) {
//printf("[%s]\n", row[0]);
unsigned long *lengths = mysql_fetch_lengths(res);
for (size_t i = 0; i < num_fields; i++)
{
std::string str = ((int)lengths[i], row[i] ? row[i] : "");
out.push_back(str);
}
}
mysql_free_result(res);
}
}
else
{
std::lock_guard<std::mutex> lck(m_PrintLock);
std::cout << "errorMsg:" << mysql_error(&node.conn) << ",errorCode:" << mysql_errno(&node.conn) << std::endl;
}
}
}
}
else
{
std::lock_guard<std::mutex> lck(m_PrintLock);
std::cout << "errorMsg:" << mysql_error(&node.conn) << ",errorCode:" << mysql_errno(&node.conn) << std::endl;
BackConnect(node);
return false;
}
BackConnect(node);
}
return true;
}
bool MySqlPool::Query(std::string const& sql, _resVec & result, int &id)
{
result.clear();
bool IsSuccess = false;
Node& node = GetConnect(IsSuccess);
if (IsSuccess)
{
int ret = mysql_real_query(&node.conn,sql.data(),sql.length());
if (ret == 0)
{
MYSQL_RES *res = mysql_store_result(&node.conn);//SELECT 会进这里
if (res)
{
unsigned int num_fields = mysql_num_fields(res);//有多少列
//fields = mysql_fetch_fields(res);
//遍历列名
MYSQL_FIELD *field;
while ((field = mysql_fetch_field(res)))
{
std::cout << field->name << std::endl;
}
MYSQL_ROW row;
std::vector<std::string> vec;
while ((row = mysql_fetch_row(res)))
{
vec.clear();
unsigned long *lengths = mysql_fetch_lengths(res);
for (size_t i = 0; i < num_fields; i++)
{
std::string str = ((int)lengths[i],row[i]?row[i]:"");
vec.push_back(str);
}
result.push_back(vec);
}
mysql_free_result(res);
}
else//update ,delete,insert
{
if (mysql_field_count(&node.conn) == 0)
{
// query does not return data
// (it was not a SELECT)
unsigned int num_rows = mysql_affected_rows(&node.conn);
if (num_rows>0)
{
id = num_rows;
}
std::lock_guard<std::mutex> lck(m_PrintLock);
std::cout << "影响的行:" << num_rows << std::endl;
}
else // mysql_store_result() should have returned data
{
std::lock_guard<std::mutex> lck(m_PrintLock);
fprintf(stderr, "Error: %s\n", mysql_error(&node.conn));
}
}
}
else
{
std::lock_guard<std::mutex> lck(m_PrintLock);
std::cout <<"errorMsg:" <<mysql_error(&node.conn) << ",errorCode:" << mysql_errno(&node.conn) << std::endl;
BackConnect(node);
return false;
}
BackConnect(node);
}
return true;
}
bool MySqlPool::AddConnect()
{
int addSize = m_MaxConnectCount - m_CurConnectCount;
for (size_t i = 0; i < addSize; i++)
{
MYSQL conn;
if (mysql_init(&conn))
{
int ret = 0;
my_bool reconnect = 1;//1:启动自动重连 0:禁用
ret = mysql_options(&conn, MYSQL_OPT_RECONNECT, &reconnect);
if (ret != 0) {
std::cout << "mysql_options fail:" << mysql_error(&conn) << ",错误码:" << mysql_errno(&conn) << std::endl;
continue;
}
if (mysql_real_connect(&conn, m_host.data(), m_userName.data(), m_passWord.data(), m_dbName.data(), m_Port, NULL, 0))
{
mysql_query(&conn, "set names gbk");//解决写入中文或返回中文问题
Node node;
if (m_IdQueue.size()>0)
{
node.id = m_IdQueue.front();
m_IdQueue.pop();
}
else
{
node.id = i;
}
node.conn = conn;
node.isUsed = false;
m_NodeVec.push_back(node);
m_CurConnectCount++;
}
else
{
std::cout << "mysql_real_connect fail:" << mysql_error(&conn) << ",错误码:" << mysql_errno(&conn) << std::endl;
continue;
}
}
else
{
std::cout << "mysql_init error" << std::endl;
continue;
}
}
return true;
}
bool MySqlPool::InitConnect(int count, std::string const&host, std::string const&userName,
std::string const&passWord, std::string const&dbName, unsigned int port)
{
m_MaxConnectCount = count;
m_Port = port;
m_host = host;
m_userName = userName;
m_passWord = passWord;
m_dbName = dbName;
for (size_t i = 0; i < count; i++)
{
MYSQL conn;
if (mysql_init(&conn))
{
int ret = 0;
my_bool reconnect = 1;//1:启动自动重连 0:禁用
ret = mysql_options(&conn, MYSQL_OPT_RECONNECT, &reconnect);
if (ret != 0) {
std::cout << "mysql_options fail:" << mysql_error(&conn) << ",错误码:" << mysql_errno(&conn) << std::endl;
return false;
}
if (mysql_real_connect(&conn, host.data(), userName.data(), passWord.data(), dbName.data(), port, NULL, 0))
{
mysql_query(&conn, "set names gbk");//解决写入中文或返回中文问题
Node node;
node.id = i;
node.conn = conn;
node.isUsed = false;
m_NodeVec.push_back(node);
m_CurConnectCount++;
}
else
{
std::cout << "mysql_real_connect fail:" << mysql_error(&conn) << ",错误码:" << mysql_errno(&conn) << std::endl;
return false;
}
}
else
{
std::cout << "mysql_init error" << std::endl;
return false;
}
}
std::cout << "初始化成功连接数:" << m_CurConnectCount << std::endl;
return true;
}
MySqlPool& MySqlPool::GetInstance()
{
static MySqlPool _instance;
return _instance;
}
#endif // !_MY_SQL_POOL_HPP_