目录
- 一：关键接口说明
- 二：具体实现
- 三：接口测试
- 四：后续
一：关键接口说明
一般预处理流程都是，连接数据库–>预处理–>绑定查询参数–>执行查询–>获取查询结果。
故api封装之后有如下几个关键的接口：
1：bool connect(); //连接数据库，初始化数据库连接等
2：bool prepare(const std::string &query);//预处理
3：template<typename T> void bindValue(int pos, const T &value);//给placeholder相对应的位置绑定参数
4：bool execute();//执行数据库查询
5：bool next();//移到下一条记录
6：template<typename T>T value(int index);//获取结果集index位置中T类型的结果
二：具体实现
1：首先需要封装数据库连接的参数配置
DatabaseOption.hpp
#include <string>
struct DatabaseOption {
std::string host = "127.0.0.1";//数据库ip地址
int port = 3307;//端口号
std::string username = "root";//用户名
std::string password = "123456";//数据库密码
std::string databaseName = "test";//数据库名
int connectTimeout = 0;//连接的超时时间
std::string charsetName = "utf8";//编码
int reconnectTime = 1;//超时重连次数
};
2：然后需要封装预处理的参数绑定
PrepareBinder.hpp
#include <vector>
#include <string>
#include <algorithm>
#include <mysql.h>
#include <iostream>
class PrepareBinder {
private:
std::vector<MYSQL_BIND> paramBinds;//预处理的参数绑定
public:
explicit PrepareBinder(int prepareBindNum) {
paramBinds.resize(prepareBindNum);//初始化绑定参数列表
}
std::vector<MYSQL_BIND> &getParamBinds() {
return paramBinds;
}
void bindValue(int pos, const int &value) {
paramBinds[pos].buffer_type = MYSQL_TYPE_LONG;
paramBinds[pos].buffer = (void *) &value;
paramBinds[pos].is_unsigned = false;
}
void bindValue(int pos, const unsigned int &value) {
paramBinds[pos].buffer_type = MYSQL_TYPE_LONG;
paramBinds[pos].buffer = (void *) &value;
paramBinds[pos].is_unsigned = true;
}
void bindValue(int pos, const std::string &value) {
paramBinds[pos].buffer_type = MYSQL_TYPE_STRING;
paramBinds[pos].buffer = (void *) value.c_str();
paramBinds[pos].buffer_length = value.size();
}
};
3：再有需要对封装查询结果集进行封装
ResultBinder.hpp
#include <vector>
#include <map>
#include <mysql.h>
#include <functional>
#include <iostream>
class ResultBinder {
private:
template<typename T>
struct IndexWrapper {
int index = 0;//索引
explicit IndexWrapper(int index) : index(index) {}
};
private:
const static int STRING_MAX_LENGTH = 65535;
std::vector<MYSQL_BIND> resultBinds;
//字符串缓存
std::vector<std::vector<char>> stringBuffer;
//整型的缓存
std::vector<int> intBuffer;
//类型与处理函数映射
std::map<int, std::function<void(int)>> typeProcessMap = {
{MYSQL_TYPE_LONG, std::bind(&ResultBinder::bindInt, this, std::placeholders::_1)},
{MYSQL_TYPE_VAR_STRING, std::bind(&ResultBinder::bindString, this, std::placeholders::_1)}
};
private:
void bindInt(int index) {
resultBinds[index].buffer_type = MYSQL_TYPE_LONG;
resultBinds[index].buffer = &intBuffer[index];
}
void bindString(int index) {
stringBuffer[index].clear();
stringBuffer[index].resize(STRING_MAX_LENGTH);
resultBinds[index].buffer_type = MYSQL_TYPE_STRING;
resultBinds[index].buffer = stringBuffer[index].data();
resultBinds[index].buffer_length = STRING_MAX_LENGTH;
}
std::string value(IndexWrapper<std::string> wrapper) {
return stringBuffer[wrapper.index].data();
}
int value(IndexWrapper<int> wrapper) {
return intBuffer[wrapper.index];
}
public:
explicit ResultBinder(int resultBindNum) {
resultBinds.resize(resultBindNum);
stringBuffer.resize(resultBindNum);
intBuffer.resize(resultBindNum);
}
void bindValue(enum_field_types fieldTypes, int index) {
if (typeProcessMap.find(fieldTypes) == typeProcessMap.end()) {
std::cerr << "[unsupported type]" << fieldTypes << std::endl;
return;
}
//调用绑定函数
typeProcessMap[fieldTypes](index);
}
template<typename T>
T value(int index) {
return value(std::move(IndexWrapper<T>(index)));
}
std::vector<MYSQL_BIND> &getBindResult() {
return resultBinds;
}
};
4：最后是对mysql查询接口的封装
Connection.hpp
#include "DatabaseOption.hpp"
#include "PrepareBinder.hpp"
#include "ResultBinder.hpp"
#include <mysql.h>
#include <memory>
#include <vector>
#include <iostream>
#include <list>
class Connection {
public:
void setOption(const DatabaseOption &option) {
this->option = option;
}
void close() {
if (connection != nullptr) {
mysql_close(connection);
connection = nullptr;
}
if (!mysqlStmt)
mysql_stmt_free_result(mysqlStmt);
}
bool ping() {
return mysql_ping(connection) == 0;
}
int getLastAffectedRows() {
return (int) mysql_affected_rows(connection);
}
bool connect() {
//初始化mysql
connection = mysql_init(nullptr);
if (nullptr == connection) {
setLastError("mysql init failed");
return false;
}
//设置超时时间
if (option.connectTimeout > 0) {
if (0 != mysql_options(connection, MYSQL_OPT_CONNECT_TIMEOUT, &option.connectTimeout)) {
setLastError("set option error");
return false;
}
}
//编码
mysql_options(connection, MYSQL_SET_CHARSET_NAME, option.charsetName.c_str());
mysql_options(connection, MYSQL_OPT_RECONNECT, &option.reconnectTime);
if (!mysql_real_connect(connection, option.host.c_str(), option.username.c_str(), option.password.c_str(),
option.databaseName.c_str(), option.port, nullptr, 0)) {
setLastError("failed to connect to database");
return false;
}
return true;
}
void setLastError(const std::string &lastError) {
this->lastError = "[" + lastError + "] " + mysql_error(connection);
std::cerr << this->lastError << std::endl;
}
std::string getLastError() const {
return this->lastError;
}
bool begin() {
if (mysql_query(connection, "BEGIN")) {
setLastError("begin error");
return false;
}
return true;
}
bool commit() {
if (mysql_query(connection, "COMMIT")) {
setLastError("commit error");
return false;
}
return true;
}
bool rollback() {
if (mysql_query(connection, "ROLLBACK")) {
setLastError("rollback error");
return false;
}
return true;
}
bool execute() {
//绑定查询参数
if (mysql_stmt_bind_param(mysqlStmt, &prepareBinder->getParamBinds()[0])) {
setLastError("mysql_stmt_bind_param error");
return false;
}
//获取结果集元数据
mysqlRes = mysql_stmt_result_metadata(mysqlStmt);
auto columnNum = mysql_num_fields(mysqlRes);
resultBinder = std::make_shared<ResultBinder>(columnNum);
//根据表元数据绑定结果集
for (int i = 0; i < mysqlRes->field_count; i++) {
resultBinder->bindValue(mysqlRes->fields[i].type, i);
// std::cout << "name:" << mysqlRes->fields[i].name << " type: " << mysqlRes->fields[i].type << std::endl;
}
//绑定查询结果
if (mysql_stmt_bind_result(mysqlStmt, &resultBinder->getBindResult()[0])) {
setLastError("mysql_stmt_bind_result");
return false;
}
if (mysql_stmt_execute(mysqlStmt)) {
setLastError("mysql_stmt_execute error");
return false;
}
return true;
}
template<typename T>
void bindValue(int pos, const T &value) {
prepareBinder->bindValue(pos, value);
}
bool prepare(const std::string &query) {
//初始化预处理环境
mysqlStmt = mysql_stmt_init(connection);
if (!mysqlStmt) {
setLastError("mysql_stmt_init error");
return false;
}
if (mysql_stmt_prepare(mysqlStmt, query.c_str(), query.size())) {
setLastError("mysql_stmt_prepare error");
return false;
}
//获取预处理绑定的参数个数
auto paramCount = mysql_stmt_param_count(mysqlStmt);
prepareBinder = std::make_shared<PrepareBinder>(paramCount);
return true;
}
bool next() {
//0表示正确,其他情况返回错误
return mysql_stmt_fetch(mysqlStmt) == 0;
}
template<typename T>
T value(int index) {
return resultBinder->value<T>(index);
}
private:
MYSQL *connection = nullptr;
MYSQL_STMT *mysqlStmt = nullptr;
MYSQL_RES *mysqlRes = nullptr;//结果
std::string lastError;//最后的错误信息
DatabaseOption option;//数据库配置信息
std::shared_ptr<PrepareBinder> prepareBinder = nullptr;
std::shared_ptr<ResultBinder> resultBinder = nullptr;
};
三：接口测试
1：数据库的准备
mysql> create database test;
mysql> use test;
mysql> select * from user;
mysql> insert into user(id,name)values(1,"zhangsan"),(2,"lisi"),(3,"wangwu"),(4,"liwu");
mysql> select * from user;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
| 4 | liwu |
+------+----------+
2：测试代码
main.cpp
#include <Connection.hpp>
int main() {
Connection connection;
connection.setOption(DatabaseOption());//初始化配置
connection.connect();//连接数据库
std::string sql = "select name,id from user where `id` > ? and `name` like ?";
connection.prepare(sql);//预处理
connection.bindValue(0, 1);//绑定查询参数
connection.bindValue(1, "%wu");
connection.execute();//执行查询
while (connection.next()) {
//获取结果
std::cout << "id = " << connection.value<int>(1);
std::cout << " name = " << connection.value<std::string>(0) << std::endl;
}
return 0;
}
输出结果如下
id = 3 name = wangwu
id = 4 name = liwu
四：后续
后续加入数据库连接池
