MySQL是一个比较好用并且免费的数据库,实际开发中可能会用到。如果想用C++调用MySQL,需要调用MySQL提供的一系列API。但是,这些API是基于C语言编写的,没有面向对象的特征,特别是查询获取结果集,用起来可能不太方便。而在Java中访问数据库,无论是查询还是其他操作,都被高度封装,非常方便。为了弥补这一缺陷,我最近利用课余时间封装了一下MySQL常用的API,下面就来介绍一下。
程序框架
程序主要分为DataBase,QueryResult,QueryRow和一些异常类,主要封装了查询的功能,因为查询获取结果集是最麻烦的,而其它操作直接执行就可以。其中DataBase的功能是连接数据库并执行操作,QueryResult是查询的结果集,QueryRow是结果集的某一行。
更新记录
2022.11.3:删除QueryFields类,把之前通过QueryRow维护QueryFields的模式改为强制绑定QueryRow和QueryResult;把Execute与Query合并,返回值改用std::optional。
2022.9.4:增加字符串转义功能及其相关的异常类。
使用示例
#include<iostream>
#include"MySQLLibrary.hpp"
using namespace std;
using namespace MyStd::MySQL;
void ShowQueryResult(const QueryResult& result)
{
for (const string& name : result.GetFields())
{
cout.width(20);
cout << name;
}
cout << endl;
for (const QueryRow& row : result.GetRows())
{
for (const string& name : row.GetData())
{
cout.width(20);
cout << name;
}
cout << endl;
}
}
int main()
{
try
{
DataBase db("root", "424586", "mirai", "gbk");
cout << db.GetDataBaseName() << endl;
ShowQueryResult(db.Execute("select*from group473405258").value());
db.Execute("use information_schema");
cout << db.GetDataBaseName() << endl;
ShowQueryResult(db.Execute("show tables").value());
db.Execute("use mirai");//没有返回数据
}
catch (const MySQLException& e)
{
cout << e.what();
}
return 0;
}
运行结果(程序中出现的数据库和表均存在):
可以看出,程序满足需求。
怎么样,是不是有点Java的味了?
程序代码
MySQLLibrary.hpp:
#pragma once
#include<Windows.h>
#include<mysql.h>
#include<string>
#include<stdexcept>
#include<ios>
#include<vector>
#include<format>
#include<optional>
namespace MyStd
{
bool IgnoreCaseCompare(const std::string& a, const std::string& b)noexcept
{
if (a.size() != b.size())
return false;
for (size_t i = 0; i < a.size(); i++)
{
if (tolower(a[i]) != tolower(b[i]))
return false;
}
return true;
};
namespace MySQL
{
class MySQLException :public std::ios_base::failure
{
public:
MySQLException(const std::string& message);
};
class MySQLConnectException :public MySQLException
{
public:
MySQLConnectException(const std::string& message);
};
class MySQLExecuteException :public MySQLException
{
public:
MySQLExecuteException(const std::string& message);
};
class MySQLFieldNotFoundException :public MySQLException
{
public:
MySQLFieldNotFoundException(const std::string& message);
};
class MySQLEscapeException :public MySQLException
{
public:
MySQLEscapeException(const std::string& message);
};
class QueryResult;
class QueryRow
{
public:
QueryRow(const QueryResult& result, MYSQL_ROW row);
QueryRow(const QueryResult& newres,QueryRow&& row);
const std::vector<std::string>& GetData()const noexcept;
std::string operator[](const std::string& index)const;
private:
std::vector<std::string> data;
const QueryResult& res;
};
class QueryResult
{
public:
QueryResult(MYSQL_RES&& result);
QueryResult(QueryResult&& result);
const std::vector<QueryRow>& GetRows()const noexcept;
const std::vector<std::string>& GetFields()const noexcept;
bool IsEmpty()const noexcept;
~QueryResult();
private:
std::vector<QueryRow> rows;
std::vector<std::string> fields;
};
class DataBase
{
public:
DataBase(const std::string& user_name, const std::string& password, const std::string& database, const std::string& characters, const std::string& host = "localhost", const unsigned int port = 3306, const char* unix_socket = nullptr, const unsigned long client_flag = 0);
std::optional<QueryResult> Execute(const std::string& str);
std::string GetDataBaseName()const noexcept;
std::string EscapeString(const std::string& str);
~DataBase();
private:
MYSQL db;
};
DataBase::DataBase(const std::string& user_name, const std::string& password, const std::string& database, const std::string& characters, const std::string& host, const unsigned int port, const char* unix_socket, const unsigned long client_flag)
{
mysql_init(&db);
if (!mysql_real_connect(&db, host.data(), user_name.data(), password.data(), database.data(), port, unix_socket, client_flag))
throw MySQLConnectException(mysql_error(&db));
//设置访问编码
mysql_set_character_set(&db, characters.data());
}
std::optional<QueryResult> DataBase::Execute(const std::string& str)
{
if (mysql_real_query(&db, str.data(), str.size()))
throw MySQLExecuteException(mysql_error(&db));
MYSQL_RES* result = mysql_store_result(&db);
if (result)
{
return QueryResult(std::move(*result));
}
if (mysql_field_count(&db) == 0)//无返回数据,不是查询语句
{
return std::nullopt;
}
throw MySQLExecuteException(mysql_error(&db));
}
std::string DataBase::GetDataBaseName() const noexcept
{
return db.db;
}
std::string DataBase::EscapeString(const std::string& str)
{
char* temp = new char[str.size() * 2 + 1];
const unsigned long ret = mysql_real_escape_string(&db, temp, str.data(), str.size());
if (ret == -1)
throw MySQLEscapeException("格式化出现错误!");
return std::string(temp, ret);
}
DataBase::~DataBase()
{
mysql_close(&db);
}
MySQLException::MySQLException(const std::string& message) : std::ios::failure(message)
{
}
MySQLConnectException::MySQLConnectException(const std::string& message) : MySQLException(message)
{
}
MySQLExecuteException::MySQLExecuteException(const std::string& message) : MySQLException(message)
{
}
QueryResult::QueryResult(MYSQL_RES&& result)
{
//处理列
MYSQL_FIELD* fs = mysql_fetch_fields(&result);
const unsigned int field_count = mysql_num_fields(&result);
fields.reserve(field_count);
for (unsigned int i = 0; i < field_count; ++i)
{
fields.push_back(fs[i].name);
}
//处理行
MYSQL_ROW row;
while (row = mysql_fetch_row(&result))
{
rows.emplace_back(*this, row);
}
mysql_free_result(&result);
}
QueryResult::QueryResult(QueryResult&& result):fields(std::move(result.fields))
{
rows.reserve(result.rows.size());
for (auto&& row : result.rows)
{
rows.emplace_back(*this, std::move(row));
}
}
const std::vector<QueryRow>& QueryResult::GetRows() const noexcept
{
return rows;
}
const std::vector<std::string>& QueryResult::GetFields() const noexcept
{
return fields;
}
inline bool QueryResult::IsEmpty() const noexcept
{
return rows.empty();
}
QueryResult::~QueryResult()
{
}
QueryRow::QueryRow(const QueryResult& result, MYSQL_ROW row) :res(result)
{
for (size_t i = 0; i < res.GetFields().size(); ++i)
{
if (row[i])
data.push_back(row[i]);
else
data.push_back("");
}
}
QueryRow::QueryRow(const QueryResult& newres, QueryRow&& row) :res(newres), data(std::move(row.data))
{
}
const std::vector<std::string>& QueryRow::GetData() const noexcept
{
return data;
}
std::string QueryRow::operator[](const std::string& index)const
{
for (size_t i = 0; i < res.GetFields().size(); ++i)
{
if (IgnoreCaseCompare(res.GetFields()[i], index))
return data[i];
}
throw MySQLFieldNotFoundException(std::format("未找到名为\"{}\"的列", index));
}
MySQLFieldNotFoundException::MySQLFieldNotFoundException(const std::string& message) :MySQLException(message)
{
}
MySQLEscapeException::MySQLEscapeException(const std::string& message) :MySQLException(message)
{
}
}
}
博主创作不易,三连支持一下吧!