C++ 实现mysql数据库连接池

1、连接池类实现：

#pragma once
#include "mysqlConn.h"
#include 
#include 
using namespace std;

class connPool
{
public:
	static connPool* getConnPool();
	connPool(const connPool& obj) = delete;
	connPool& operator=(const connPool& obj) = delete;
	~connPool();

	bool create(string user, string passWord, string dbName, string ip, unsigned short port = 3306, unsigned long multiSqlFlag = 0, int poolSize = 10, int timeOut = 60);
	mysqlConn* getOneConn();
	void releaseOneConn(mysqlConn* pConn);
private:
	connPool();
	void destroy();
	void addIdelQueue();

	string m_user;
	string m_passWord;
	string m_dbName;
	string m_ip;
	unsigned short m_port = 3306;
	unsigned short m_multiSqlFlag = 0;
	int m_nPoolSize = 0;
	int m_nTimeOut = 0;

	mutex m_mtx;

	deque m_qIdle;
	deque m_qBusy;
};

#include "connPool.h"
#include 

connPool * connPool::getConnPool()
{
	static connPool pool;
	return &pool;
}

connPool::~connPool()
{
	destroy();
}

bool connPool::create(string user, string passWord, string dbName, string ip, unsigned short port , unsigned long multiSqlFlag , int poolSize , int timeOut )
{
	m_user = user;
	m_passWord = passWord;
	m_dbName = dbName;
	m_ip = ip;
	m_port = port;
	m_multiSqlFlag = multiSqlFlag;
	m_nPoolSize = poolSize;
	m_nTimeOut = timeOut;

	{
		lock_guard locker(m_mtx);

		for (int i = 0; i < m_nPoolSize; i++)
		{
			addIdelQueue();
		}
	}

	return m_nPoolSize == m_qIdle.size();
}

mysqlConn * connPool::getOneConn()
{
	mysqlConn* pConn = nullptr;

	{
		lock_guard locker(m_mtx);

		if (!m_qIdle.empty())
		{
			pConn = m_qIdle.front();
			m_qIdle.pop_front();

			if (pConn && pConn->isConnect())
			{
				m_qBusy.push_back(pConn);
			}
			else
			{
				if (pConn)
				{
					delete pConn, pConn = nullptr;
				}

				pConn = new mysqlConn;
				if (pConn && pConn->connect(m_user, m_passWord, m_dbName, m_ip, m_port, m_multiSqlFlag))
				{
					m_qBusy.push_back(pConn);
				}
				else 
				{
					if (pConn)
					{
						delete pConn; 
					}
					pConn = nullptr;
				}
			}
		}
		else
		{
			pConn = new mysqlConn;
			if (pConn && pConn->connect(m_user, m_passWord, m_dbName, m_ip, m_port, m_multiSqlFlag))
			{
				m_qBusy.push_back(pConn);
			}
			else
			{
				if (pConn)
				{
					delete pConn;
				}
				pConn = nullptr;
			}
		}
	}

	return pConn;
}

void connPool::releaseOneConn(mysqlConn* pConn)
{
	if (nullptr == pConn) return;

	lock_guard locker(m_mtx);

	deque::iterator it = find(m_qBusy.begin(), m_qBusy.end(), pConn);
	if (it != m_qBusy.end())
	{
		m_qBusy.erase(it);
		m_qIdle.push_back(pConn);
	}

	int nDelNum = m_qIdle.size() - m_nPoolSize;
	for (int i = 0;i < nDelNum;i++)
	{
		mysqlConn* pConn = m_qIdle.front();
		m_qIdle.pop_front();
		if (pConn) delete pConn, pConn = nullptr;
	}

	m_qBusy.shrink_to_fit();
	m_qIdle.shrink_to_fit();
}

connPool::connPool()
{
}

void connPool::destroy()
{
	lock_guard locker(m_mtx);

	while (!m_qIdle.empty())
	{
		mysqlConn* pConn = m_qIdle.front();
		m_qIdle.pop_front();
		if (pConn) delete pConn;
		printf("销毁资源n");
	}

	while (!m_qBusy.empty())
	{
		mysqlConn* pConn = m_qBusy.front();
		m_qBusy.pop_front();
		if (pConn) delete pConn;
		printf("销毁资源n");
	}
}

void connPool::addIdelQueue()
{
	mysqlConn* pConn = new mysqlConn;
	if (pConn && pConn->connect(m_user, m_passWord, m_dbName, m_ip, m_port, m_multiSqlFlag))
	{
		m_qIdle.push_back(pConn);
	}
	else if (pConn)
	{
		delete pConn, pConn = nullptr;
	}

	printf("数据库池中的连接总数 = %dn", m_qIdle.size());
}

其中连接池中涉及到的mysql连接类封装请查看上一篇博客：

https://zhangzc.blog.csdn.net/article/details/124371361​​​​​​​

2、连接池的使用方法及与非连接池的用时比较：

#include "mysqlConn.h"
#include "connPool.h"
#include "Windows.h"
#include 
using namespace std;
using namespace chrono;

#pragma comment(lib, "ws2_32.lib")

void op1(int begin, int end)
{
	for (int i = begin; i < end; ++i)
	{
		mysqlConn sqlCon;
		sqlCon.connect("root", "root", "zzc", "127.0.0.1", 3306, 0);
		char sql[1024] = { 0 };
		sprintf(sql, "insert student(id, name, time) values(%d, 'zhangsan', '2022-04-25 11:05:33')", i);
		sqlCon.execute(sql);
	}
}

void op2(connPool* pool, int begin, int end)
{
	for (int i = begin; i < end; ++i)
	{
		mysqlConn* conn = pool->getOneConn();
		char sql[1024] = { 0 };
		sprintf(sql, "insert student(id, name, time) values(%d, 'zhangsan', '2022-04-25 11:05:33')", i);
		conn->execute(sql);
		pool->releaseOneConn(conn);
	}
}

int main()
{
	WSADATA wsaData;
	WSAStartup(MAKEWORd(2, 2), &wsaData);

#if 0
	//某一次：非连接池用时: 24186980000 纳秒, 24186 毫秒
	steady_clock::time_point begin = steady_clock::now();
	thread t1(op1, 0, 1000);
	thread t2(op1, 1000, 2000);
	thread t3(op1, 2000, 3000);
	thread t4(op1, 3000, 4000);
	thread t5(op1, 4000, 5000);
	t1.join();
	t2.join();
	t3.join();
	t4.join();
	t5.join();
	steady_clock::time_point end = steady_clock::now();
	auto length = end - begin;
	cout << "非连接池用时: " << length.count() << " 纳秒, "
		<< length.count() / 1000000 << " 毫秒" << endl;

#else
	//某一次：连接池用时: 1958285900 纳秒, 1958 毫秒
	connPool* pool = connPool::getConnPool();
	pool->create("root", "root", "zzc", "localhost");
	
	steady_clock::time_point begin = steady_clock::now();
	thread t1(op2, pool, 0, 1000);
	thread t2(op2, pool, 1000, 2000);
	thread t3(op2, pool, 2000, 3000);
	thread t4(op2, pool, 3000, 4000);
	thread t5(op2, pool, 4000, 5000);
	t1.join();
	t2.join();
	t3.join();
	t4.join();
	t5.join();
	steady_clock::time_point end = steady_clock::now();
	auto length = end - begin;
	cout << "连接池用时: " << length.count() << " 纳秒, "
		<< length.count() / 1000000 << " 毫秒" << endl;

#endif

	return 0;
}

 下面是连接池时结果：