1.封装的目的
mysql提供了较多的api,但是这些api的功能较为单一,而我们自己在使用的时候,经常为了实现一个简单的想法,但是要调用多个函数去实现我们的功能,我们可以根据自己的需求,重新封装一下mysql,这样便于我们项目的开发和提升代码的可读性。
这里的代码是在前面博客的基础上开发的,如果比较熟悉mysql和proto的话,可以只看XMysql的实现。如果不熟悉的话,建议可以阅读博主前面几篇博客。
2.封装一个XMysql类
①这个类可以保证基本的增删改查需求。
②保证执行时mysql的服务连接。
③简化初始化连接过程。
④可以返回protobuf格式的操作结果。
⑤可以返回普通的字符串格式的操作结果。
XMysql.h的代码
#ifndef X_MYSQL
#define X_MYSQL
#include "mysqlToProtobuf.h"
#include "/usr/local/mysql/include/mysql.h"
#include <iostream>
#include <vector>
struct XMysqlRes
{
bool suc = false;
std::vector<std::vector<std::string>> res;
};
class XMysql
{
public:
//初始化数据库
bool init(const char* host, const char* user, const char* pwd, const char* db, const uint32_t timeout = 2);
//有返回的操作
XMysqlRes exeSqlStore(const char* sql);
//无返回的操作
bool exeSql(const char* sql);
//定义一个模板,针对不同的protobuf结构
template<typename PROTO_TYPE>
//这里必须再类内实现,在类外实现的话,进行外部链接的时候会出错
bool exeSqlProtobuf(const char* sql, std::vector<std::shared_ptr<PROTO_TYPE>>& protoRes)
{
if (!exeSql(sql)) {
std::cout << "XMysql exeSqlStore error! sql=" << sql << std::endl;
return false;
}
//转成protobuf形式
mysqlToProtobuf(mysql_store_result(&m_mysql), protoRes);
return true;
}
//校验连接状态
void check();
//连接数据库
bool connect();
private:
MYSQL m_mysql;
std::string m_host = "";
std::string m_user = "";
std::string m_pwd = "";
std::string m_db = "";
uint32_t m_timeout = 0;
};
#endif
XMysql.cpp
#include "XMysql.h"
bool XMysql::init(const char* host, const char* user, const char* pwd, const char* db, const uint32_t timeout)
{
m_host = host;
m_user = user;
m_pwd = pwd;
m_db = db;
m_timeout = timeout;
if (connect()) {
std::cout << "XMysql connect error" << std::endl;
return false;
}
return true;
}
bool XMysql::exeSql(const char* sql)
{
if (mysql_real_query(&m_mysql,sql,strlen(sql)) != 0) {
std::cout << "XMysql exeSql error! sql=" << sql << std::endl;
return false;
}
return true;
}
XMysqlRes XMysql::exeSqlStore(const char* sql)
{
XMysqlRes res;
check();
if (!exeSql(sql)) {
std::cout << "XMysql exeSqlStore error! sql=" << sql << std::endl;
return res;
}
//执行成功
res.suc = true;
//查询结果
auto result = mysql_store_result(&m_mysql);
//行数
auto rowNum = mysql_num_rows(result);
//列数
auto fiedNum = mysql_num_fields(result);
for (int i = 0; i < rowNum; i++) {
auto row = mysql_fetch_row(result);
std::vector<std::string> rows;
for (int j = 0; j < fiedNum; j++) {
rows.push_back(row[j]);
}
res.res.push_back(rows);
}
return res;
}
void XMysql::check()
{
//ping返回0时表示正常
if (mysql_ping(&m_mysql) != 0) {
std::cout << "XMysql ping == 0" << std::endl;
//关闭mysql
mysql_close(&m_mysql);
return;
}
//重新连接
connect();
}
bool XMysql::connect()
{
//初始化
if (!mysql_init(&m_mysql)) {
std::cout << "XMysql init error!" << std::endl;
return false;
}
//连接数据库
if (!mysql_real_connect(&m_mysql, m_host.c_str(), m_user.c_str(), m_pwd.c_str(), m_db.c_str(), 0, NULL, CLIENT_MULTI_STATEMENTS)) {
std::cout << "XMysql connect error!" << std::endl;
return false;
}
//设置参数
mysql_options(&m_mysql, MYSQL_OPT_READ_TIMEOUT, &m_timeout);
mysql_options(&m_mysql, MYSQL_SET_CHARSET_NAME, "utf8");
}
mysqlToProtobuf.h
#ifndef FIRE_PROCESS_PROCESS_MYSQLER_MYSQL_PROTOBUF_H
#define FIRE_PROCESS_PROCESS_MYSQLER_MYSQL_PROTOBUF_H
#include "/usr/local/mysql/include/mysql.h"
#include <google/protobuf/stubs/common.h>
#include <google/protobuf/arena.h>
#include <google/protobuf/arenastring.h>
#include <google/protobuf/generated_message_util.h>
#include <google/protobuf/metadata.h>
#include <google/protobuf/message.h>
#include <google/protobuf/repeated_field.h>
#include <google/protobuf/extension_set.h>
#include <google/protobuf/unknown_field_set.h>
#include <vector>
#include <memory>
#include <iostream>
/*
把mysql查询出来的结果MYSQL_RES* 根据定义的protobuf去解析,
这样可以把结果转换成protobuf的格式,可以省去解析的流程。
*/
// 表行数据转为对应protobuf
template<typename PROTO_TYPE>
bool mysqlToProtobuf(MYSQL_RES* res, std::vector<std::shared_ptr<PROTO_TYPE>>& msgVec)
{
//查询失败
if (!res) {
std::cout << "xmysql res == nullptr" << std::endl;
return false;
}
//sql查询结果行数
int rowNum = mysql_num_rows(res);
//sql查询结果字段数量
int fcount = mysql_num_fields(res);
for (int i = 0; i < rowNum; ++i) {
MYSQL_ROW row = mysql_fetch_row(res);
if (!row) {
std::cout << "xmysql fethRow = nullptr" << std::endl;
return false;
}
//申请空间保存结果
auto message = std::make_shared<PROTO_TYPE>();
const google::protobuf::Reflection* reflection = message->GetReflection();
const google::protobuf::Descriptor* descriptor = message->GetDescriptor();
if (!reflection || !descriptor) {
return false;
}
//判断是否匹配
if (fcount != descriptor->field_count()) {
std::cout << "mysql_field != proto_field" << std::endl;
return false;
}
//遍历proto所有的字段
for (int i = 0; i < descriptor->field_count(); ++i) {
//得到对应proto的描述
const google::protobuf::FieldDescriptor* desField = descriptor->field(i);
// 剔除repeated类型
if (desField->label() == google::protobuf::FieldDescriptor::LABEL_REPEATED) {
return false;
}
//根据类型进行string转换
switch (desField->type()) {
// int32_t
case google::protobuf::FieldDescriptor::TYPE_INT32:
{
reflection->SetInt32(message.get(), desField, std::stoi(row[i]));
break;
}
// int64_t
case google::protobuf::FieldDescriptor::TYPE_INT64:
{
reflection->SetInt64(message.get(), desField, std::stoll(row[i]));
break;
}
// uint32_t
case google::protobuf::FieldDescriptor::TYPE_UINT32:
case google::protobuf::FieldDescriptor::TYPE_FIXED32:
{
reflection->SetUInt32(message.get(), desField, std::stoul(row[i]));
break;
}
// int64_t
case google::protobuf::FieldDescriptor::TYPE_UINT64:
case google::protobuf::FieldDescriptor::TYPE_FIXED64:
{
reflection->SetUInt64(message.get(), desField, std::stoull(row[i]));
break;
}
//string bytes
case google::protobuf::FieldDescriptor::TYPE_STRING:
{
reflection->SetString(message.get(), desField, row[i]);
break;
}
case google::protobuf::FieldDescriptor::TYPE_BYTES:
{
//获取结果的长度
auto len = mysql_fetch_lengths(res);
reflection->SetString(message.get(), desField, std::string(row[i], len[i]));
break;
}
// double
case google::protobuf::FieldDescriptor::TYPE_DOUBLE:
{
reflection->SetDouble(message.get(), desField, std::stod(row[i]));
break;
}
// bytes
case google::protobuf::FieldDescriptor::TYPE_FLOAT:
{
reflection->SetFloat(message.get(), desField, std::stof(row[i]));
break;
}
// bool
case google::protobuf::FieldDescriptor::TYPE_BOOL:
{
reflection->SetBool(message.get(), desField, std::stoi(row[i]));
break;
}
//暂时只写比较基础的类型,后面的再丰富
default:
{
std::cout << "undeal mysql type " << desField->type() << "\n";
}
}
}
msgVec.push_back(message);
}
return true;
}
#endif
main.cpp
#include <iostream>
#include "../proto/src/User.pb.h"
#include "XConfig.h"
#include "XMysql.h"
int main(int argc, char** argv)
{
//加载配置
XConfig config;
if (!config.load(argv[1])) {
std::cout << "load config error !" << std::endl;
return -1;
}
//调用配置
std::string host = config.getConfigValue("mysql", "host");
std::string user = config.getConfigValue("mysql", "user");
std::string pwd = config.getConfigValue("mysql", "pwd");
std::string db = config.getConfigValue("mysql", "db");
//试试封装好的类
XMysql mysql;
if (!mysql.init(host.c_str(), user.c_str(), pwd.c_str(), db.c_str())) {
std::cout << "init mysql error!" << std::endl;
return -1;
}
User user1;
user1.set_id(200);
user1.set_nickname("窑下村吴彦祖");
std::string str = "";
user1.SerializeToString(&str);
//测试无返回类型的
char sql[128];
snprintf(sql, sizeof(sql), "insert into user_data(user_id, user_data) value (%d, \'%s\');", user1.id(), str.c_str());
if (!mysql.exeSql(sql)) {
return -1;
}
//测试返回普通结果
char sql2[] = "select * from user_data;";
auto res = mysql.exeSqlStore(sql2);
if (res.suc == false) {
return -1;
}
User user3;
for (auto& rows : res.res) {
for (auto& field : rows) {
std::cout << field <<" ";
}
std::cout << std::endl;
}
//测试返回protobuf
std::vector<std::shared_ptr<User>> protoRes;
if (!mysql.exeSqlProtobuf(sql2, protoRes)) {
return -1;
}
for (auto user : protoRes) {
user3.ParseFromString(user->nickname());
std::cout << "user_id = " << user3.id() << "nickname =" << user3.nickname() << std::endl;
}
return 0;
}
Makefile
cc=g++
cc_flags=-std=c++11 \
-I/usr/local/mysql/include \
ln_flags=-L/usr/local/lib -lprotobuf \
-L/usr/local/mysql/lib -lmysqlclient \
-lpthread \
-lm \
-ldl \
obj=main.o \
XConfig.o \
XMysql.o \
../proto/src/User.pb.o \
target=process
$(target) : $(obj)
$(cc) $(ln_flags) $(obj) -o $(target)
%.o : %.cpp
$(cc) $(cc_flags) -c $< -o $@
%.o: %.cc
$(cc) $(cc_flags) -c $< -o $@
clean:
rm -f $(obj) $(target)
3.执行结果
4.小结
这里在处理返回结果的时候,用了两种方法,一种是把MYSQL_RES*映射成protobuf的message指针,另一种是把结果保存到struct XMysqlRes中,这两种方案都可以节省很多代码量,其中,protobuf和mysql的映射可以多花一点时间看看。