第二个库: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 api | |
1、建立,断开数据库连接 | mysql_real_connect,mysql_close | |
2、维持数据库链接心跳 | mysql_ping | |
3、直接执行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_string | |
4、预处理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_error | |
5、获取结果集之后,释放结果集(以上两种方式通用) | mysql_free_result | |
6、在数据库连接上获取错误信息 | 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);
//ping
static bool Ping(MYSQL* mysql);
/直接执行SQL的方式/
//转换sql字符
static bool MakeSafeStr(MYSQL* mysql, char *to, int toLen, const char *from, int fromLen);
//执行sql
static 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);
//执行预处理sql
static 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专用log
extern common::tool::Logger g_LibDBLog;
#endif
DBDefine.cpp
#include "DBDefine.h"
#include "Logger.h"
using namespace common::tool;
//libdb专用log
Logger g_LibDBLog("LibDB");