简单易用的mysql c api 封装类

本文参考mysql官方文档（http://dev.mysql.com/doc/refman/5.6/en/c-api.html），对MySQL C API 简单的C++封装。欢迎大家批评指正。

1.源码文件：

 

#ifndef _MY_SQL_DB_H_
#define _MY_SQL_DB_H_

#include <windows.h>
#include <winsock2.h>
#include <string>
#include <map>
#include "mysql.h"
using namespace std;

class CMySQLDB
{
public:
	CMySQLDB();
	~CMySQLDB();

	my_bool BeginTransaction();
	my_bool CommitTransaction();
	my_bool RollbackTransaction();
    my_bool EndTransaction();

	int OpenDB(const char *host,
		const char *user,
		const char *passwd,
		const char *db,
		unsigned int port);

	int ExecuteSQL(const string& sql);
	int NextRow();
	bool IsEndOfRow();
	int SeekRow(my_ulonglong offset);

	string GetValue(int fieldIndex);
	string GetValue(const string& fieldName);

	my_ulonglong GetNumRows(){ return m_num_rows; }
	unsigned int GetNumFields(){ return m_num_fields; }

private:
	int Initialize();
	void ResetQuery();

	HANDLE m_hMutex;
	MYSQL*		m_mySQL_CON;
	MYSQL_RES*	m_mySQL_RES;
	MYSQL_ROW	m_mySQL_ROW;
	my_ulonglong m_num_rows;
	unsigned int m_num_fields;
	map<string,int> m_name2IndexMap;
};
#endif // _MY_SQL_DB_H_

 

 

#include "StdAfx.h"
#include "MySQLDB.h"
#pragma comment(lib, "libmysql.lib")

CMySQLDB::CMySQLDB()
: m_mySQL_CON(NULL)
, m_mySQL_RES(NULL)
, m_mySQL_ROW(NULL)
{
	m_num_rows = 0;
	m_num_fields = 0;
	m_hMutex = CreateMutex(NULL,FALSE,_T("MYSQLDBMUTEX"));
	Initialize();
}

int CMySQLDB::Initialize()
{
	WaitForSingleObject(m_hMutex,INFINITE);
	int res = mysql_library_init(0,NULL,NULL);
	if (res==0)
	{
		m_mySQL_CON = mysql_init(NULL);
		if (NULL == m_mySQL_CON)
		{
			ReleaseMutex(m_hMutex);
			return -1;
		}

		my_bool reConnect = 1;
		res = mysql_options(m_mySQL_CON,MYSQL_OPT_RECONNECT,&reConnect);
	}
	ReleaseMutex(m_hMutex);
	return res;
}

CMySQLDB::~CMySQLDB()
{
	if (m_hMutex != INVALID_HANDLE_VALUE)
	{
		CloseHandle(m_hMutex);
		m_hMutex = INVALID_HANDLE_VALUE;
	}
	if (m_mySQL_CON)
	{
		mysql_close(m_mySQL_CON);
		m_mySQL_CON = NULL;
	}
	mysql_library_end();
}

int CMySQLDB::OpenDB(const char *host,
					 const char *user, 
					 const char *passwd, 
					 const char *db,
					 unsigned int port)
{
	if (mysql_real_connect(m_mySQL_CON,host,user,passwd,db,port,NULL,0))
	{
		return mysql_set_character_set(m_mySQL_CON,"GBK");
	}
	return -1;
}

int CMySQLDB::ExecuteSQL( const std::string& sql )
{
	ResetQuery();
	if (mysql_query(m_mySQL_CON,sql.c_str()) == 0)
	{
		m_mySQL_RES = mysql_store_result(m_mySQL_CON);
		if (NULL != m_mySQL_RES)
		{
			m_num_rows = mysql_num_rows(m_mySQL_RES);
			m_mySQL_ROW = mysql_fetch_row(m_mySQL_RES);

			MYSQL_FIELD* field = mysql_fetch_fields(m_mySQL_RES);
			m_num_fields = mysql_num_fields(m_mySQL_RES);
			for (int i = 0; i<m_num_fields;++i)
			{
				m_name2IndexMap.insert(std::pair<std::string,int>(field[i].name,i));
			}
		}
		else
		{
			if (mysql_field_count(m_mySQL_CON) == 0)
			{
				return 0;
			}
		}
            return 0;
	}
	return -1;
}

void CMySQLDB::ResetQuery()
{
	if (NULL != m_mySQL_RES)
	{
		mysql_free_result(m_mySQL_RES);
		m_mySQL_RES = NULL;
	}
	m_mySQL_ROW = NULL;
	m_num_rows = 0;
	m_num_fields = 0;
	m_name2IndexMap.clear();
}

string CMySQLDB::GetValue(int fieldIndex)
{
	string value;
	if (fieldIndex>= 0 && fieldIndex < m_num_fields)
	{
		if (m_mySQL_ROW)
		{
			if (NULL != m_mySQL_ROW[fieldIndex])
				value = m_mySQL_ROW[fieldIndex];
		}
	}
	return value;
}

string CMySQLDB::GetValue(const string& fieldName)
{
	string value;

	int index = -1;
	for (map<string,int>::iterator iter = m_name2IndexMap.begin();
		iter != m_name2IndexMap.end();++iter)
	{
		if (fieldName == iter->first)
		{
			index = iter->second;
			break;
		}
	}

	return GetValue(index);
}

int CMySQLDB::NextRow()
{
	m_mySQL_ROW = mysql_fetch_row(m_mySQL_RES);
	if (m_mySQL_RES == NULL)
		return -1;
	return 0;
}

bool CMySQLDB::IsEndOfRow()
{
	return m_mySQL_ROW == NULL;
}

int CMySQLDB::SeekRow( my_ulonglong offset )
{
	if (offset< 0 || offset >= m_num_rows)
		return -1;
	mysql_data_seek(m_mySQL_RES,offset);
	m_mySQL_ROW = mysql_fetch_row(m_mySQL_RES);
	if (m_mySQL_ROW == NULL)
		return -1;
	return 0;
}

my_bool CMySQLDB::BeginTransaction()
{
	return mysql_autocommit(m_mySQL_CON,0);
}
my_bool CMySQLDB::CommitTransaction()
{
	return mysql_commit(m_mySQL_CON);
}
my_bool CMySQLDB::RollbackTransaction()
{
	return mysql_rollback(m_mySQL_CON);
}
my_bool CMySQLDB::EndTransaction()
{
	return mysql_autocommit(m_mySQL_CON,1);
}

2. 如何使用

 

 

 

 

 

在需要的地方包含该头文件：MySQLDB.h

代码示例：

 

CMySQLDB db;
	db.OpenDB("localhost","root","root","mysqlbzbh",3306);
	string sql = "select * from customers";
	db.ExecuteSQL(sql);
	string strValue;
	while (!db.IsEndOfRow())
	{
		for (unsigned int i = 0; i< db.GetNumFields();++i)
  		{
   			strValue = db.GetValue(i); // 按照索引
   			TRACE(CString(strValue.c_str()));
  		}
		db.NextRow();
	}

	db.SeekRow(0);
	while (!db.IsEndOfRow())
	{
		strValue = db.GetValue("cust_address"); // 按照字段名
		TRACE(CString(strValue.c_str()));
		db.NextRow();
	}