From 6cc341355e736952329b328232c2560dc6f29060 Mon Sep 17 00:00:00 2001
From: lzx <2246256235@qq.com>
Date: 星期二, 05 十一月 2024 15:07:50 +0800
Subject: [PATCH] Merge branch 'master' of ssh://115.28.86.8:29418/~admin/昆仑_1025
---
Server/王琨元/code/ConnectionPool.cpp | 204 +++++++--------
Server/王琨元/code/MysqlConn.h | 88 ++---
Server/王琨元/code/MysqlConn.cpp | 310 ++++++++++-------------
~$表.docx | 0
表.docx | 0
kunlun.sql | 55 ++-
Server/王琨元/log/日志模板_王琨元_1104.doc | 0
Server/王琨元/code/ConnectionPool.h | 82 +++---
8 files changed, 349 insertions(+), 390 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 367a7f1..ce7acb2 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,95 +1,102 @@
+#include "stdafx.h"
#include "ConnectionPool.h"
-ConnectionPool::ConnectionPool()
-{
- if (!parseXmlFile())
+
+#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 (m_num = 0; m_num < m_minSize;) {
- bool flag = addConnection();
- if (!flag) {
- return;
- }
}
- // 如果子线程的任务函数是类的非静态函数，我们需要指定任务函数的地址和任务函数的所有者
- thread producer(&ConnectionPool::productConnection, this); // 创建连接
- thread recycler(&ConnectionPool::recycleConnection, this); // 检测并销毁连接
- // 线程分离，防止阻塞主线程
+ 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();
}
-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;
+
+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;
}
- else {
+
+}
+
+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 false; // 提示
+ return;
}
+ conn->refreshActiveTime( );
+ m_connkQueue.push(conn);
+ m_cond.notify_one(); // 唤醒一个线程
}
-void ConnectionPool::productConnection()
-{
+
+void ConnectionPool::produce(){
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;
- }
+ std::unique_lock<std::mutex> lock(m_mutex);
+ while (m_connkQueue.size() >= m_min_conn) { // 连接队列的数量大于最小的连接数
+ m_cond.wait(lock);
}
- // 唤醒
- m_cond1.notify_all();
+ addConnection();
}
}
-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();
+
+// 删除空闲连接
+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;
- --m_num;
}
else {
break;
@@ -97,35 +104,22 @@
}
}
}
-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 继续阻塞
- }
+
+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;
}
}
- // 要指定删除器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
+ 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_connections.pop();
- m_cond.notify_all();
- return conn;
-}
+ m_connkQueue.pop();
+ m_cond.notify_one(); // 唤醒阻塞的生产者线程,开始生产
+ return connPtr;
+}
\ 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 3ad1ee0..bce6a07 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,54 +1,50 @@
#pragma once
-#include "MysqlConn.h"
-#include "./tinyxml/tinyxml.h"
+
#include <queue>
#include <mutex>
-#include <thread>
-#include <atomic>
#include <condition_variable>
+//#include <json/json.h>
+#include "MysqlConn.h"
-#pragma comment(lib, "./tinyxml/x64/Debug/tinyxml.lib")
+//using namespace Json;
-// 应用-单例模式：懒汉模式[需要考虑多线程安全问题]
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();
+ static ConnectionPool* getInstance( );
+
+ std::shared_ptr<MysqlConn> getMysqlConn(); // 从数据库连接池获取连接
+
+ ConnectionPool(const ConnectionPool& other) = delete;
+ ConnectionPool& operator = (const ConnectionPool & other ) = delete;
+
+ ~ConnectionPool();
+
+ void description();
+
+protected:
+ ConnectionPool( ); // 构造函数
+private:
+ bool parseJsonFile(); // 解析配置
+
+ 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;
+
+ 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; // 条件变量
};
+
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 57ba1c6..5725af8 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,174 +1,144 @@
+#include "stdafx.h"
#include "MysqlConn.h"
+#include <regex>
-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();
+
+// 初始化连接
+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;
+ }
+ return true;
+}
+
+// 释放资源
+MysqlConn::~MysqlConn() {
+ if (mysql_) {
+ mysql_close(mysql_);
+ mysql_ = nullptr;
+ }
+ freeRes();
+}
+
+// 更新数据
+bool MysqlConn::update(std::string sql) {
+ // 参数化查询优化后的安全检查
+ MYSQL_STMT* stmt = mysql_stmt_init(mysql_);
+ if (!stmt) {
+ return false;
+ }
+ if (mysql_stmt_prepare(stmt, sql.c_str(), sql.length())) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ int paramCount = mysql_stmt_param_count(stmt);
+ if (paramCount > 0) {
+ // 如果有参数，需要进行参数绑定等操作，这里暂不实现
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ if (mysql_stmt_execute(stmt)) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ mysql_stmt_close(stmt);
+ return true;
+}
+
+// 查询数据库
+bool MysqlConn::query(std::string sql) {
+ freeRes();
+ // 参数化查询优化后的安全检查
+ MYSQL_STMT* stmt = mysql_stmt_init(mysql_);
+ if (!stmt) {
+ return false;
+ }
+ if (mysql_stmt_prepare(stmt, sql.c_str(), sql.length())) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ int paramCount = mysql_stmt_param_count(stmt);
+ if (paramCount > 0) {
+ // 如果有参数，需要进行参数绑定等操作，这里暂不实现
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ if (mysql_stmt_execute(stmt)) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ res_ = mysql_stmt_result_metadata(stmt);
+ if (!res_) {
+ mysql_stmt_close(stmt);
+ return false;
+ }
+ mysql_stmt_close(stmt);
+ return true;
+}
+
+// 得到结果集
+bool MysqlConn::getResult() {
+ if (res_) {
+ row_ = mysql_fetch_row(res_);
+ if (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("");
+
+ char* value = row_[index];
+ unsigned long len = mysql_fetch_lengths(res_)[index];
+ return std::string(value, len);
+}
+
+// 事务操作
+bool MysqlConn::transaction() {
+ return mysql_autocommit(mysql_, false);
+}
+
+// 提交事务
+bool MysqlConn::commit() {
+ return mysql_commit(mysql_);
+}
+
+// 事务回滚
+bool MysqlConn::rollback() {
+ return mysql_rollback(mysql_);
+}
+
+void MysqlConn::refreshActiveTime() {
+ activeTime_ = std::chrono::steady_clock::now();
+}
+
+long long MysqlConn::getActiveTime() {
+ // 纳米
+ std::chrono::nanoseconds nased = std::chrono::steady_clock::now() - activeTime_;
+ // 转换成毫米
+ std::chrono::microseconds millsed = std::chrono::duration_cast<std::chrono::microseconds>(nased);
+ return millsed.count(); // 多少毫秒
+}
+
+// 安全校验实现，这里简单使用正则表达式判断是否包含危险字符
+bool MysqlConn::isSqlSafe(const std::string& sql) {
+ std::regex dangerousPattern(".*(['\";\\-+=]).*");
+ return!std::regex_search(sql, dangerousPattern);
+}
+
+void MysqlConn::freeRes() {
+ if (res_) {
+ mysql_free_result(res_);
+ 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 0dbbea9..00c9917 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"
@@ -1,63 +1,49 @@
#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;
+#include <string>
+#include <WinSock2.h>
+//#include <mysql.h>
+#include "MySQL/include/mysql.h"
+#include <string.h>
+#include <chrono>
+
+#pragma comment(lib,"./MySQL/lib/libmysql.lib")
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 connect(std::string ip, std::string userName, std::string passwd, std::string db , int port = 3306);
+ // 释放资源
+ ~MysqlConn( );
+ // 更新数据库 ( insert update delete )
+ bool update(std::string sql);
// 查询数据库
- 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 query(std::string sql);
+ // 得到结果集
+ bool getResult();
+ // 获取结果集的字段
+ std::string getField(int index);
// 事务操作
- bool transaction() const;
+ bool transaction();
// 提交事务
- bool commit() const;
+ bool commit( );
// 事务回滚
- bool rollback() const;
- // 刷新起始的空闲时间点
- void refreashAliveTime();
- // 计算存活总时长
- ll getAliveTime();
+ bool rollback();
+
+ void refreshActiveTime( ); // 刷新活跃时间
+ long long getActiveTime(); // 获取当前活跃的时间间隔
+ // 安全校验接口
+ bool isSqlSafe(const std::string& sql);
+
+private:
+
+ void freeRes();
+ MYSQL* mysql_ = NULL ;
+ MYSQL_RES* res_ = NULL;
+ MYSQL_ROW row_ = nullptr;
+ std::chrono::steady_clock::time_point activeTime_;
};
+
diff --git "a/Server/\347\216\213\347\220\250\345\205\203/log/\346\227\245\345\277\227\346\250\241\346\235\277_\347\216\213\347\220\250\345\205\203_1104.doc" "b/Server/\347\216\213\347\220\250\345\205\203/log/\346\227\245\345\277\227\346\250\241\346\235\277_\347\216\213\347\220\250\345\205\203_1104.doc"
new file mode 100644
index 0000000..8bda98e
--- /dev/null
+++ "b/Server/\347\216\213\347\220\250\345\205\203/log/\346\227\245\345\277\227\346\250\241\346\235\277_\347\216\213\347\220\250\345\205\203_1104.doc"
Binary files differ
diff --git a/kunlun110401.sql b/kunlun.sql
similarity index 83%
rename from kunlun110401.sql
rename to kunlun.sql
index 816d293..aa88b37 100644
--- a/kunlun110401.sql
+++ b/kunlun.sql
@@ -31,24 +31,29 @@
-- 姝ｅ湪瀵煎嚭琛� mayi_kunlun.data_info 鐨勬暟鎹細~0 rows (澶х害)
--- 瀵煎嚭 琛� mayi_kunlun.devices_management 缁撴瀯
-CREATE TABLE IF NOT EXISTS `devices_management` (
- `id` int NOT NULL,
+-- 瀵煎嚭 琛� mayi_kunlun.devices_management_info 缁撴瀯
+CREATE TABLE IF NOT EXISTS `devices_management_info` (
+ `id` int NOT NULL AUTO_INCREMENT,
`devices_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`devices_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`devices_serial_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`devices_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
+ `area` varchar(50) DEFAULT NULL,
`longitude` double DEFAULT NULL,
`latitude` double DEFAULT NULL,
- `purchasing_time` datetime DEFAULT NULL,
- `install_time` datetime DEFAULT NULL,
+ `purchasing_time` varchar(50) DEFAULT NULL,
+ `install_time` varchar(50) DEFAULT NULL,
`manufacturer` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`mark_name` varchar(50) DEFAULT NULL,
- `mark_time` datetime DEFAULT NULL,
+ `mark_time` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--- 姝ｅ湪瀵煎嚭琛� mayi_kunlun.devices_management 鐨勬暟鎹細~0 rows (澶х害)
+-- 姝ｅ湪瀵煎嚭琛� mayi_kunlun.devices_management_info 鐨勬暟鎹細~3 rows (澶х害)
+INSERT INTO `devices_management_info` (`id`, `devices_name`, `devices_type`, `devices_serial_number`, `devices_status`, `area`, `longitude`, `latitude`, `purchasing_time`, `install_time`, `manufacturer`, `mark_name`, `mark_time`) VALUES
+ (2, '12', '3', '2', '2', '3', 4, 6, '6', '3', '3', NULL, NULL),
+ (3, '3', '1', '3', '3', '1', 2, 5, '6', '3', '6', NULL, NULL),
+ (4, '10', '1', '3', '3', '1', 2, 5, '9', '3', '6', NULL, NULL);
-- 瀵煎嚭 琛� mayi_kunlun.month_info 缁撴瀯
CREATE TABLE IF NOT EXISTS `month_info` (
@@ -112,6 +117,16 @@
-- 姝ｅ湪瀵煎嚭琛� mayi_kunlun.role_info 鐨勬暟鎹細~0 rows (澶х害)
+-- 瀵煎嚭 琛� mayi_kunlun.student 缁撴瀯
+CREATE TABLE IF NOT EXISTS `student` (
+ `id` int NOT NULL AUTO_INCREMENT,
+ `name` varchar(50) DEFAULT NULL,
+ `age` int DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
+
+-- 姝ｅ湪瀵煎嚭琛� mayi_kunlun.student 鐨勬暟鎹細~0 rows (澶х害)
+
-- 瀵煎嚭 琛� mayi_kunlun.threshold_info 缁撴瀯
CREATE TABLE IF NOT EXISTS `threshold_info` (
`id` int NOT NULL AUTO_INCREMENT,
@@ -128,19 +143,21 @@
-- 瀵煎嚭 琛� mayi_kunlun.user_info 缁撴瀯
CREATE TABLE IF NOT EXISTS `user_info` (
`id` int NOT NULL AUTO_INCREMENT,
- `department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
- `user_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
+ `department` varchar(50) DEFAULT NULL,
+ `user_name` varchar(32) DEFAULT NULL,
`password` varchar(32) DEFAULT NULL,
- `user_no` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
+ `user_no` varchar(10) DEFAULT NULL,
`age` int DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
- `role_id` int DEFAULT NULL,
`email` varchar(32) DEFAULT NULL,
`telephone` varchar(32) DEFAULT NULL,
`status` int DEFAULT NULL,
+ `role_id` int DEFAULT NULL,
`login_time` datetime DEFAULT NULL,
`register_time` datetime DEFAULT NULL,
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `role_id` (`role_id`),
+ CONSTRAINT `user_info_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `role_info` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 姝ｅ湪瀵煎嚭琛� mayi_kunlun.user_info 鐨勬暟鎹細~0 rows (澶х害)
@@ -155,13 +172,9 @@
`c_filepath` varchar(256) NOT NULL,
`uploader` char(64) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--- 姝ｅ湪瀵煎嚭琛� mayi_kunlun.version_file 鐨勬暟鎹細~3 rows (澶х害)
-INSERT INTO `version_file` (`id`, `version_id`, `filename`, `fliesize`, `s_filepath`, `c_filepath`, `uploader`) VALUES
- (1, '1.3', '1.jpd', 12555, './versoin', './2', ''),
- (2, '1.4', '2.jpd', 1255, './', './2', ''),
- (3, '1.5', '3.jpd', 122, './', './2', '');
+-- 姝ｅ湪瀵煎嚭琛� mayi_kunlun.version_file 鐨勬暟鎹細~0 rows (澶х害)
-- 瀵煎嚭 琛� mayi_kunlun.version_info 缁撴瀯
CREATE TABLE IF NOT EXISTS `version_info` (
@@ -171,9 +184,9 @@
`version_description` char(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`version_creatime` datetime NOT NULL,
PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--- 姝ｅ湪瀵煎嚭琛� mayi_kunlun.version_info 鐨勬暟鎹細~3 rows (澶х害)
+-- 姝ｅ湪瀵煎嚭琛� mayi_kunlun.version_info 鐨勬暟鎹細~2 rows (澶х害)
INSERT INTO `version_info` (`id`, `version_id`, `version_id_old`, `version_description`, `version_creatime`) VALUES
(1, '1.3', '1.3', '225', '2024-10-29 15:47:00'),
(2, '1.4', '1.4', '226', '2024-10-30 02:47:30'),
diff --git "a/~$\350\241\250.docx" "b/~$\350\241\250.docx"
new file mode 100644
index 0000000..8b245e2
--- /dev/null
+++ "b/~$\350\241\250.docx"
Binary files differ
diff --git "a/\350\241\250.docx" "b/\350\241\250.docx"
index 50f24ec..bb4ce96 100644
--- "a/\350\241\250.docx"
+++ "b/\350\241\250.docx"
Binary files differ
--
Gitblit v1.8.0