数据库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字符串做解析，只需要对变量赋值，就可以执行得出结果。

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

提供的主要功能：

上代码：

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);		};	}}#endifDBOperator.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;#endifDBDefine.cpp
#include "DBDefine.h"#include "Logger.h"using namespace common::tool;//libdb专用logLogger g_LibDBLog("LibDB");