数据库MySql类库系列(一)-DBOperator

第二个库：libdb，封装了MySql的c api

第一个工具类：DBOperator，比较纯粹的，就是封装MySql的c api

主要是为了提供一些更友好一些的接口，供上层逻辑使用

封装了出错时的错误信息输出到log（log使用的是之前工具类库中的Logger）

提供两类接口：

1、直接执行sql的方式：

直接执行sql的方式就是，每次都需要进行字符串拼接，

用每一个字段的值，想要进行的操作，拼接一个完整的sql语句。

拼接之后，交给mysql。

mysql收到sql字符串之后，需要先对字符串进行解析，再执行。

2、预处理sql的方式：

预处理sql的方式就是，先将sql语句中字段的值，以变量的形式（用一个问号'?'表示），先占位，组成一个带占位符的sql语句，

然后就交给mysql去解析字符串。

在此之后的每次执行，不需要再次对sql字符串做解析，只需要对变量赋值，就可以执行得出结果。

一般推荐都是采用第二种方式。

提供的主要功能：

功能说明对应封装的c api1、建立，断开数据库连接mysql_real_connect，mysql_close2、维持数据库链接心跳mysql_ping3、直接执行sql(1)、执行sql语句mysql_real_query(2)、执行sql之后，如果是查询，获取结果集mysql_store_result(3)、获取结果集之后，获取结果集的列数mysql_num_fields(4)、获取结果集之后，获取其中一条结果数据（数据以字符串数组的形式表现）mysql_fetch_row(5)、获取一条结果数据之后，获取每一列的数据长度（字符串形式的长度）mysql_fetch_lengths(6)、拼接sql语句时，执行sql之前，如有必要，对sql字符串进行安全转换，防止sql注入mysql_real_escape_string4、预处理sql(1)、预处理sql语句，得到预处理句柄（以下预处理相关的操作，将都在这个句柄上执行）mysql_stmt_init，mysql_stmt_prepare(2)、绑定sql语句的参数（参数一般就是sql语句中where后面跟的那些字段）mysql_stmt_bind_param(3)、绑定sql语句的查询结果（查询结果就是select ... from，select的那些字段）mysql_stmt_bind_result(4)、具体绑定某一类型的值（由于本人的业务需要，目前仅提供支持7种数据类型：int，unsigned int，long long，unsigned long long，float，字符串，Blob）MYSQL_BIND结构赋值(5)、真实执行预处理sql，得到结果mysql_stmt_execute(6)、执行sql之后，如果是查询，获取结果集mysql_stmt_result_metadata，mysql_stmt_store_result(7)、获取结果集之后，获取其中一条结果数据（数据将直接返回到（4）中绑定的对应内存中）mysql_stmt_fetch(8)、关闭，释放预处理句柄mysql_stmt_close(9)、预处理方式的获取错误信息mysql_stmt_errno，mysql_stmt_error5、获取结果集之后，释放结果集（以上两种方式通用）mysql_free_result6、在数据库连接上获取错误信息mysql_errno，mysql_error

上代码：

DBOperator.h

#ifndef __DBOperator_h__#define__DBOperator_h__struct st_mysql;typedef struct st_mysql MYSQL;struct st_mysql_stmt;typedef struct st_mysql_stmt MYSQL_STMT;struct st_mysql_bind;typedef struct st_mysql_bind MYSQL_BIND;struct st_mysql_res;typedef struct st_mysql_res MYSQL_RES;typedef char** MYSQL_ROW;namespace common{namespace db{class DBOperator{public:DBOperator();~DBOperator();//获取错误信息static void GetErrorInfo(MYSQL* mysql);static void GetStmtErrorInfo(MYSQL_STMT* stmt);//连接数据库static MYSQL* Connect(const char *host,unsigned int port,const char *user,const char *passwd,const char *db,const char *charset = "utf8");//断开连接static void DisConnect(MYSQL* mysql);//pingstatic bool Ping(MYSQL* mysql);/////////////////////直接执行SQL的方式///////////////////////转换sql字符static bool MakeSafeStr(MYSQL* mysql, char *to, int toLen, const char *from, int fromLen);//执行sqlstatic bool ExecQuery(MYSQL* mysql, const char *sql);//查询/获取查询结果集static MYSQL_RES* SelectResult(MYSQL* mysql);static MYSQL_ROW GetNextResult(MYSQL_RES* res);//获取结果集的列数static unsigned int GetResultFields(MYSQL_RES* res);//获取结果集的每个字段的（字符串形式的）长度static unsigned long* GetResultLens(MYSQL_RES* res);//释放结果集（预处理方式下通用）static void FreeResult(MYSQL_RES* pRes);/////////////////////预处理执行SQL的方式///////////////////////预处理句柄static MYSQL_STMT* ExecPrepare(MYSQL* mysql, const char *sql);//绑定字段static bool BindInt(MYSQL_STMT* stmt, MYSQL_BIND* bind, int *value);static bool BindUInt(MYSQL_STMT* stmt, MYSQL_BIND* bind, unsigned int *value);static bool BindInt64(MYSQL_STMT* stmt, MYSQL_BIND* bind, long long *value);static bool BindUInt64(MYSQL_STMT* stmt, MYSQL_BIND* bind, unsigned long long *value);static bool BindFloat(MYSQL_STMT* stmt, MYSQL_BIND* bind, float *value);static bool BindString(MYSQL_STMT* stmt, MYSQL_BIND* bind, char *value, unsigned long *len);static bool BindBlob(MYSQL_STMT* stmt, MYSQL_BIND* bind, void *value, unsigned long *len);//绑定参数/结果static bool BindResultStmt(MYSQL_STMT* stmt, MYSQL_BIND* bind);static bool BindParameterStmt(MYSQL_STMT* stmt, MYSQL_BIND* bind);//执行预处理sqlstatic bool ExecStmt(MYSQL_STMT* stmt);//查询/获取查询结果static MYSQL_RES* SelectResult(MYSQL_STMT* stmt);static bool GetNextResult(MYSQL_STMT* stmt);//关闭预处理句柄static bool FreeStmt(MYSQL_STMT* stmt);};}}#endif

DBOperator.cpp

#include "DBOperator.h"#ifdef WIN32#include <winsock2.h>#endif#include <stdio.h>#include <mysql.h>#include <string.h>#include "Logger.h"using namespace common::tool;#include "DBDefine.h"namespace common{namespace db{DBOperator::DBOperator(){}DBOperator::~DBOperator(){}void DBOperator::GetErrorInfo(MYSQL* mysql){LOG_INFO(g_LibDBLog) << "error : " << mysql_errno(mysql) << ", " << mysql_error(mysql);}void DBOperator::GetStmtErrorInfo(MYSQL_STMT* stmt){LOG_INFO(g_LibDBLog) << "error : " << mysql_stmt_errno(stmt) << ", " << mysql_stmt_error(stmt);}//申请数据源的连接MYSQL* DBOperator::Connect(const char *host,unsigned int port,const char *user,const char *passwd,const char *db,const char *charset){MYSQL* mysql = mysql_init(NULL);if (NULL != mysql){mysql_options(mysql, MYSQL_SET_CHARSET_NAME, charset);mysql = mysql_real_connect(mysql, host, user, passwd, db, port, NULL, 0);if (NULL != mysql){return mysql;}else{GetErrorInfo(mysql);return NULL;}}else{GetErrorInfo(mysql);return NULL;}}void DBOperator::DisConnect(MYSQL* mysql){if (NULL != mysql){mysql_close(mysql);}}bool DBOperator::Ping(MYSQL* mysql){if (NULL != mysql){if (0 == mysql_ping(mysql)){return true;}else{GetErrorInfo(mysql);return false;}}else{return false;}}bool DBOperator::MakeSafeStr(MYSQL* mysql, char *to, int toLen, const char *from, int fromLen){if (NULL != mysql){memset(to, 0x00, sizeof(char)* toLen);if (0 != mysql_real_escape_string(mysql, to, from, fromLen)){return true;}else{GetErrorInfo(mysql);return false;}}else{return false;}}bool DBOperator::ExecQuery(MYSQL* mysql, const char *sql){if (NULL != mysql && NULL != sql){if (0 == mysql_real_query(mysql, sql, strlen(sql))){return true;}else{GetErrorInfo(mysql);return false;}}else{return false;}}MYSQL_RES* DBOperator::SelectResult(MYSQL* mysql){if (NULL != mysql){return mysql_store_result(mysql);}else{return NULL;}}MYSQL_ROW DBOperator::GetNextResult(MYSQL_RES* res){if (NULL != res){return mysql_fetch_row(res);}else{return NULL;}}unsigned int DBOperator::GetResultFields(MYSQL_RES* res){if (NULL != res){return mysql_num_fields(res);}else{return 0;}}unsigned long* DBOperator::GetResultLens(MYSQL_RES* res){if (NULL != res){return mysql_fetch_lengths(res);}else{return NULL;}}void DBOperator::FreeResult(MYSQL_RES* res){if (NULL != res){mysql_free_result(res);}}MYSQL_STMT* DBOperator::ExecPrepare(MYSQL* mysql, const char *sql){if (NULL != mysql){MYSQL_STMT* stmt = mysql_stmt_init(mysql);if (NULL != stmt){if (0 == mysql_stmt_prepare(stmt, sql, strlen(sql))){return stmt;}else{GetStmtErrorInfo(stmt);return NULL;}}else{GetErrorInfo(mysql);return NULL;}}else{return NULL;}}bool DBOperator::BindInt(MYSQL_STMT* stmt, MYSQL_BIND* bind, int *value){if (NULL != stmt && NULL != bind && NULL != value){memset(bind, 0, sizeof(MYSQL_BIND));bind->buffer_type = MYSQL_TYPE_LONG;bind->buffer = (char *)value;bind->is_unsigned = false;return true;}else{return false;}}bool DBOperator::BindUInt(MYSQL_STMT* stmt, MYSQL_BIND* bind, unsigned int *value){if (NULL != stmt && NULL != bind && NULL != value){memset(bind, 0, sizeof(MYSQL_BIND));bind->buffer_type = MYSQL_TYPE_LONG;bind->buffer = (char *)value;bind->is_unsigned = true;return true;}else{return false;}}bool DBOperator::BindInt64(MYSQL_STMT* stmt, MYSQL_BIND* bind, long long *value){if (NULL != stmt && NULL != bind && NULL != value){memset(bind, 0, sizeof(MYSQL_BIND));bind->buffer_type = MYSQL_TYPE_LONGLONG;bind->buffer = (char *)value;bind->is_unsigned = false;return true;}else{return false;}}bool DBOperator::BindUInt64(MYSQL_STMT* stmt, MYSQL_BIND* bind, unsigned long long *value){if (NULL != stmt && NULL != bind && NULL != value){memset(bind, 0, sizeof(MYSQL_BIND));bind->buffer_type = MYSQL_TYPE_LONGLONG;bind->buffer = (char *)value;bind->is_unsigned = true;return true;}else{return false;}}bool DBOperator::BindFloat(MYSQL_STMT* stmt, MYSQL_BIND* bind, float *value){if (NULL != stmt && NULL != bind && NULL != value){memset(bind, 0, sizeof(MYSQL_BIND));bind->buffer_type = MYSQL_TYPE_FLOAT;bind->buffer = (char *)value;return true;}else{return false;}}bool DBOperator::BindString(MYSQL_STMT* stmt, MYSQL_BIND* bind, char *value, unsigned long *len){if (NULL != stmt && NULL != bind && NULL != value && NULL != len){memset(bind, 0, sizeof(MYSQL_BIND));bind->buffer_type = MYSQL_TYPE_STRING;bind->buffer = (char *)value;bind->buffer_length = *len;bind->length = len;return true;}else{return false;}}bool DBOperator::BindBlob(MYSQL_STMT* stmt, MYSQL_BIND* bind, void *value, unsigned long *len){if (NULL != stmt && NULL != bind && NULL != value && NULL != len){memset(bind, 0, sizeof(MYSQL_BIND));bind->buffer_type = MYSQL_TYPE_BLOB;bind->buffer = (char *)value;bind->buffer_length = *len;bind->length = len;return true;}else{return false;}}bool DBOperator::BindParameterStmt(MYSQL_STMT* stmt, MYSQL_BIND* bind){if (NULL != stmt && NULL != bind){if (0 == mysql_stmt_bind_param(stmt, bind)){return true;}else{GetStmtErrorInfo(stmt);return false;}}else{return false;}}bool DBOperator::BindResultStmt(MYSQL_STMT* stmt, MYSQL_BIND* bind){if (NULL != stmt && NULL != bind){if (0 == mysql_stmt_bind_result(stmt, bind)){return true;}else{GetStmtErrorInfo(stmt);return false;}}else{return false;}}bool DBOperator::ExecStmt(MYSQL_STMT* stmt){if (NULL != stmt){if (0 == mysql_stmt_execute(stmt)){return true;}else{GetStmtErrorInfo(stmt);return false;}}else{return false;}}MYSQL_RES* DBOperator::SelectResult(MYSQL_STMT* stmt){if (NULL != stmt){MYSQL_RES* res = mysql_stmt_result_metadata(stmt);if (NULL != res){if (0 == mysql_stmt_store_result(stmt)){return res;}else{GetStmtErrorInfo(stmt);return NULL;}}else{return NULL;}}else{return NULL;}}bool DBOperator::GetNextResult(MYSQL_STMT* stmt){if (NULL != stmt){if (0 == mysql_stmt_fetch(stmt)){return true;}else{return false;}}else{return false;}}bool DBOperator::FreeStmt(MYSQL_STMT* stmt){if (NULL != stmt){if (0 == mysql_stmt_close(stmt)){return true;}else{GetStmtErrorInfo(stmt);return false;}}else{return false;}}}}

DBDefine.h

#ifndef __DBDefine_h__#define __DBDefine_h__namespace common{namespace tool{class Logger;}}//libdb专用logextern common::tool::Logger g_LibDBLog;#endif

DBDefine.cpp

#include "DBDefine.h"#include "Logger.h"using namespace common::tool;//libdb专用logLogger g_LibDBLog("LibDB");