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

    数据库使用的是Mysql 。

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

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

/*********************************************
创建人： Snight
类名  ： CMysqStmt & CMysqlDataBase
功能  ： MySql数据库访问封装
版本  ： V1.0
说明  ： 实现 CMysqStmt 语句 CMysqlDataBase 数据库 对象
时间  ： 2011-02-23
QQ    ： 51171107
**********************************************/
#ifndef H_MYSQLHELPER_H
#define H_MYSQLHELPER_H
#include "CTypeDef.h"
#include "mysql.h"
#pragma comment(lib,"libmySQL.lib")

#define MySql_SvrGoneAway	2006																				// 连接已经失效 这时候应该调用 ReConnect

/**********************************************/
class CMysqStmt
{
public:
	CMysqStmt(MYSQL* apMySql);
	CMysqStmt(void);
public:
	CMysqStmt*	StmtInit(void);																					// 初始化
	void		StmtUnInit(void);																				// 释放
	int			StmtPrepare(char* apStrdPrcdrsName, ...);														// 准备语句
	int			StmtExcute(void);																				// 执行语句
	
	BOOL		StmtSetCnt(int aiColCnt);																		// 设置取几列结果

	BOOL		StmtBindCol(SInt8&		acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);
	BOOL		StmtBindCol(SInt16&		acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);
	BOOL		StmtBindCol(SInt32&		acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);
	BOOL		StmtBindCol(SLong&		acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);
	BOOL		StmtBindCol(SInt64&		acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);
	BOOL		StmtBindCol(float&		acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);
	BOOL		StmtBindCol(double&		acParam, ULong* aulRetLen = NULL, char* acRetNull = NULL);
	BOOL		StmtBindCol(char*&		acParam, int aiLen, ULong* aulRetLen = NULL, char* acRetNull = NULL);
	int			StmtBindResult(void);
	int			StmtStoreResult(void);																			// 存储结果
	int			StmtFetch(void);																				// 取结果
	int			StmtFreeResult(void);																			// 释放结果
	int			StmtNextResult(void);
	UInt64		StmtNumRows(void);

private:
	MYSQL_STMT*					m_pStmt;
	MYSQL*						m_pMysql;
	MYSQL_BIND*					m_pColBind;	
	int							m_iColCnt;																		// 总列数
	int							m_iColNum;																		// 当前绑定到第几列
};

class	CMysqlDataBase
{
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);
	
	//  执行存储过程
	int				RealQuery(char* apStrdPrcdrsName, ...);

	CMysqStmt*		StmtInit(void);

	void			StmtUnInit(CMysqStmt* apStm);

	int				StmtPrepare(MYSQL_STMT* apStmt, char* apStrdPrcdrsName, ...);

	//  档结果
	MYSQL_RES*		StoreResult(void);

	// 得到错误信息
	const char*			GetError(void) 
		{ return  mysql_error(m_pMysql);} ;			

	
private:
	// 记录连接信息
	BOOL			RcdCnctInfo(	char* apHost, char* apUName, char* apPWord, 
									char* apDBName, ULong aulPort);

	MYSQL		m_oMysql;			// Mysql对象
	MYSQL*		m_pMysql;			// Mysql对象
	ULong		m_ulPort;
	char		m_lszHost[20];		
	char		m_lszUName[20];
	char		m_lszPWord[20];
	char		m_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;
}

int	CMysqStmt::StmtFetch(void)
{
	if (m_pStmt)
		return mysql_stmt_fetch(m_pStmt);
	return -1;
}

int	CMysqStmt::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;
}

int	CMysqStmt::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;
}

UInt64	CMysqStmt::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));

//  执行存储过程
int	CMysqlDataBase::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;
}