IM 开源项目 群组服务 数据库设计-数据库访问 （02）

    数据库使用的是Mysql 。

    操作直接就用Mysql提供的API，为了保证以后数据库操作的通用性和简化API调用，这里对API进行无关业务的封装，

    目前封装了写基本功能，之后根据需求可以不断扩转，下面贴码：

/*********************************************创建人： Snight类名  ： CMysqStmt & CMysqlDataBase功能  ： MySql数据库访问封装版本  ： V1.0说明  ： 实现 CMysqStmt 语句 CMysqlDataBase 数据库 对象时间  ： 2011-02-23QQ    ： 51171107**********************************************/#ifndef H_MYSQLHELPER_H#define H_MYSQLHELPER_H#include "CTypeDef.h"#include "mysql.h"#pragma comment(lib,"libmySQL.lib")#define MySql_SvrGoneAway2006// 连接已经失效 这时候应该调用 ReConnect/**********************************************/class CMysqStmt{public:CMysqStmt(MYSQL* apMySql);CMysqStmt(void);public:CMysqStmt*StmtInit(void);// 初始化voidStmtUnInit(void);// 释放intStmtPrepare(char* apStrdPrcdrsName, ...);// 准备语句intStmtExcute(void);// 执行语句BOOLStmtSetCnt(int aiColCnt);// 设置取几列结果BOOLStmtBindCol(SInt8&acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);BOOLStmtBindCol(SInt16&acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);BOOLStmtBindCol(SInt32&acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);BOOLStmtBindCol(SLong&acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);BOOLStmtBindCol(SInt64&acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);BOOLStmtBindCol(float&acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);BOOLStmtBindCol(double&acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);BOOLStmtBindCol(char*&acParam, int aiLen, ULong* aulRetLen = NULL, char* acRetNull = NULL);intStmtBindResult(void);intStmtStoreResult(void);// 存储结果intStmtFetch(void);// 取结果intStmtFreeResult(void);// 释放结果intStmtNextResult(void);UInt64StmtNumRows(void);private:MYSQL_STMT*m_pStmt;MYSQL*m_pMysql;MYSQL_BIND*m_pColBind;intm_iColCnt;// 总列数intm_iColNum;// 当前绑定到第几列};classCMysqlDataBase{public:CMysqlDataBase(void);~CMysqlDataBase(void);public://连接到数据库BOOL ConnectDb(char* apHost,  char* apUName,  char* apPWord,    char* apDBName,  ULong aulPort);//重新连接数据库BOOL ReConnect(char* apHost = NULL, char* apUName = NULL, char* apPWord = NULL,    char* apDBName = NULL,  ULong aulPort = 0);//  执行存储过程intRealQuery(char* apStrdPrcdrsName, ...);CMysqStmt*StmtInit(void);voidStmtUnInit(CMysqStmt* apStm);intStmtPrepare(MYSQL_STMT* apStmt, char* apStrdPrcdrsName, ...);//  档结果MYSQL_RES*StoreResult(void);// 得到错误信息const char*GetError(void) { return  mysql_error(m_pMysql);} ;private:// 记录连接信息BOOLRcdCnctInfo(char* apHost, char* apUName, char* apPWord, char* apDBName, ULong aulPort);MYSQLm_oMysql;// Mysql对象MYSQL*m_pMysql;// Mysql对象ULongm_ulPort;charm_lszHost[20];charm_lszUName[20];charm_lszPWord[20];charm_lszDBName[20];};#endif//H_MYSQLHELPER_H

 

#include "StdAfx.h"#include "MysqlHelper.h"#include <stdarg.h>#include "CRunningLog.h"/***********************CMysqStmt***********************/CMysqStmt::CMysqStmt(MYSQL* apMySql) : m_pMysql(apMySql), m_pStmt(NULL), m_pColBind(NULL), m_iColCnt(0), m_iColNum(0){}CMysqStmt::CMysqStmt(void){if (m_pColBind) delete [] m_pColBind;}CMysqStmt* CMysqStmt::StmtInit(void){if (m_pMysql)m_pStmt =mysql_stmt_init(m_pMysql);if (m_pStmt)  return this;return NULL;}void CMysqStmt::StmtUnInit(void){mysql_stmt_close(m_pStmt);}int CMysqStmt::StmtPrepare(char* apStrdPrcdrsName, ...){int liRet = -1;if (m_pStmt){va_list list; va_start(list, apStrdPrcdrsName);char lszContext[250] = {0};vsprintf_s(lszContext, apStrdPrcdrsName, list);liRet=  mysql_stmt_prepare(m_pStmt, lszContext, (unsigned int)strlen(lszContext));va_end(list);if (0 != liRet){liRet = mysql_errno(m_pMysql);ERROR_LOG("ERROR: MYSQL Error:%d Context:%s", liRet, mysql_error(m_pMysql));}}return liRet;}// 执行int CMysqStmt::StmtExcute(void){if (m_pStmt)return mysql_stmt_execute(m_pStmt);return -1;}intCMysqStmt::StmtFetch(void){if (m_pStmt)return mysql_stmt_fetch(m_pStmt);return -1;}intCMysqStmt::StmtFreeResult(void){if (m_pStmt){StmtNextResult();return mysql_stmt_free_result(m_pStmt);}return -1;}BOOL CMysqStmt::StmtSetCnt(int aiColCnt){if (m_pColBind){delete  [] m_pColBind;m_iColNum = 0;m_pColBind = NULL;}m_pColBind= new MYSQL_BIND[aiColCnt];m_iColCnt= aiColCnt;if (m_pColBind){memset(m_pColBind, 0 , sizeof(MYSQL_BIND)*aiColCnt);return TRUE;}return FALSE;}intCMysqStmt::StmtBindResult(void){if (m_pStmt && m_pColBind)return mysql_stmt_bind_result(m_pStmt, m_pColBind);return -1;}int CMysqStmt::StmtStoreResult(void){if (m_pStmt)return mysql_stmt_store_result(m_pStmt);return -1;}int CMysqStmt::StmtNextResult(void){if (m_pStmt)return mysql_stmt_next_result(m_pStmt);return -1;}UInt64CMysqStmt::StmtNumRows(void){if (m_pStmt)return mysql_stmt_num_rows(m_pStmt);return 0;}BOOL CMysqStmt::StmtBindCol(SInt8&  aoParam, ULong* aulRetLen /*= NULL*/, char* acRetNull /*= NULL*/){if((m_pColBind) && (m_iColNum < m_iColCnt)){m_pColBind[m_iColNum].buffer_type=MYSQL_TYPE_TINY;m_pColBind[m_iColNum].buffer=&aoParam;m_pColBind[0].length=aulRetLen;m_pColBind[0].is_null=acRetNull;m_iColNum++;return TRUE;}return FALSE;}BOOL CMysqStmt::StmtBindCol(SInt16&  aoParam, ULong* aulRetLen /*= NULL*/, char* acRetNull /*= NULL*/){if((m_pColBind) && (m_iColNum < m_iColCnt)){m_pColBind[m_iColNum].buffer_type=MYSQL_TYPE_SHORT;m_pColBind[m_iColNum].buffer=&aoParam;m_pColBind[0].length=aulRetLen;m_pColBind[0].is_null=acRetNull;m_iColNum++;return TRUE;}return FALSE;}BOOL CMysqStmt::StmtBindCol(SInt32&  aoParam, ULong* aulRetLen /*= NULL*/, char* acRetNull /*= NULL*/){if((m_pColBind) && (m_iColNum < m_iColCnt)){m_pColBind[m_iColNum].buffer_type=MYSQL_TYPE_LONG;m_pColBind[m_iColNum].buffer=&aoParam;m_pColBind[0].length=aulRetLen;m_pColBind[0].is_null=acRetNull;m_iColNum++;return TRUE;}return FALSE;}BOOL CMysqStmt::StmtBindCol(SLong&  aoParam, ULong* aulRetLen /*= NULL*/, char* acRetNull /*= NULL*/){if((m_pColBind) && (m_iColNum < m_iColCnt)){m_pColBind[m_iColNum].buffer_type=MYSQL_TYPE_LONG;m_pColBind[m_iColNum].buffer=&aoParam;m_pColBind[0].length=aulRetLen;m_pColBind[0].is_null=acRetNull;m_iColNum++;return TRUE;}return FALSE;}BOOL CMysqStmt::StmtBindCol(SInt64&  aoParam, ULong* aulRetLen /*= NULL*/, char* acRetNull /*= NULL*/){if((m_pColBind) && (m_iColNum < m_iColCnt)){m_pColBind[m_iColNum].buffer_type=MYSQL_TYPE_LONGLONG;m_pColBind[m_iColNum].buffer=&aoParam;m_pColBind[0].length=aulRetLen;m_pColBind[0].is_null=acRetNull;m_iColNum++;return TRUE;}return FALSE;}BOOL CMysqStmt::StmtBindCol(float&   aoParam, ULong* aulRetLen /*= NULL*/, char* acRetNull /*= NULL*/){if((m_pColBind) && (m_iColNum < m_iColCnt)){m_pColBind[m_iColNum].buffer_type=MYSQL_TYPE_FLOAT;m_pColBind[m_iColNum].buffer=&aoParam;m_pColBind[0].length=aulRetLen;m_pColBind[0].is_null=acRetNull;m_iColNum++;return TRUE;}return FALSE;}BOOL CMysqStmt::StmtBindCol(double&  aoParam, ULong* aulRetLen /*= NULL*/, char* acRetNull /*= NULL*/){if((m_pColBind) && (m_iColNum < m_iColCnt)){m_pColBind[m_iColNum].buffer_type=MYSQL_TYPE_DOUBLE;m_pColBind[m_iColNum].buffer=&aoParam;m_pColBind[0].length=aulRetLen;m_pColBind[0].is_null=acRetNull;m_iColNum++;return TRUE;}return FALSE;}BOOL CMysqStmt::StmtBindCol(char*&  aoParam, int aiLen, ULong* aulRetLen /*= NULL*/, char* acRetNull /*= NULL*/){if((m_pColBind) && (m_iColNum < m_iColCnt)){m_pColBind[m_iColNum].buffer_type=MYSQL_TYPE_VAR_STRING;m_pColBind[m_iColNum].buffer=&aoParam;m_pColBind[m_iColNum].buffer_length=aiLen;m_pColBind[0].length=aulRetLen;m_pColBind[0].is_null=acRetNull;m_iColNum++;return TRUE;}return FALSE;}/***********************CMysqlDataBase***********************/CMysqlDataBase::CMysqlDataBase(void) : m_pMysql(0){mysql_init(&m_oMysql); //初始化mysql句柄.}CMysqlDataBase::~CMysqlDataBase(void){mysql_close(&m_oMysql);}BOOL CMysqlDataBase::RcdCnctInfo(char* apHost, char* apUName, char* apPWord, char* apDBName,  ULong aulPort){if ((apHost) && (apUName) && (apPWord) && (apDBName)){m_ulPort = aulPort;ZeroMemory(m_lszHost,sizeof(char)*20);ZeroMemory(m_lszUName,sizeof(char)*20);ZeroMemory(m_lszPWord,sizeof(char)*40);ZeroMemory(m_lszDBName,sizeof(char)*40);strcat(m_lszHost, apHost);strcat(m_lszUName, apUName);strcat(m_lszPWord, apPWord);strcat(m_lszDBName, apDBName);return TRUE;}return FALSE;}BOOL CMysqlDataBase::ReConnect(char* apHost, char* apUName, char* apPWord, char* apDBName, ULong aulPort){m_pMysql = NULL;mysql_close(&m_oMysql);mysql_init(&m_oMysql);BOOL abResult = TRUE;if(apHost)abResult = RcdCnctInfo(apHost, apUName, apPWord, apDBName, aulPort);if (m_pMysql = mysql_real_connect(&m_oMysql, m_lszHost, m_lszUName,m_lszPWord, m_lszDBName,m_ulPort, NULL, CLIENT_MULTI_STATEMENTS))return TRUE;return FALSE;}BOOL CMysqlDataBase::ConnectDb(char* apHost, char* apUName, char* apPWord, char* apDBName, ULong aulPort){if ((!m_pMysql)&&(apHost)&&(apUName)&&(apPWord)&&(apDBName)){if(RcdCnctInfo(apHost, apUName, apPWord, apDBName, aulPort))if (m_pMysql = mysql_real_connect(&m_oMysql, apHost, apUName,apPWord, apDBName,aulPort, NULL, CLIENT_MULTI_STATEMENTS))return TRUE;}return FALSE;}// 如果是错误2006 的话是连接已经断开// char lszError[250] = {0};// sprintf(lszError,"ERROR: MYSQL Error:%d Context:%s", mysql_errno(m_pMysql), mysql_error(m_pMysql));//  执行存储过程intCMysqlDataBase::RealQuery(char* apStrdPrcdrsName, ...){int liRet = -1;if (m_pMysql){va_list list; va_start(list, apStrdPrcdrsName);char lszContext[250] = {0};vsprintf_s(lszContext, apStrdPrcdrsName, list);liRet=  mysql_real_query(m_pMysql, lszContext, (unsigned int)strlen(lszContext));va_end(list);if (0 != liRet){// liRet = mysql_errno(m_pMysql);// char lszError[250] = {0};// sprintf(lszError,"ERROR: MYSQL Error:%d Context:%s", mysql_errno(m_pMysql), mysql_error(m_pMysql));ERROR_LOG("ERROR: MYSQL Error:%d Context:%s", liRet, mysql_error(m_pMysql));}}return liRet;}CMysqStmt* CMysqlDataBase::StmtInit(void){if (m_pMysql){CMysqStmt* lpSqlStmt = new CMysqStmt(m_pMysql);if (lpSqlStmt->StmtInit())return lpSqlStmt;delete lpSqlStmt;lpSqlStmt = NULL;}return NULL;}void CMysqlDataBase::StmtUnInit(CMysqStmt* apStmt){if (apStmt) apStmt->StmtUnInit();}MYSQL_RES* CMysqlDataBase::StoreResult(void){MYSQL_RES*  loRes = NULL;if (m_pMysql)loRes = mysql_store_result(m_pMysql);return loRes;}