CREATE DATABASE chat;
use chat;
CREATE TABLE user (
       id INT(11) NOT NULL AUTO_INCREMENT,
      name VARCHAR(50) NOT NULL,
      age INT(11) NOT NULL,
        sex ENUM('male', 'female') NOT NULL,
        PRIMARY KEY (id)
      );

desc user;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int                   | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)           | NO   |     | NULL    |                |
| age   | int                   | NO   |     | NULL    |                |
| sex   | enum('male','female') | NO   |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+

mysql> select * from user;
Empty set (0.19 sec)

#pragma once
#include <mysql.h>
#include <string>
#include <ctime>
using namespace std;
/*
封裝MySQL數(shù)據(jù)庫(kù)的接口操作
*/
class Connection
{
public:
	// 初始化數(shù)據(jù)庫(kù)連接
	Connection();
	// 釋放數(shù)據(jù)庫(kù)連接資源
	~Connection();
	// 連接數(shù)據(jù)庫(kù)
	bool connect(string ip,
		unsigned short port,
		string user,
		string password,
		string dbname);
	// 更新操作 insert、delete、update
	bool update(string sql);
	// 查詢操作 select
	MYSQL_RES* query(string sql);

	// 刷新一下連接的起始的空閑時(shí)間點(diǎn)
	void refreshAliveTime() { _alivetime = clock(); }
	// 返回存活的時(shí)間
	clock_t getAliveeTime()const { return clock() - _alivetime; }
private:
	MYSQL* _conn; // 表示和MySQL Server的一條連接
	clock_t _alivetime; // 記錄進(jìn)入空閑狀態(tài)后的起始存活時(shí)間
};

include "public.h"
#include "Connect.h"
#include <iostream>
using namespace std;

Connection::Connection()
{
	// 初始化數(shù)據(jù)庫(kù)連接
	_conn = mysql_init(nullptr);
}

Connection::~Connection()
{
	// 釋放數(shù)據(jù)庫(kù)連接資源
	if (_conn != nullptr)
		mysql_close(_conn);
}

bool Connection::connect(string ip, unsigned short port,
	string username, string password, string dbname)
{
	// 連接數(shù)據(jù)庫(kù)
	MYSQL* p = mysql_real_connect(_conn, ip.c_str(), username.c_str(),
		password.c_str(), dbname.c_str(), port, nullptr, 0);
	return p != nullptr;
}

bool Connection::update(string sql)
{
	// 更新操作 insert、delete、update
	if (mysql_query(_conn, sql.c_str()))
	{
		LOG(+ "更新失敗:" + sql);
		return false;
	}
	return true;
}

MYSQL_RES* Connection::query(string sql)
{
	// 查詢操作 select
	if (mysql_query(_conn, sql.c_str()))
	{
		LOG("查詢失敗:" + sql);
		return nullptr;
	}
	return mysql_use_result(_conn);
}

#pragma once
#include <iostream>
#define LOG(str) \
	std::cout << __FILE__ << ":"<<__LINE__<<" " \
	__TIMESTAMP__ << ":"<<str <<std::endl;

#include <iostream>
#include "Connect.h"
int main()
{
	Connection conn;
	char sql[1024] = { 0 };
//插入一條數(shù)據(jù)
	sprintf(sql, "insert into user(name,age,sex) values('%s','%d','%s');", "zhang san", 20, "male");
	conn.connect("127.0.0.1", 3306, "root", "123456", "chat");
	conn.update(sql); //更新sql語(yǔ)句
	return 0;
}

mysql> select * from user;
+----+-----------+-----+------+
| id | name      | age | sex  |
+----+-----------+-----+------+
|  1 | zhang san |  20 | male |
+----+-----------+-----+------+
1 row in set (0.02 sec)

#數(shù)據(jù)庫(kù)連接池的配置文件
ip=127.0.0.1
port=3306
username=root
password=123456
initSize=10
maxSize=1024
#最大空閑時(shí)間默認(rèn)單位為秒
maxIdleTime=60
#連接超時(shí)時(shí)間單位是毫米
connectionTimeOut=100

#pragma once
#include "public.h"
#include "Connect.h"
#include <queue>
#include <mutex>
#include <string>
#include <atomic>
#include <memory>
#include <functional>
#include <condition_variable>
//因?yàn)閿?shù)據(jù)庫(kù)連接池子只有一個(gè)，所以我們采用單例模式
class mySqlPool {
public:
	//獲取連接池對(duì)象實(shí)例
	static mySqlPool* getMySqlPool();
	std::shared_ptr<Connection> getConnection();//從連接池獲取一個(gè)可用的空閑連接
private:
	mySqlPool();//構(gòu)造函數(shù)私有化
	bool loadConfigFile();//從配置文件中加載配置項(xiàng)
	void produceConnectionTask(); //運(yùn)行在獨(dú)立的線程中，專門(mén)負(fù)責(zé)生產(chǎn)新連接
	//掃描超過(guò)maxIdleTime時(shí)間的空閑連接，進(jìn)行隊(duì)列的連接回收
	void scannerConnectionTask();

	std::string _ip;//mysql的ip地址
	std::string _dbname;//數(shù)據(jù)庫(kù)的名稱
	unsigned short _port; //mysql端口號(hào)3306
	std::string _username;//mysql用戶名
	std::string _password;//mysql登陸密碼
	int _initSize;//連接池的初始連接量
	int _maxSize;//連接池的最大連接量
	int _maxIdleTime;//連接池最大空閑時(shí)間
	int _connectionTimeOut;//連接池獲取連接的超時(shí)時(shí)間

	std::queue<Connection*> _connectionQue;//存儲(chǔ)mysql連接隊(duì)列
	std::mutex _queueMutex; //維護(hù)連接隊(duì)列的線程安全互斥鎖
	std::atomic_int _connectionCnt; //記錄連接所創(chuàng)建的connect的數(shù)量
	std::condition_variable cv;//設(shè)置條件變量，用于生產(chǎn)者線程和消費(fèi)者線程的通信
};

//在mySqlPool.cpp中
//加載配置文件
bool mySqlPool::loadConfigFile()
{
	FILE* pf = fopen("mysql.ini", "r");
	if (pf == nullptr)
	{
		LOG("mysql.ini file is not exits!");
		return false;
	}
	while (!feof(pf)) //遍歷配置文件
	{
		char line[1024] = { 0 };
		fgets(line, 1024, pf);
		std::string str = line;
		int idx = str.find('=', 0); //從0開(kāi)始找'='符號(hào)的位置
		if (idx == -1)continue;
		int endidx = str.find('\n', idx);//從idx尋找'\n'的位置，也就是末尾
		std::string key = str.substr(0, idx); //獲取配置文件中=號(hào)左邊的key
		//從等號(hào)后到末尾，剛好是value的string形式
		std::string value = str.substr(idx + 1, endidx - idx - 1);
		if (key == "ip")
		{
			_ip = value;
		}
		else if (key == "port")
		{
			//字符串轉(zhuǎn)換成unsigned short
			_port = static_cast<unsigned short>(std::stoul(value));
		}
		else if (key == "username")
		{
			_username = value;
		}
		else if (key == "password")
		{
			_password = value;
		}
		else if (key == "dbname")
		{
			_dbname = value;
		}
		else if (key == "initSize")
		{
			_initSize = std::stoi(value); 
		}
		else if (key == "maxSize")
		{
			_maxSize = std::stoi(value); 
		}
		else if (key == "maxIdleTime")
		{
			_maxIdleTime = std::stoi(value); 
		}
		else if (key == "connectionTimeOut")
		{
			_connectionTimeOut = std::stoi(value); 
		}
	}
	return true;
}

//mySqlPool.h
//構(gòu)造方法
mySqlPool::mySqlPool()
{
	if (!loadConfigFile())
	{
		LOG("load Config File is error!");
		return;
	}
	//創(chuàng)建初始數(shù)量的連接
	for (int i = 0; i < _initSize; ++i)
	{
		Connection* p = new Connection();
		p->connect(_ip, _port, _username, _password, _dbname);
	}
	//啟動(dòng)一個(gè)新線程，作為連接的生產(chǎn)者
	std::thread produce(std::bind(&mySqlPool::produceConnectionTask, this));
	produce.detach();
	//啟動(dòng)一個(gè)新線程，作為空閑連接超時(shí)的回收者
	std::thread scanner(std::bind(&mySqlPool::scannerConnectionTask, this));
	scanner.detach();
}

//mySqlPool.h
//單例模式
mySqlPool* mySqlPool::getMySqlPool()
{
	static mySqlPool pool;
	return &pool;
}

//運(yùn)行在獨(dú)立的線程中，專門(mén)負(fù)責(zé)生產(chǎn)新連接
void mySqlPool::produceConnectionTask()
{
	while (true)
	{
		std::unique_lock<std::mutex> lock(_queueMutex);
		while (!_connectionQue.empty())
			cv.wait(lock);     //隊(duì)列不為空不生產(chǎn)線程

		//沒(méi)有到上線就可以生產(chǎn)線程
		if (_connectionCnt < _maxSize)
		{
			auto p = new Connection();
			p->connect(_ip, _port, _username, _password, _dbname);
			p->refreshAliveTime();//創(chuàng)建的時(shí)候刷新存活時(shí)間
			_connectionQue.push(p);
			++_connectionCnt;
		}
		cv.notify_all();
	}
}

/從連接池獲取一個(gè)可用的空閑連接
std::shared_ptr<Connection> mySqlPool::getConnection()
{
	std::unique_lock<std::mutex> lock(_queueMutex);
	while (_connectionQue.empty())
	{
		//如果超時(shí)沒(méi)有獲取可用的空閑連接返回空
		if (std::cv_status::timeout == cv.wait_for(lock, std::chrono::milliseconds(100)))

			if (_connectionQue.empty())
			{
				LOG("get Connection error");
				return nullptr;
			}
	}
	std::shared_ptr<Connection> sp(_connectionQue.front(), [&](Connection* pcon) {
		//保證只能同一時(shí)刻只能有一個(gè)線程歸還連接給隊(duì)列
		std::unique_lock<std::mutex> lock(_queueMutex);
		pcon->refreshAliveTime();//創(chuàng)建的時(shí)候刷新存活時(shí)間
		_connectionQue.push(pcon);
		});
	_connectionQue.pop();
	cv.notify_all();
	return sp;
}

std::shared_ptr<Connection> sp(_connectionQue.front(), [&](Connection* pcon) {
		//保證只能同一時(shí)刻只能有一個(gè)線程歸還連接給隊(duì)列
		std::unique_lock<std::mutex> lock(_queueMutex);
		pcon->refreshAliveTime();//創(chuàng)建的時(shí)候刷新存活時(shí)間
		_connectionQue.push(pcon);
		});

//連接線程回收
void mySqlPool::scannerConnectionTask()
{
	while (true)
	{
		//通過(guò)sleep模擬定時(shí)效果，每_maxIdleTime檢查一次
		std::this_thread::sleep_for(std::chrono::seconds(_maxIdleTime));
		
		//掃描整個(gè)隊(duì)列釋放多余的超時(shí)連接
		std::unique_lock<std::mutex> lock(_queueMutex);
		while (_connectionCnt > _initSize)
		{
			auto p = _connectionQue.front();
			if (p->getAliveeTime() >= (_maxIdleTime * 1000))
			{
				_connectionQue.pop();
				delete p;//這里會(huì)調(diào)用智能指針，回收到隊(duì)列中
			}
		}
	}
}

//test.h
//非線程池的連接
void testSql( int n)
{
	clock_t begin = clock();
	std::thread t([&n]() {
		for (int i = 1; i < n; ++i)
		{
			Connection cnn;
			char sql[1024] = { 0 };
			sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhang san", 20, "male");
			cnn.connect("127.0.0.1", 3306, "root", "123456", "chat");
			cnn.update(sql);
		}});
	t.join();
	clock_t end = clock();
	std::cout << "普通連接數(shù)量為:" << n << "的sql執(zhí)行時(shí)間:" << (end - begin) << "ms" << std::endl;
}

#include <iostream>
#include "Connect.h"
#include "mySqlPool.h"
#include "test.h"

int main()
{
	testSql(10);//普通連接數(shù)量為 : 10的sql執(zhí)行時(shí)間 : 2838ms
	testSql(100);//普通連接數(shù)量為 : 100的sql執(zhí)行時(shí)間: 12299
	testSql(1000);//普通連接數(shù)量為 : 1000的sql執(zhí)行時(shí)間 : 104528ms
	return 0;
}

 //test.h
void f(int n)
{
	mySqlPool* cp = mySqlPool::getMySqlPool();
	for (int i = 1; i <= n; ++i)
	{
		std::shared_ptr<Connection> sp = cp->getConnection();
		char sql[1024] = { 0 };
		sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhang san", 20, "male");
		sp->update(sql);
	}
}
//測(cè)試連接池連接
void testSqlPool(int n)
{
	clock_t begin = clock();
	std::thread t1(f, n);
	t1.join();
	clock_t end = clock();
	std::cout << "單線程采用數(shù)據(jù)庫(kù)連接池，連接數(shù)量為:" << n << "的sql執(zhí)行時(shí)間:" << (end - begin) << "ms" << std::endl;
}

#include <iostream>
#include "Connect.h"
#include "mySqlPool.h"
#include "test.h"

int main()
{
  testSqlPool(10);//單線程 采用數(shù)據(jù)庫(kù)連接池，連接數(shù)量為:10的sql執(zhí)行時(shí)間:1745ms
  testSqlPool(100);//單線程 采用數(shù)據(jù)庫(kù)連接池，連接數(shù)量為:100的sql執(zhí)行時(shí)間:9779ms 
  testSqlPool(1000);//單線程 采用數(shù)據(jù)庫(kù)連接池，連接數(shù)量為:1000的sql執(zhí)行時(shí)間 : 86016ms
	return 0;
}

//test.h
//測(cè)試連接池連接 4線程
void testSqlPool4(int n)
{
	int n2 = n / 4;
	clock_t begin = clock();
	std::thread t1(f, n2);
	std::thread t2(f, n2);
	std::thread t3(f, n2);
	std::thread t4(f, n2);
	t1.join();
	t2.join();
	t3.join();
	t4.join();
	clock_t end = clock();
	std::cout << "四線程采用數(shù)據(jù)庫(kù)連接池，連接數(shù)量為:" << n << "的sql執(zhí)行時(shí)間:" << (end - begin) << "ms" << std::endl;
}

#include <iostream>
#include "Connect.h"
#include "mySqlPool.h"
#include "test.h"

int main()
{
	testSqlPool4(100);//4條線程 采用數(shù)據(jù)庫(kù)連接池，連接數(shù)量為:100的sql執(zhí)行時(shí)間 : 3715ms
    testSqlPool4(1000);//4條線程 采用數(shù)據(jù)庫(kù)連接池，連接數(shù)量為:1000的sql執(zhí)行時(shí)間 : 34686ms
	return 0;
}