Chap09-MysqlManager
Chap09-MysqlManager
Mysql C Connector操作
mysql_init()- 初始化连接句柄mysql_real_connect()- 连接到数据库mysql_query()- 执行SQL查询mysql_store_result()- 存储查询结果mysql_fetch_row()- 获取结果行mysql_stmt_init()- 初始化预处理语句mysql_stmt_prepare()- 准备预处理语句mysql_stmt_bind_param()- 绑定参数mysql_stmt_execute()- 执行预处理语句
这些示例涵盖了 MySQL C API 的基本用法，包括连接数据库、执行查询、处理结果和使用预处理语句等重要操作。
预处理的基本流程
mysql_stmt_init -> mysql_stmt_prepare -> mysql_stmt_bind_param -> mysql_stmt_execute -> mysql_stmt_close
// 预处理操作的例子
void prepared_statement_example(MYSQL *conn) {
MYSQL_STMT *stmt;
MYSQL_BIND bind[2];
int user_id;
char name[100];
my_bool is_null[2];
// 准备语句
stmt = mysql_stmt_init(conn);
const char *query = "SELECT id, name FROM users WHERE id > ? AND name LIKE ?";
if (mysql_stmt_prepare(stmt, query, strlen(query))) {
fprintf(stderr, "预处理错误: %s\n", mysql_stmt_error(stmt));
return;
}
// 绑定参数
memset(bind, 0, sizeof(bind));
int min_id = 10;
char search_name[] = "%John%";
// 第一个参数: min_id
bind[0].buffer_type = MYSQL_TYPE_LONG;
bind[0].buffer = (char *)&min_id;
bind[0].is_null = 0;
bind[0].length = 0;
// 第二个参数: search_name
bind[1].buffer_type = MYSQL_TYPE_STRING;
bind[1].buffer = search_name;
bind[1].buffer_length = strlen(search_name);
bind[1].is_null = 0;
bind[1].length = 0;
if (mysql_stmt_bind_param(stmt, bind)) {
fprintf(stderr, "绑定参数错误: %s\n", mysql_stmt_error(stmt));
mysql_stmt_close(stmt);
return;
}
// 执行查询
if (mysql_stmt_execute(stmt)) {
fprintf(stderr, "执行错误: %s\n", mysql_stmt_error(stmt));
mysql_stmt_close(stmt);
return;
}
// 绑定结果
MYSQL_BIND result_bind[2];
memset(result_bind, 0, sizeof(result_bind));
result_bind[0].buffer_type = MYSQL_TYPE_LONG;
result_bind[0].buffer = (char *)&user_id;
result_bind[0].is_null = &is_null[0];
result_bind[1].buffer_type = MYSQL_TYPE_STRING;
result_bind[1].buffer = name;
result_bind[1].buffer_length = sizeof(name);
result_bind[1].is_null = &is_null[1];
mysql_stmt_bind_result(stmt, result_bind);
// 获取结果
printf("查询结果:\n");
while (!mysql_stmt_fetch(stmt)) {
if (!is_null[0] && !is_null[1]) {
printf("ID: %d, 姓名: %s\n", user_id, name);
}
}
mysql_stmt_close(stmt);
}
mysql封装
在这里我们为了解耦，我们将给后端使用的管理类和实际执行操作的类分开，分为Manager和Dao.
首先看myselManager类
// .h
#ifndef MYSQLMANAGER_H
#define MYSQLMANAGER_H
#include "../global/Singleton.h"
#include "MysqlDao.h"
#include <mysql/mysql.h>
struct UserInfo;
class MysqlManager : public Singleton<MysqlManager> {
friend class Singleton<MysqlManager>;
public:
~MysqlManager();
int RegisterUser(const std::string& name, const std::string& email, const std::string& password);
private:
MysqlManager();
private:
MysqlDao _dao;
};
#endif // MYSQLMANAGER_H
// .cpp
#include "MysqlManager.h"
MysqlManager::~MysqlManager()
{
}
MysqlManager::MysqlManager()
{
}
int MysqlManager::RegisterUser(const std::string& name, const std::string& email, const std::string& password)
{
return _dao.RegisterUser(name, email, password);
}
我们可以看到，透露给外部管理类的RegisterUser函数，实际调用了Dao层的函数。
MysqlDao
#ifndef MYSQLDAO_H
#define MYSQLDAO_H
#include <atomic>
#include <condition_variable>
#include <memory>
#include <mysql/mysql.h>
#include <queue>
#include <string>
#include <thread>
/**
┌─────────────────┐ ┌──────────────────┐ ┌─────────────────┐
│ MysqlDao │ │ MySqlPool │ │ UserInfo (DTO) │
│ │ │ │ │ │
│ - pool_ │───────│ - connections │ │ - name │
│ │ │ - pool params │ │ - email │
│ + RegUser() │ │ + getConnection()│ │ - password │
│ + other CRUD │ │ + returnConn() │ │ │
└─────────────────┘ └──────────────────┘ └─────────────────┘
│ │
│ │
▼ ▼
┌─────────────────┐ ┌──────────────────┐
│ Business Logic │ │ mysql::Connection│
│ (Service层) │ │ (MySQL驱动) │
└─────────────────┘ └──────────────────┘
*
*/
class MysqlPool {
public:
struct conn_deleter {
void operator()(MYSQL* conn) const noexcept
{
mysql_close(conn);
mysql_thread_end();
}
};
MysqlPool(const std::string& url, const std::string& user, const std::string& password, const std::string& schedma, int poolSize = std::thread::hardware_concurrency());
std::unique_ptr<MYSQL, MysqlPool::conn_deleter> GetConnection() noexcept;
void ReturnConnection(std::unique_ptr<MYSQL, conn_deleter> conn) noexcept;
void Close() noexcept;
~MysqlPool();
private:
std::string _url;
std::string _user;
std::string _password;
std::string _schedma;
std::size_t _poolSize;
std::queue<std::unique_ptr<MYSQL, conn_deleter>> _connections;
std::mutex _mutex;
std::condition_variable _cv;
std::atomic<bool> _stop;
};
class MysqlDao {
public:
MysqlDao();
~MysqlDao();
int RegisterUser(const std::string& name, const std::string& email, const std::string& password);
private:
std::unique_ptr<MysqlPool> _pool;
};
#endif
实际执行操作的时候，都需要一个MYSQL*的连接实例进行操作。
为了调高数据库访问的并发行，我们建立了MysqlPool,每次执行操作，取出一个MYSQL*进行接下来的步骤。
以下是MysqlPool：
#include "MysqlDao.h"
#include "../global/ConfigManager.h"
#include <iostream>
MysqlPool::MysqlPool(const std::string& url, const std::string& user, const std::string& password, const std::string& schedma, int poolSize)
: _url(url)
, _user(user)
, _password(password)
, _schedma(schedma)
, _poolSize(poolSize)
, _stop(false)
{
int successCount = 0;
try {
for (std::size_t i = 0; i < _poolSize; ++i) {
MYSQL* conn = mysql_init(nullptr);
if (conn == nullptr) {
throw std::runtime_error("mysql_init failed");
}
if (mysql_real_connect(conn, _url.c_str(), _user.c_str(), _password.c_str(), _schedma.c_str(), 0, NULL, 0) == nullptr) {
throw std::runtime_error("mysql_real_connect failed");
}
if (mysql_ping(conn) != 0) {
std::string error = "mysql_ping failed: ";
error += mysql_error(conn);
mysql_close(conn);
throw std::runtime_error(error);
}
std::unique_ptr<MYSQL, conn_deleter> conn_ptr(conn, conn_deleter());
_connections.push(std::move(conn_ptr));
successCount++;
}
} catch (std::exception& e) {
while (!_connections.empty()) {
auto conn = std::move(_connections.front());
_connections.pop();
if (conn) {
mysql_close(conn.get());
}
}
std::cerr << e.what() << std::endl;
}
if (successCount < _poolSize) {
std::cerr << "Warning Only" << successCount << "Out Of" << _poolSize << " Connections Were Established" << std::endl;
}
}
MysqlPool::~MysqlPool()
{
Close();
}
std::unique_ptr<MYSQL, MysqlPool::conn_deleter> MysqlPool::GetConnection() noexcept
{
std::unique_lock<std::mutex> lock(_mutex);
_cv.wait(lock, [this] {
return _stop || !_connections.empty();
});
if (_stop) {
return nullptr;
}
auto conn = std::move(_connections.front());
_connections.pop();
return conn;
}
void MysqlPool::ReturnConnection(std::unique_ptr<MYSQL, conn_deleter> conn) noexcept
{
std::unique_lock<std::mutex> lock(_mutex);
if (_stop) {
return;
}
_connections.push(std::move(conn));
_cv.notify_one();
}
void MysqlPool::Close() noexcept
{
std::unique_lock<std::mutex> lock(_mutex);
_stop = true;
_cv.notify_all();
while (!_connections.empty()) {
auto p = std::move(_connections.front());
_connections.pop();
}
}
每次初始化池子，就要创建并且初始化_size个MYSQL*，并且连接上对应的数据库。Dao需要操作的时候，调用MysqlPool::GetConnection()得到一个MYSQL*然后执行操作。为了池子的长久运行，每当操作执行完毕之后，调用MysqlPool::ReturnConnection将得到的MYSQL*返回给池子。
接下来看具体的Dao层
MysqlDao::MysqlDao()
{
auto& cfgMgr = ConfigManager::GetInstance();
const auto& host = cfgMgr["Mysql"]["host"];
const auto& port = cfgMgr["Mysql"]["port"];
const auto& schema = cfgMgr["Mysql"]["schema"];
const auto& password = cfgMgr["Mysql"]["password"];
const auto& user = cfgMgr["Mysql"]["user"];
_pool = std::make_unique<MysqlPool>(host, user, password, schema);
}
MysqlDao::~MysqlDao()
{
_pool->Close();
}
int MysqlDao::RegisterUser(const std::string& name, const std::string& email, const std::string& password)
{
auto conn = _pool->GetConnection();
try {
if (conn == nullptr) {
_pool->ReturnConnection(std::move(conn));
return false;
}
MYSQL_STMT* stmt = mysql_stmt_init(conn.get());
std::string query = "select * from user where name = ? or email =?";
if (mysql_stmt_prepare(stmt, query.c_str(), query.size()) != 0) {
std::cout << mysql_error(conn.get()) << std::endl;
mysql_stmt_close(stmt);
_pool->ReturnConnection(std::move(conn));
return -1;
}
MYSQL_BIND params[2];
memset(params, 0, sizeof(params));
params[0].buffer_type = MYSQL_TYPE_STRING;
params[0].buffer = (char*)name.c_str();
params[0].buffer_length = name.size();
params[0].length = ¶ms[0].buffer_length;
params[1].buffer_type = MYSQL_TYPE_STRING;
params[1].buffer = (char*)email.c_str();
params[1].buffer_length = email.size();
params[1].length = ¶ms[1].buffer_length;
if (mysql_stmt_bind_param(stmt, params) != 0) {
std::cout << mysql_stmt_error(stmt) << std::endl;
mysql_stmt_close(stmt);
_pool->ReturnConnection(std::move(conn));
return -1;
}
if (mysql_stmt_execute(stmt) != 0) {
mysql_stmt_close(stmt);
_pool->ReturnConnection(std::move(conn));
return -1;
}
mysql_stmt_close(stmt);
MYSQL_RES* result = mysql_store_result(conn.get());
if (result == nullptr) {
_pool->ReturnConnection(std::move(conn));
return -1;
}
my_ulonglong row_count = mysql_stmt_num_rows(stmt);
mysql_stmt_close(stmt);
// 如果存在记录，返回 -1 表示用户已存在
if (row_count > 0) {
_pool->ReturnConnection(std::move(conn));
return -1;
}
stmt = mysql_stmt_init(conn.get());
query = "INSERT INTO user (name, email, password) VALUES (?, ?, ?)";
if (mysql_stmt_prepare(stmt, query.c_str(), query.size()) != 0) {
std::cout << "Insert prepare failed: " << mysql_error(conn.get()) << std::endl;
mysql_stmt_close(stmt);
_pool->ReturnConnection(std::move(conn));
return -1;
}
// 绑定插入参数
MYSQL_BIND insert_params[3];
memset(insert_params, 0, sizeof(insert_params));
unsigned long name_len = name.size();
insert_params[0].buffer_type = MYSQL_TYPE_STRING;
insert_params[0].buffer = (char*)name.c_str();
insert_params[0].buffer_length = name_len;
insert_params[0].length = &name_len;
unsigned long email_len = email.size();
insert_params[1].buffer_type = MYSQL_TYPE_STRING;
insert_params[1].buffer = (char*)email.c_str();
insert_params[1].buffer_length = email_len;
insert_params[1].length = &email_len;
unsigned long password_len = password.size();
insert_params[2].buffer_type = MYSQL_TYPE_STRING;
insert_params[2].buffer = (char*)password.c_str();
insert_params[2].buffer_length = password_len;
insert_params[2].length = &password_len;
if (mysql_stmt_bind_param(stmt, insert_params) != 0) {
std::cout << "Insert bind failed: " << mysql_stmt_error(stmt) << std::endl;
mysql_stmt_close(stmt);
_pool->ReturnConnection(std::move(conn));
return -1;
}
if (mysql_stmt_execute(stmt) != 0) {
std::cout << "Insert execute failed: " << mysql_stmt_error(stmt) << std::endl;
mysql_stmt_close(stmt);
_pool->ReturnConnection(std::move(conn));
return -1;
}
mysql_stmt_close(stmt);
_pool->ReturnConnection(std::move(conn));
return 1; // 返回1表示注册成功
} catch (const std::exception& e) {
if (conn != nullptr) {
_pool->ReturnConnection(std::move(conn));
}
std::cerr << "Exception: " << e.what() << std::endl;
return -1;
}
}
创建时，先从配置文件读取对应的信息，初始化池子信息，连接数据库。之后就是等待MysqlManager的调用。
比如其中的RegisterUser函数，实际执行了select * from user where name = ? or email = ? ;用于检测当前信息的用户是否已经注册，如果结果不为0
my_ulonglong row_count = mysql_stmt_num_rows(stmt);
mysql_stmt_close(stmt);
// 如果存在记录，返回 -1 表示用户已存在
if (row_count > 0) {
_pool->ReturnConnection(std::move(conn));
return -1;
}
那么返回-1,错误。
如果结果为0,说明当前还未注册这个用户，接下来我们就执行插入语句，将信息插入数据库，实现真正的注册( INSERT INTO user (name, email, password) VALUES (?, ?, ?) )。
浙公网安备 33010602011771号