From 9583a7be21958cafdf30f03526dbf68838aa883f Mon Sep 17 00:00:00 2001
From: wangky <m1561510467@163.com>
Date: 星期四, 31 十月 2024 14:57:07 +0800
Subject: [PATCH] 1
---
Server/王琨元/code/ConnectionPool.cpp | 131 ++++++++++++++++
Server/王琨元/code/MysqlConn.h | 63 +++++++
Server/王琨元/code/MysqlConn.cpp | 174 +++++++++++++++++++++
Server/王琨元/code/ConnectionPool.h | 54 ++++++
4 files changed, 422 insertions(+), 0 deletions(-)
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/code/ConnectionPool.cpp" "b/Server/\347\216\213\347\220\250\345\205\203/code/ConnectionPool.cpp"
new file mode 100644
index 0000000..367a7f1
--- /dev/null
+++ "b/Server/\347\216\213\347\220\250\345\205\203/code/ConnectionPool.cpp"
@@ -0,0 +1,131 @@
+#include "ConnectionPool.h"
+ConnectionPool::ConnectionPool()
+{
+ if (!parseXmlFile())
+ return;
+ for (m_num = 0; m_num < m_minSize;) {
+ bool flag = addConnection();
+ if (!flag) {
+ return;
+ }
+ }
+ // 如果子线程的任务函数是类的非静态函数，我们需要指定任务函数的地址和任务函数的所有者
+ thread producer(&ConnectionPool::productConnection, this); // 创建连接
+ thread recycler(&ConnectionPool::recycleConnection, this); // 检测并销毁连接
+ // 线程分离，防止阻塞主线程
+ producer.detach();
+ recycler.detach();
+}
+ConnectionPool::~ConnectionPool()
+{
+ while (!m_connections.empty()) {
+ MysqlConn* conn = m_connections.front();
+ m_connections.pop();
+ delete conn;
+ }
+}
+bool ConnectionPool::parseXmlFile()
+{
+ TiXmlDocument xml("mysql.xml");
+ // 加载文件
+ bool res = xml.LoadFile();
+ if (!res) {
+ return false; // 提示
+ }
+ // 根
+ TiXmlElement* rootElement = xml.RootElement();
+ TiXmlElement* childElement = rootElement->FirstChildElement("mysql");
+ // 读取信息
+ m_ip = childElement->FirstChildElement("ip")->GetText();
+ m_port = static_cast<unsigned short>(stoi(string(childElement->FirstChildElement("port")->GetText())));
+ m_user = childElement->FirstChildElement("username")->GetText();
+ m_passwd = childElement->FirstChildElement("password")->GetText();
+ m_dbName = childElement->FirstChildElement("dbName")->GetText();
+ m_minSize = static_cast<int>(stoi(string(childElement->FirstChildElement("minSize")->GetText())));
+ m_maxSize = static_cast<int>(stoi(string(childElement->FirstChildElement("maxSize")->GetText())));
+ m_maxIdleTime = static_cast<int>(stoi(string(childElement->FirstChildElement("maxIdleTime")->GetText())));
+ m_timeout = static_cast<int>(stoi(string(childElement->FirstChildElement("timeout")->GetText())));
+ return true;
+}
+bool ConnectionPool::addConnection()
+{
+ MysqlConn* conn = new MysqlConn;
+ bool res = conn->connect(m_user, m_passwd, m_dbName, m_ip, m_port);
+ if (res) {
+ // 刷新空闲时间
+ conn->refreashAliveTime();
+ m_connections.push(conn);
+ ++m_num;
+ return true;
+ }
+ else {
+ delete conn;
+ return false; // 提示
+ }
+}
+void ConnectionPool::productConnection()
+{
+ while (true) {
+ unique_lock<mutex> lc(m_mutex);
+ m_cond.wait(lc, [this]() {return m_connections.empty(); });
+ if (m_num < m_maxSize) {
+ bool flag = addConnection();
+ if (!flag) {
+ return;
+ }
+ }
+ // 唤醒
+ m_cond1.notify_all();
+ }
+}
+void ConnectionPool::recycleConnection()
+{
+ while (true) {
+ // 休眠一段时间 0.5s
+ this_thread::sleep_for(milliseconds(500));
+ lock_guard<mutex> lc(m_mutex);
+ while (!m_connections.empty() && m_num > m_minSize) {
+ MysqlConn* conn = m_connections.front();
+ if (conn->getAliveTime() >= m_maxIdleTime) {
+ m_connections.pop();
+ delete conn;
+ --m_num;
+ }
+ else {
+ break;
+ }
+ }
+ }
+}
+ConnectionPool* ConnectionPool::getConnectPool()
+{
+ // 不使用互斥锁的线程安全的懒汉模式
+ static ConnectionPool pool; // 只在第一次调用函数时初始化
+ return &pool;
+}
+shared_ptr<MysqlConn> ConnectionPool::getConnection()
+{
+ unique_lock<mutex> lc(m_mutex);
+ while (m_connections.empty()) {
+ if (cv_status::timeout == m_cond1.wait_for(lc, chrono::milliseconds(m_timeout))) {
+ if (m_connections.empty()) {
+ // cout << "out of time" << endl;
+ return nullptr; // 结束 // 提示
+ // continue; // 利用while配合continue 继续阻塞
+ }
+ }
+ }
+ // 要指定删除器destructor，来保证连接的归还
+ shared_ptr<MysqlConn> conn(m_connections.front(), [this](MysqlConn* conn) {
+ // 加锁保证队列线程安全
+ // m_mutex.lock(); // 1
+ unique_lock<mutex> lc(m_mutex); // 2
+ // lock_guard<mutex> lc(m_mutex); // 3
+ conn->refreashAliveTime();
+ m_connections.push(conn);
+ // m_mutex.unlock(); // 1
+ });
+ m_connections.pop();
+ m_cond.notify_all();
+ return conn;
+}
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/code/ConnectionPool.h" "b/Server/\347\216\213\347\220\250\345\205\203/code/ConnectionPool.h"
new file mode 100644
index 0000000..3ad1ee0
--- /dev/null
+++ "b/Server/\347\216\213\347\220\250\345\205\203/code/ConnectionPool.h"
@@ -0,0 +1,54 @@
+#pragma once
+#include "MysqlConn.h"
+#include "./tinyxml/tinyxml.h"
+#include <queue>
+#include <mutex>
+#include <thread>
+#include <atomic>
+#include <condition_variable>
+
+
+#pragma comment(lib, "./tinyxml/x64/Debug/tinyxml.lib")
+
+// 应用-单例模式：懒汉模式[需要考虑多线程安全问题]
+class ConnectionPool
+{
+private:
+ ConnectionPool();
+ // 移动拷贝最终还是有且仅有一个对象，所以依旧是属于单例模式。
+ // delete 阻止拷贝构造和拷贝赋值的类对象生成
+ ConnectionPool(ConnectionPool&) = delete;
+ ConnectionPool& operator=(ConnectionPool&) = delete;
+ ~ConnectionPool();
+ // 解析xml配置文件 读取数据库及连接池的相关信息
+ bool parseXmlFile();
+ // 添加连接
+ bool addConnection();
+ // 线程函数
+ void productConnection();
+ void recycleConnection();
+
+ // 存放数据库连接池建立的连接
+ queue<MysqlConn*> m_connections;
+ // 一些基本信息
+ string m_ip; // IP
+ unsigned short m_port; // 端口
+ string m_user; // 用户名
+ string m_passwd; // 密码
+ string m_dbName; // 数据库名称
+ int m_minSize; // 初始连接量(最小连接量)
+ int m_maxSize; // 最大连接量
+ int m_timeout; // 超时时长
+ int m_maxIdleTime; // 最大空闲时长
+ // 线程安全相关
+ mutex m_mutex;
+ condition_variable m_cond; // 仅用一个条件变量来唤醒线程，但并不影响线程运行
+ condition_variable m_cond1;
+ // 连接数量
+ atomic_int m_num; // 连接的总数量
+public:
+ // 获取单例对象的接口
+ static ConnectionPool* getConnectPool();
+ // 用户获取连接的接口, 如果获取失败，会返回nullptr
+ shared_ptr<MysqlConn> getConnection();
+};
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/code/MysqlConn.cpp" "b/Server/\347\216\213\347\220\250\345\205\203/code/MysqlConn.cpp"
new file mode 100644
index 0000000..57ba1c6
--- /dev/null
+++ "b/Server/\347\216\213\347\220\250\345\205\203/code/MysqlConn.cpp"
@@ -0,0 +1,174 @@
+#include "MysqlConn.h"
+
+MysqlConn::MysqlConn()//初始化数据库
+{
+ m_result = nullptr;
+ m_mysqlRow = nullptr;
+ // 传入nullptr空指针时，会自动分配一个MYSQL对象
+ m_conn = mysql_init(nullptr);
+}
+MysqlConn::~MysqlConn()//释放数据库连接
+{
+ freeRes(); // 释放结果集
+ if (m_conn != nullptr) {
+ mysql_close(m_conn);
+ m_conn = nullptr;
+ }
+}
+void MysqlConn::freeRes()//结果集释放
+{
+ if (m_result) {
+ mysql_free_result(m_result);
+ m_result = nullptr;
+ }
+}
+bool MysqlConn::connect(const string user, const string passwd, \
+ const string dbName, string ip, \
+ const unsigned short port)//连接数据库
+{
+ MYSQL* res = mysql_real_connect(m_conn, ip.c_str(), user.c_str(), \
+ passwd.c_str(), dbName.c_str(), port, nullptr, 0);
+ // 修改编码
+ mysql_set_character_set(m_conn, "gb2312");
+ return res != nullptr;
+}
+bool MysqlConn::update(const string sql) const
+{
+ // 执行成功返回0；
+ int res = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned int>(sql.size()));
+ if (res != 0) {
+ return false; // 提示
+ }
+ return true;
+}
+bool MysqlConn::query(const string sql)
+{
+ freeRes();
+ int res = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned int>(sql.size()));
+ if (res != 0) {
+ return false; // 提示
+ }
+ m_result = mysql_store_result(m_conn);
+ return true;
+}
+bool MysqlConn::getRes()
+{
+ if (m_result != nullptr) {
+ // char** 获取单行记录
+ m_mysqlRow = mysql_fetch_row(m_result);
+ if (m_mysqlRow != nullptr) {
+ return true;
+ }
+ freeRes();
+ }
+ return false;
+}
+string MysqlConn::getValue(const int fieldIndex) const
+{
+ int fieldCount = mysql_num_fields(m_result);
+ if (fieldIndex >= fieldCount || fieldIndex < 0) {
+ return string(); // 提示
+ }
+ char* value = m_mysqlRow[fieldIndex];
+ // 得到一个保存各字段值长度的数组
+ unsigned long* len = mysql_fetch_lengths(m_result);
+ unsigned long length = len[fieldIndex];
+ // 防止结果中存在\0导致数据丢失
+ return string(value, length);
+}
+bool MysqlConn::selectDB(const string dbName) const
+{
+ int res = mysql_select_db(m_conn, dbName.c_str());
+ if (res != 0) {
+ return false; // 提示
+ }
+ return true;
+}
+void MysqlConn::backupCurrentDB(const string path)
+{
+ string sql = "show tables";
+ int r = mysql_real_query(m_conn, sql.c_str(), static_cast<unsigned long>(sql.size()));
+ if (r != 0) {
+ return; // 提示
+ }
+ MYSQL_RES* tableRes = mysql_store_result(m_conn);
+ for (int i = 0; i < mysql_num_rows(tableRes); ++i) {
+ MYSQL_ROW tableName = mysql_fetch_row(tableRes);
+ backupCurrentTable(path, tableName[0]);
+ }
+}
+void MysqlConn::backupCurrentTable(const string path, const string tableName)
+{
+ string file = path + tableName + ".sql";
+ ofstream ofs(file);
+ if (!ofs.is_open()) {
+ return; // 提示
+ }
+ // 表结构写入
+ string showCreate = "show create table " + tableName + ";";
+ bool res = query(showCreate);
+ if (!res) {
+ return; // 提示
+ }
+ if (getRes()) {
+ string writeSQL = getValue(1) + ";\n";
+ ofs.write(writeSQL.c_str(), writeSQL.size());
+ // cout << writeSQL << endl;
+ }
+ // 表数据写入
+ string sql = "select * from " + tableName + ";";
+ res = query(sql);
+ if (!res) {
+ return; // 提示
+ }
+ while (getRes()) {
+ string writeSQL = "insert into `" + tableName + "` values(";
+ for (int i = 0; !getValue(i).empty(); ++i) {
+ if (i != 0) {
+ writeSQL += ",";
+ }
+ MYSQL_FIELD* valueType = mysql_fetch_field_direct(m_result, i);
+ if (valueType->type == MYSQL_TYPE_DECIMAL
+ || valueType->type == MYSQL_TYPE_TINY
+ || valueType->type == MYSQL_TYPE_SHORT
+ || valueType->type == MYSQL_TYPE_LONG
+ || valueType->type == MYSQL_TYPE_FLOAT
+ || valueType->type == MYSQL_TYPE_DOUBLE
+ || valueType->type == MYSQL_TYPE_TIMESTAMP
+ || valueType->type == MYSQL_TYPE_LONGLONG
+ || valueType->type == MYSQL_TYPE_INT24) {
+ writeSQL += getValue(i);
+ }
+ else {
+ writeSQL += "'" + getValue(i) + "'";
+ }
+ }
+ writeSQL += ");\n";
+ ofs.write(writeSQL.c_str(), writeSQL.size());
+ }
+ ofs.close();
+}
+bool MysqlConn::transaction() const
+{
+ // 将事务提交设置为手动提交
+ return mysql_autocommit(m_conn, false);
+}
+bool MysqlConn::commit() const
+{
+ return mysql_commit(m_conn);
+}
+bool MysqlConn::rollback() const
+{
+ return mysql_rollback(m_conn);
+}
+void MysqlConn::refreashAliveTime()
+{
+ m_alivetime = steady_clock::now();
+}
+ll MysqlConn::getAliveTime()
+{
+ // 毫秒 <<= 纳秒:精度降低
+ milliseconds res = duration_cast<milliseconds>(steady_clock::now() - m_alivetime);
+ return res.count();
+}
+
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/code/MysqlConn.h" "b/Server/\347\216\213\347\220\250\345\205\203/code/MysqlConn.h"
new file mode 100644
index 0000000..0dbbea9
--- /dev/null
+++ "b/Server/\347\216\213\347\220\250\345\205\203/code/MysqlConn.h"
@@ -0,0 +1,63 @@
+#pragma once
+#include<iostream>
+#include <memory>
+#include <string>
+#include <mysql.h>
+#include <chrono> // 时钟
+#include <fstream>
+
+#pragma comment(lib, "./lib/libmysql.lib") // 加载数据库库文件
+using namespace std;
+using namespace chrono;
+typedef long long ll;
+
+class MysqlConn
+{
+private:
+ // 绝对时钟
+ steady_clock::time_point m_alivetime;
+ // 连接
+ MYSQL* m_conn;
+ // 查询的结果集
+ MYSQL_RES* m_result;
+ // 单记录结果集
+ MYSQL_ROW m_mysqlRow;
+
+ // 结果集释放
+ void freeRes();
+ // 导出某一张表中的数据
+ void backupCurrentTable(const string path, const string tableName);
+public:
+ // 初始化数据库
+ MysqlConn();
+ // 数据库连接释放
+ ~MysqlConn();
+ // 连接数据库, 需提供用户 密码 数据库名称 ip 端口
+ bool connect(const string user, const string passwd, \
+ const string dbName, string ip, \
+ const unsigned short port = 3306U);
+ // 更新数据库:增删改操作
+ bool update(const string sql) const;
+ // 查询数据库
+ bool query(const string sql);
+ // 遍历查询结果集
+ bool getRes();
+ // 获取结果集中的字段值
+ string getValue(const int fieldIndex) const;
+ // 切换数据库
+ bool selectDB(const string dbName) const;
+ // 建库
+ //bool createDB(const string dbName) const;
+ // 备份某个库
+ void backupCurrentDB(const string path);
+ // 事务操作
+ bool transaction() const;
+ // 提交事务
+ bool commit() const;
+ // 事务回滚
+ bool rollback() const;
+ // 刷新起始的空闲时间点
+ void refreashAliveTime();
+ // 计算存活总时长
+ ll getAliveTime();
+};
--
Gitblit v1.8.0