From 2e420a0a40cc3fcc62bcb42ee6a25f4ad4f22dd3 Mon Sep 17 00:00:00 2001
From: wangky <m1561510467@163.com>
Date: 星期三, 06 十一月 2024 16:26:04 +0800
Subject: [PATCH] 1
---
Server/王琨元/code/ConnectionPool.cpp | 182 +++++++++++--------------
Server/王琨元/code/MysqlConn.h | 41 +++--
Server/王琨元/code/MysqlConn.cpp | 133 +++++++++++++------
Server/王琨元/code/ConnectionPool.h | 58 +++----
4 files changed, 219 insertions(+), 195 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"
index ce7acb2..2fff64c 100644
--- "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"
@@ -1,125 +1,101 @@
#include "stdafx.h"
#include "ConnectionPool.h"
-#include <fstream>
-#include <iostream>
-#include <thread>
-
-ConnectionPool* ConnectionPool::getInstance() {
- static ConnectionPool connPool;
- return &connPool;
-}
ConnectionPool::ConnectionPool() {
- // 加载配置文件
- if (!parseJsonFile()) {
- std::cout << "parseJsonFile is failed!" << std::endl;
- return;
- }
- for ( int i = 0 ; i < m_min_conn ; i++ ) {
- addConnection( );
- }
- // 创建一个线程，对连接数进行监控 ，连接数不足就再继续创建
- std::thread producer(&ConnectionPool::produce, this);
- // 对连接数进行监控 ，如果有太多空闲得线程 ，那么就对其进行销毁
- std::thread recycler( &ConnectionPool::recycle, this);
- // 线程分离
- producer.detach();
- recycler.detach();
+
+ m_min_conn = 5; // 假设最小连接数为 5
+ m_max_conn = 10; // 假设最大连接数为 10
+ m_timeout = 1000; // 假设连接超时时间为 1000 毫秒
+ max_del_time = 60000; // 假设最大空闲时间为 60000 毫秒
+
+ for (int i = 0; i < m_min_conn; i++) {
+ addConnection();
+ }
+ // 创建一个线程，对连接数进行监控 ，连接数不足就再继续创建
+ thread producer(&ConnectionPool::produce, this);
+ // 对连接数进行监控 ，如果有太多空闲得线程 ，那么就对其进行销毁
+ thread recycler(&ConnectionPool::recycle, this);
+
+ // 线程分离
+ producer.detach();
+ recycler.detach();
}
-bool ConnectionPool::parseJsonFile() {
-
- /*std::ifstream ifs("dbconf.json");
- Reader rd;
- Value root;
- rd.parse(ifs, root);
- if ( root.isObject( ) ) {
- m_ip = root["ip"].asString();
- m_port = root["port"].asInt();
- m_userName = root["userName"].asString();
- m_passwd = root["password"].asString();
- m_db = root["dbName"].asString();
- m_min_conn = root["minSize"].asInt();
- m_max_conn = root["maxSize"].asInt();
- max_del_time = root["maxDleTime"].asInt();
- m_timeout = root["timeout"].asInt();
- return true;
- }*/
- return false;
-}
-
-void ConnectionPool::description() {
- std::cout << m_ip << ". " << m_userName << ". " << m_passwd << ". " << m_db << ". "
- << m_port << ". " << m_max_conn << ". " << m_min_conn << ". " << m_timeout << ". "
- << max_del_time << std::endl;
-}
ConnectionPool::~ConnectionPool() {
- while (!m_connkQueue.empty()) {
- MysqlConn* conn = m_connkQueue.front();
- m_connkQueue.pop();
- delete conn;
- }
-
+ while (!m_connkQueue.empty()) {
+ MysqlConn* conn = m_connkQueue.front();
+ m_connkQueue.pop();
+ delete conn;
+ }
}
void ConnectionPool::addConnection() {
- MysqlConn* conn = new MysqlConn();
- if ( !conn->connect( m_ip, m_userName, m_passwd, m_db, m_port ) ) {
- std::cout << "ConnectionPool connect to mysql is failed!" << std::endl;
- delete conn;
- return;
- }
- conn->refreshActiveTime( );
- m_connkQueue.push(conn);
- m_cond.notify_one(); // 唤醒一个线程
+ MysqlConn* conn = new MysqlConn();
+ if (!conn->isConnected()) {
+ cout << "ConnectionPool connect to mysql is failed!" << endl;
+ delete conn;
+ return;
+ }
+ conn->refreshActiveTime();
+ m_connkQueue.push(conn);
+ m_cond.notify_one(); // 唤醒一个线程
}
-void ConnectionPool::produce(){
- while (true) {
- std::unique_lock<std::mutex> lock(m_mutex);
- while (m_connkQueue.size() >= m_min_conn) { // 连接队列的数量大于最小的连接数
- m_cond.wait(lock);
- }
- addConnection();
- }
+void ConnectionPool::produce() {
+ while (true) {
+ unique_lock<mutex> lock(m_mutex);
+ while (m_connkQueue.size() >= m_min_conn) { // 连接队列的数量大于最小的连接数
+ m_cond.wait(lock);
+ }
+ addConnection();
+ }
}
// 删除空闲连接
void ConnectionPool::recycle() {
- while ( true ) {
- std::this_thread::sleep_for( std::chrono::milliseconds(500)); // 休眠500 ms
- std::unique_lock<std::mutex>lock(m_mutex);
- while ( m_connkQueue.size() > m_min_conn ) {
- MysqlConn* conn = m_connkQueue.front( );
- if (conn->getActiveTime() >= max_del_time) {
- m_connkQueue.pop();
- delete conn;
- }
- else {
- break;
- }
- }
- }
+ while (true) {
+ this_thread::sleep_for(chrono::milliseconds(500)); // 休眠 500 ms
+ unique_lock<mutex> lock(m_mutex);
+ while (m_connkQueue.size() > m_min_conn) {
+ MysqlConn* conn = m_connkQueue.front();
+ if (conn->getActiveTime() >= max_del_time) {
+ m_connkQueue.pop();
+ delete conn;
+ }
+ else {
+ break;
+ }
+ }
+ }
}
-std::shared_ptr<MysqlConn> ConnectionPool::getMysqlConn() {
-
- std::unique_lock<std::mutex>lock(m_mutex);
- while ( m_connkQueue.empty()) {
- // 如果等待一段时间后,队列还是为空,返回一个 null
- if ( std::cv_status::timeout == m_cond.wait_for(lock, std::chrono::milliseconds(m_timeout)) ) {
- if( m_connkQueue.empty( ) ) return nullptr;
- }
- }
- std::shared_ptr<MysqlConn> connPtr(std::move(m_connkQueue.front()), [this](MysqlConn* conn) {
- std::unique_lock <std::mutex>lock(m_mutex) ;
- conn->refreshActiveTime();
- m_connkQueue.push(conn);
- });
- m_connkQueue.pop();
- m_cond.notify_one(); // 唤醒阻塞的生产者线程,开始生产
- return connPtr;
+shared_ptr<MysqlConn> ConnectionPool::getMysqlConn() {
+ unique_lock<mutex> lock(m_mutex);
+ while (m_connkQueue.empty()) {
+ // 如果等待一段时间后,队列还是为空,返回一个 null
+ if (cv_status::timeout == m_cond.wait_for(lock, chrono::milliseconds(m_timeout))) {
+ if (m_connkQueue.empty()) return nullptr;
+ }
+ }
+ shared_ptr<MysqlConn> connPtr(move(m_connkQueue.front()), [this](MysqlConn* conn) {
+ unique_lock<mutex> lock(m_mutex);
+ conn->refreshActiveTime();
+ m_connkQueue.push(conn);
+ });
+ m_connkQueue.pop();
+ m_cond.notify_one(); // 唤醒阻塞的生产者线程,开始生产
+ return connPtr;
+}
+void ConnectionPool::description() {
+ cout << m_ip << ". " << m_userName << ". " << m_passwd << ". " << m_db << ". "
+ << m_port << ". " << m_max_conn << ". " << m_min_conn << ". " << m_timeout << ". "
+ << max_del_time << endl;
+}
+
+ConnectionPool* ConnectionPool::getInstance() {
+ static ConnectionPool connPool;
+ return &connPool;
}
\ No newline at end of file
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"
index bce6a07..c78a46e 100644
--- "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"
@@ -1,50 +1,44 @@
#pragma once
-#include <queue>
-#include <mutex>
-#include <condition_variable>
-//#include <json/json.h>
-
#include "MysqlConn.h"
-
-//using namespace Json;
class ConnectionPool
{
public:
- static ConnectionPool* getInstance( );
+ static ConnectionPool* getInstance();
- std::shared_ptr<MysqlConn> getMysqlConn(); // 从数据库连接池获取连接
+ shared_ptr<MysqlConn> getMysqlConn(); // 从数据库连接池获取连接
- ConnectionPool(const ConnectionPool& other) = delete;
- ConnectionPool& operator = (const ConnectionPool & other ) = delete;
-
- ~ConnectionPool();
+ ConnectionPool(const ConnectionPool& other) = delete;
+ ConnectionPool& operator=(const ConnectionPool& other) = delete;
- void description();
+ ~ConnectionPool();
+
+ void description();
protected:
- ConnectionPool( ); // 构造函数
+ ConnectionPool(); // 构造函数
private:
- bool parseJsonFile(); // 解析配置
+ // 不再需要解析配置文件的函数
+ // bool parseJsonFile(); // 解析配置
- void produce();
- void recycle();
- void addConnection(); // 增加连接数
+ void produce();
+ void recycle();
+ void addConnection(); // 增加连接数
- std::string m_ip;
- std::string m_userName;
- std::string m_passwd;
- std::string m_db;
- unsigned short m_port;
+ // 写死的数据库连接参数
+ string m_ip = "127.0.0.1";
+ string m_userName = "root";
+ string m_passwd = "123456";
+ string m_db = "mayi_kunlun";
+ unsigned short m_port = 3306;
- int m_max_conn;
- int m_min_conn;
+ int m_max_conn;
+ int m_min_conn;
- int m_timeout; // 连接超时时间
- int max_del_time; // 最大删除时间( 连接空闲时间超过这个,就给当前连接关闭 )
- std::queue<MysqlConn*>m_connkQueue ; // 连接队列
- std::mutex m_mutex; // 互斥锁
- std::condition_variable m_cond; // 条件变量
+ int m_timeout; // 连接超时时间
+ int max_del_time; // 最大删除时间( 连接空闲时间超过这个,就给当前连接关闭 )
+ queue<MysqlConn*> m_connkQueue; // 连接队列
+ mutex m_mutex; // 互斥锁
+ condition_variable m_cond; // 条件变量
};
-
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"
index 5725af8..071cf5f 100644
--- "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"
@@ -1,37 +1,60 @@
#include "stdafx.h"
#include "MysqlConn.h"
-#include <regex>
+
// 初始化连接
+// 初始化连接
MysqlConn::MysqlConn() {
- mysql_ = mysql_init(mysql_);
- // 设置字符集
- if (mysql_) mysql_set_character_set(mysql_, "gbk");
-}
-
-// 连接数据库
-bool MysqlConn::connect(std::string ip, std::string userName, std::string passwd, std::string db, int port) {
- mysql_ = mysql_real_connect(mysql_, ip.c_str(), userName.c_str(), passwd.c_str(), db.c_str(), port, nullptr, 0);
- if (!mysql_) {
- return false;
+ m_mysql = mysql_init(nullptr);
+ if (!m_mysql) {
+ cout << "Failed to initialize MySQL." << endl;
+ return;
}
- return true;
+ // 设置字符集
+ if (m_mysql) mysql_set_character_set(m_mysql, "gbk");
+ // 使用写死的参数连接数据库
+ m_mysql = mysql_real_connect(m_mysql, "127.0.0.1", "root", "123456", "mayi_kunlun", 3306, nullptr, 0);
+ if (!m_mysql) {
+ cout << "MysqlConn connect to mysql is failed!" << endl;
+ // 确保在连接失败时正确清理资源
+ mysql_close(m_mysql);
+ m_mysql = nullptr;
+ }
+ // 初始化 row_ 和 res_
+ m_row = nullptr;
+ m_res = nullptr;
}
+bool MysqlConn::isConnected() {
+ return m_mysql != nullptr;
+}
+// 连接数据库
+//bool MysqlConn::connect() {
+// string ip = "127.0.0.1";
+// string userName = "root";
+// string passwd = "123456";
+// string db = "mayi_kunlun";
+// int port = 3306;
+// m_mysql = mysql_real_connect(mysql_, ip.c_str(), userName.c_str(), passwd.c_str(), db.c_str(), port, nullptr, 0);
+// if (!m_mysql) {
+// return false;
+// }
+// return true;
+//}
// 释放资源
MysqlConn::~MysqlConn() {
- if (mysql_) {
- mysql_close(mysql_);
- mysql_ = nullptr;
+ if (m_mysql) {
+ mysql_close(m_mysql);
+ m_mysql = nullptr;
}
freeRes();
}
// 更新数据
-bool MysqlConn::update(std::string sql) {
+bool MysqlConn::update(string sql) {
// 参数化查询优化后的安全检查
- MYSQL_STMT* stmt = mysql_stmt_init(mysql_);
+ MYSQL_STMT* stmt = mysql_stmt_init(m_mysql);
if (!stmt) {
return false;
}
@@ -53,11 +76,36 @@
return true;
}
+bool MysqlConn::query(string sql, vector<vector<string>>& result)
+{
+ freeRes();
+ int ret = mysql_query(m_mysql, sql.c_str());
+ if (ret != 0) {
+ fprintf(stderr, "Query failed: %s\n", mysql_error(m_mysql));
+ return false;
+ }
+ // 获取查询结果
+ m_res = mysql_store_result(m_mysql);
+ if (!m_res) return false;
+ //将结果集转到二维数组result里面
+ //int col_cnt = m_mysql->field_count;
+ int col_cnt = mysql_field_count(m_mysql);
+ cout << col_cnt << endl;
+ while (m_row = mysql_fetch_row(m_res)) {
+ vector<string> tmp;
+ for (int i = 0; i < col_cnt; i++) {
+ tmp.push_back(m_row[i]);
+ }
+ result.push_back(tmp);
+ }
+ return true;
+}
+
// 查询数据库
-bool MysqlConn::query(std::string sql) {
+bool MysqlConn::query(string sql) {
freeRes();
// 参数化查询优化后的安全检查
- MYSQL_STMT* stmt = mysql_stmt_init(mysql_);
+ MYSQL_STMT* stmt = mysql_stmt_init(m_mysql);
if (!stmt) {
return false;
}
@@ -75,8 +123,8 @@
mysql_stmt_close(stmt);
return false;
}
- res_ = mysql_stmt_result_metadata(stmt);
- if (!res_) {
+ m_res = mysql_stmt_result_metadata(stmt);
+ if (!m_row) {
mysql_stmt_close(stmt);
return false;
}
@@ -86,59 +134,60 @@
// 得到结果集
bool MysqlConn::getResult() {
- if (res_) {
- row_ = mysql_fetch_row(res_);
- if (row_) return true;
+ if (m_row) {
+ m_row = mysql_fetch_row(m_res);
+ if (m_row) return true;
}
return false;
}
// 获取结果集的字段
-std::string MysqlConn::getField(int index) {
- int cols = mysql_num_fields(res_);
- if (index >= cols || index < 0) return std::string("");
+string MysqlConn::getField(int index) {
+ int cols = mysql_num_fields(m_res);
+ if (!m_res || index >= cols || index < 0) return string("");
- char* value = row_[index];
- unsigned long len = mysql_fetch_lengths(res_)[index];
- return std::string(value, len);
+ char* value = m_row[index];
+ // 使用显式类型转换
+ unsigned long len = static_cast<unsigned long>(mysql_fetch_lengths(m_res)[index]);
+ return string(value, len);
}
// 事务操作
bool MysqlConn::transaction() {
- return mysql_autocommit(mysql_, false);
+ return mysql_autocommit(m_mysql, false);
}
// 提交事务
bool MysqlConn::commit() {
- return mysql_commit(mysql_);
+ return mysql_commit(m_mysql);
}
// 事务回滚
bool MysqlConn::rollback() {
- return mysql_rollback(mysql_);
+ return mysql_rollback(m_mysql);
}
void MysqlConn::refreshActiveTime() {
- activeTime_ = std::chrono::steady_clock::now();
+ m_activeTime = chrono::steady_clock::now();
}
long long MysqlConn::getActiveTime() {
// 纳米
- std::chrono::nanoseconds nased = std::chrono::steady_clock::now() - activeTime_;
+ chrono::nanoseconds nased = chrono::steady_clock::now() - m_activeTime;
// 转换成毫米
- std::chrono::microseconds millsed = std::chrono::duration_cast<std::chrono::microseconds>(nased);
+ chrono::microseconds millsed = chrono::duration_cast<chrono::microseconds>(nased);
return millsed.count(); // 多少毫秒
}
// 安全校验实现，这里简单使用正则表达式判断是否包含危险字符
-bool MysqlConn::isSqlSafe(const std::string& sql) {
- std::regex dangerousPattern(".*(['\";\\-+=]).*");
- return!std::regex_search(sql, dangerousPattern);
+bool MysqlConn::isSqlSafe(const string& sql) {
+ regex dangerousPattern(".*(['\";\\-+=]).*");
+ return!regex_search(sql, dangerousPattern);
}
void MysqlConn::freeRes() {
- if (res_) {
- mysql_free_result(res_);
- res_ = nullptr;
+ if (m_res) {
+ mysql_free_result(m_res);
+ m_res = nullptr;
}
}
\ No newline at end of file
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"
index 00c9917..a6506d1 100644
--- "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"
@@ -2,48 +2,53 @@
#include <string>
#include <WinSock2.h>
-//#include <mysql.h>
#include "MySQL/include/mysql.h"
#include <string.h>
#include <chrono>
-
+#include <iostream>
+#include <memory>
+#include <queue>
+#include <mutex>
+#include <condition_variable>
+#include <regex>
#pragma comment(lib,"./MySQL/lib/libmysql.lib")
+using namespace std;
class MysqlConn
{
public:
// 初始化连接
MysqlConn();
// 连接数据库
- bool connect(std::string ip, std::string userName, std::string passwd, std::string db , int port = 3306);
+ //bool connect();
// 释放资源
- ~MysqlConn( );
+ ~MysqlConn();
// 更新数据库 ( insert update delete )
- bool update(std::string sql);
+ bool update(string sql);
// 查询数据库
- bool query(std::string sql);
+ bool query(string sql, vector<vector<string>>& result);
+ bool query(string sql);
// 得到结果集
bool getResult();
// 获取结果集的字段
- std::string getField(int index);
+ string getField(int index);
// 事务操作
bool transaction();
// 提交事务
- bool commit( );
+ bool commit();
// 事务回滚
bool rollback();
- void refreshActiveTime( ); // 刷新活跃时间
+
+ void refreshActiveTime(); // 刷新活跃时间
long long getActiveTime(); // 获取当前活跃的时间间隔
// 安全校验接口
- bool isSqlSafe(const std::string& sql);
-
-private:
-
+ bool isSqlSafe(const string& sql);
+ bool isConnected();
+protected:
void freeRes();
- MYSQL* mysql_ = NULL ;
- MYSQL_RES* res_ = NULL;
- MYSQL_ROW row_ = nullptr;
- std::chrono::steady_clock::time_point activeTime_;
+ MYSQL* m_mysql;
+ MYSQL_RES* m_res;
+ MYSQL_ROW m_row;
+ chrono::steady_clock::time_point m_activeTime;
};
-
--
Gitblit v1.8.0