这个项目介绍了用C++语言实现MySQL数据库的连接池项目，包括项目功能点分析，功能设计，项目编码以及压力测试四个部分。项目代码量约400行，涉及了11个核心功能点，包括 MySQL数据库编程、单例模式、queue队列容器、C++11多线程编程、线程互斥、线程同步通信、unique_lock智能锁、基于CAS的原子整形、智能指针shared_ptr、lambda表达式、生产者-消费者线程模型。
要求能够熟练C++语言、设计模式、智能指针和多线程编程、线程互斥、同步通信、生产者-消费者线程模型的综合应用，能提高知识的综合实践扩展能力。
编译环境：window10 Visual Studio 2019，需要加入mysql环境，MySQL版本为5.6
有任何问题请在下面评论，谢谢。
接下来直接上代码：
文件结构如下图：
头文件代码如下：
1.CommonConnectionPool.h
#pragma once
#include<queue>
#include<mutex>
#include<string>
#include"Connection.h"
#include"public.h"
#include <iostream>
#include<atomic>
#include<thread>
#include<functional>
#include<ctime>
#include<memory>
#include<condition_variable>
using namespace std;
/*实现连接池功能模块*/
class ConnectionPool
{
public:
static ConnectionPool* GetConnectionPool();
shared_ptr<Connection> getConnection();
private:
ConnectionPool();
ConnectionPool(ConnectionPool&) = delete;
ConnectionPool& operator=(ConnectionPool&) = delete;
bool loadConfigFile();//加载数据库信息
void produceConnectionTask();//生产连接
void scannerConnectionTask();//扫描空闲连接并将其delete
string _ip;//数据库IP地址
unsigned short _port;//数据库端口，3306
string username;//连接数据的用户名
string password;//用户名的密码
string _dbname;//数据库名
int _initSize;//连接池的初始大小
int _maxSize;//连接池的最大连接数
int _maxIdleTime;//连接池的最大空闲时间
int _connectionTimeout;//获取数据库超时时间
//保存数据库连接的容器
queue<Connection*> _connectionQue;
//保证容器线程安全的互斥锁
mutex _queueMutex;
//设置条件变量，用于生产者与消费者的线程安全控制
condition_variable cv;
//连接池中现有连接的数量
atomic_int _connectionCnt;
};
2.Connection.h
#pragma once
#include <winsock.h>
#include <mysql.h>
#include <string>
#include <ctime>
using namespace std;
/*
实现MySQL数据库的操作
*/
class Connection
{
public:
// 初始化数据库连接
Connection();
// 释放数据库连接资源
~Connection();
// 连接数据库
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);
//刷新数据库的存活时间
void setAliveTime() { _alivetime = clock(); }
clock_t getAliveTime() { return _alivetime - clock(); }
private:
MYSQL* _conn; // 表示和MySQL Server的一条连接
clock_t _alivetime;
};
3.pch.h
// 入门提示:
// 1. 使用解决方案资源管理器窗口添加/管理文件
// 2. 使用团队资源管理器窗口连接到源代码管理
// 3. 使用输出窗口查看生成输出和其他消息
// 4. 使用错误列表窗口查看错误
// 5. 转到“项目”>“添加新项”以创建新的代码文件，或转到“项目”>“添加现有项”以将现有代码文件添加到项目
// 6. 将来，若要再次打开此项目，请转到“文件”>“打开”>“项目”并选择 .sln 文件
#ifndef PCH_H
#define PCH_H
// TODO: 添加要在此处预编译的标头
#endif //PCH_H
4.public.h
#pragma once
#define LOG(str) cout << __FILE__ << ":" << __LINE__<<" "<<__TIMESTAMP__<<" : "<< str <<endl;
源文件
1.CommonConnectionPool.cpp
#include"CommonConnectionPool.h"
//创建连接池
ConnectionPool* ConnectionPool::GetConnectionPool()
{
static ConnectionPool pool;
return &pool;
}
//创建初始线程
ConnectionPool::ConnectionPool()
{
if (!loadConfigFile())
{
return;
}
//创初始连接池
for (int i = 0; i < _initSize; ++i)
{
Connection* p = new Connection();
p->connect(_ip,_port,username,password,_dbname);
p->setAliveTime();
_connectionQue.push(p);
_connectionCnt++;
}
//创建一个线程，负责产生连接
thread produce(bind(&ConnectionPool::produceConnectionTask,this));
produce.detach();
//创建一个线程，对连接池中的元素进行扫描，空闲时间太大的delete
thread scanner(bind(&ConnectionPool::scannerConnectionTask, this));
scanner.detach();
}
//加载数据库配置
bool ConnectionPool::loadConfigFile()
{
FILE *pf = fopen("mysql.ini","r");
if (pf == nullptr)
{
LOG("mysql.ini file is not exist!");
return false;
}
while (!feof(pf))
{
char line[1024] = { 0 };
fgets(line , 1024, pf);
string str = string(line);
int idx = str.find("=",0);
if (idx == -1)
{
continue;
}
int endidx = str.find("\n", idx);
string key = str.substr(0, idx);
string value = str.substr(idx + 1, endidx - idx - 1);
//cout << key << ":" << value << endl;
if (key == "ip")
{
_ip = value;
} else if(key == "port")
{
_port = atoi(value.c_str());
}
else if (key == "username")
{
username = value;
}
else if (key == "password")
{
password = value;
}
else if (key == "dbname")
{
_dbname = value;
}
else if (key == "initSize")
{
_initSize = atoi(value.c_str());
}
else if (key == "maxSize")
{
_maxSize = atoi(value.c_str());
}
else if (key == "maxIdleTime")
{
_maxIdleTime = atoi(value.c_str());
}
else if (key == "connectionTimeout")
{
_connectionTimeout = atoi(value.c_str());
}
}
return true;
}
//生产连接
void ConnectionPool::produceConnectionTask()
{
while (1)
{
unique_lock<mutex> lck(_queueMutex);
//如果连接池里还有元素，则等待，不产生元素直到连接池为空
while (!_connectionQue.empty())
{
cv.wait(lck);
}
//产生元素并判断元素是否大于连接池最大连接数
if (_connectionCnt < _maxSize)
{
Connection* cp = new Connection();
cp->connect(_ip, _port, username, password, _dbname);
cp->setAliveTime();
_connectionQue.push(cp);
_connectionCnt++;
}
//产生元素成功，通知消费者消费
cv.notify_all();
}
}
//获取连接
shared_ptr<Connection> ConnectionPool::getConnection()
{
unique_lock<mutex> lock(_queueMutex);
while (_connectionQue.empty())
{
//判断是否是因连接超时而导致程序被唤醒
if (cv_status::timeout == cv.wait_for(lock, chrono::milliseconds(_connectionTimeout)))
{
if (_connectionQue.empty())
{
LOG("数据库连接超时！");
return nullptr;
}
}
}
//从连接池中拿一个元素出来，并重新定义其析构，当该智能指针被释放时应放入连接池中而不是delete
shared_ptr <Connection> cp(_connectionQue.front(), [&](Connection* conn) {unique_lock<mutex> lock(_queueMutex); conn->setAliveTime(); _connectionQue.push(conn); });
_connectionQue.pop();
cv.notify_all();
return cp;
}
//扫描空闲连接并将其delete
void ConnectionPool::scannerConnectionTask()
{
while (1)
{
this_thread::sleep_for(chrono::seconds(_maxSize));
unique_lock<mutex> lock(_queueMutex);
while (_connectionCnt > _initSize)
{
Connection* p = _connectionQue.front();
if (p->getAliveTime() >=(_maxIdleTime*1000))
{
_connectionQue.pop();
_connectionCnt--;
delete p;
}
}
}
}
2.Connection.cpp
#pragma once
#include "pch.h"
#include "public.h"
#include "Connection.h"
#include <iostream>
using namespace std;
Connection::Connection()
{
// 初始化数据库连接
_conn = mysql_init(nullptr);
}
Connection::~Connection()
{
// 释放数据库连接资源
if (_conn != nullptr)
mysql_close(_conn);
}
bool Connection::connect(string ip, unsigned short port,string username, string password, string dbname)
{
// 连接数据库
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);
}
3.mian.cpp
使用多线程和单线程分别使用连接池做测试
#include "pch.h"
#include <iostream>
using namespace std;
#include "Connection.h"
#include "CommonConnectionPool.h"
int main()
{
//Connection conn;
//conn.connect("127.0.0.1", 3306, "root", "123456", "chat");
clock_t begin = clock();
thread t1([]() {
for (int i = 0; i < 2500; i++)
{
/*Connection conn;
char sql[1024] = { 0 };
sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhouli", 21, "male");
conn.connect("127.0.0.1", 3306, "root", "123456", "chat");
conn.update(sql);*/
ConnectionPool* cp = ConnectionPool::GetConnectionPool();
shared_ptr<Connection> conn = cp->getConnection();
char sql[1024] = { 0 };
sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhouli", 21, "male");
//conn->connect("127.0.0.1", 3306, "root", "123456", "chat");
conn->update(sql);
}
});
thread t2([]() {
for (int i = 0; i < 2500; i++)
{
/*Connection conn;
char sql[1024] = { 0 };
sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhouli", 21, "male");
conn.connect("127.0.0.1", 3306, "root", "123456", "chat");
conn.update(sql);*/
ConnectionPool* cp = ConnectionPool::GetConnectionPool();
shared_ptr<Connection> conn = cp->getConnection();
char sql[1024] = { 0 };
sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhouli", 21, "male");
//conn->connect("127.0.0.1", 3306, "root", "123456", "chat");
conn->update(sql);
}
});
thread t3([]() {
for (int i = 0; i < 2500; i++)
{
/*Connection conn;
char sql[1024] = { 0 };
sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhouli", 21, "male");
conn.connect("127.0.0.1", 3306, "root", "123456", "chat");
conn.update(sql);*/
ConnectionPool* cp = ConnectionPool::GetConnectionPool();
shared_ptr<Connection> conn = cp->getConnection();
char sql[1024] = { 0 };
sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhouli", 21, "male");
//conn->connect("127.0.0.1", 3306, "root", "123456", "chat");
conn->update(sql);
}
});
thread t4([]() {
for (int i = 0; i < 2500; i++)
{
/*Connection conn;
char sql[1024] = { 0 };
sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhouli", 21, "male");
conn.connect("127.0.0.1", 3306, "root", "123456", "chat");
conn.update(sql);*/
ConnectionPool* cp = ConnectionPool::GetConnectionPool();
shared_ptr<Connection> conn = cp->getConnection();
char sql[1024] = { 0 };
sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhouli", 21, "male");
//conn->connect("127.0.0.1", 3306, "root", "123456", "chat");
conn->update(sql);
}
});
t1.join();
t2.join();
t3.join();
t4.join();
clock_t end = clock();
cout << end - begin << "ms" << endl;
#if 0
ConnectionPool* cp = ConnectionPool::GetConnectionPool();
clock_t begin = clock();
for (int i = 0; i < 10000; i++)
{
shared_ptr<Connection> conn = cp->getConnection();
char sql[1024] = { 0 };
sprintf(sql, "insert into user(name,age,sex) values('%s',%d,'%s')", "zhouli", 21, "male");
//conn->connect("127.0.0.1", 3306, "root", "123456", "chat");
conn->update(sql);
}
clock_t end = clock();
cout << end - begin<<"ms" << endl;
//cp->loadConfigFile();
Connection conn;
char sql[1024] = { 0 };
sprintf(sql,"insert into user(name,age,sex) values('%s',%d,'%s')","zhouli",21,"male");
conn.connect("127.0.0.1",3306,"root","123456","chat");
conn.update(sql);
return 0;
#endif
}
4 mysql.ini
#保存数据库的信息
ip=127.0.0.1
port=3306
username=root
password=123456
dbname=chat
initSize=10
maxSize=1024
maxIdleTime=60
connectionTimeout=100
扫一扫
557
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
