C++封装mysql数据库操作类及其使用

1、封装类：

#pragma once
#include 
#include 
using namespace std;

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

	//连接
	bool connect(string user, string passWord, string dbName, string ip, unsigned short port = 3306, unsigned long multiSqlFlag = 0);
	//设置编码格式
	void setCharacter(string strCsName);
	//设置连接超时时间
	bool setConnTimeOut(int second);
	//设置自动重连
	bool setReconnect(bool bReconn = false);
	//查询
	bool query(string sql);
	//逐行遍历结果集
	bool next();
	//得到结果集中的字段值
	string value(int index);
	//增、删、改
	bool execute(string sql);
	//获取文件大小
	DWORD getFileSize(string strFileName);
	//字符转换
	bool convertSqlString(char* dataFrom, char* dataTo, unsigned long fromLen, unsigned long* toLen = nullptr);
	//事务操作
	bool transaction(bool bAuto = false);
	//提交事务
	bool commit();
	//事务回滚 
	bool rollback();
private:
	void init();
	void release();
	void freeResult();
	bool options(mysql_option opt, const void *arg);

private:
	MYSQL* m_conn = nullptr;
	MYSQL_RES* m_result = nullptr;
	MYSQL_ROW m_row = nullptr;
};

#include "mysqlConn.h"

mysqlConn::mysqlConn()
{
	init();
}

mysqlConn::~mysqlConn()
{
	release();
}

bool mysqlConn::connect(string user, string passWord, string dbName, string ip, unsigned short port, unsigned long multiSqlFlag)
{
	if (nullptr == m_conn)
	{
		if (0 != mysql_errno(m_conn)) printf("错误信息:m_conn is nullptr!n");
		return false;
	}
	if (!mysql_real_connect(m_conn, ip.c_str(), user.c_str(), passWord.c_str(), dbName.c_str(), port, nullptr, multiSqlFlag))
	{
		if (0 != mysql_errno(m_conn)) printf("错误编码:%d, 错误信息:%sn", mysql_errno(m_conn), mysql_error(m_conn));
		return false;
	}

	return true;
}

void mysqlConn::setCharacter(string strCsName)
{
	if (m_conn)
	{
		mysql_set_character_set(m_conn, strCsName.c_str());
	}
}

bool mysqlConn::setConnTimeOut(int second)
{
	return options(MYSQL_OPT_CONNECT_TIMEOUT, &second);
}

bool mysqlConn::setReconnect(bool bReconn)
{
	return options(MYSQL_OPT_RECONNECT, &bReconn);
}

bool mysqlConn::query(string sql)
{
	freeResult();
	if (mysql_real_query(m_conn, sql.c_str(), sql.length()))
	{
		if (0 != mysql_errno(m_conn)) printf("错误编码:%d, 错误信息:%sn", mysql_errno(m_conn), mysql_error(m_conn));
		return false;
	}
	if (nullptr == (m_result = mysql_store_result(m_conn)))
	{
		if (0 != mysql_errno(m_conn)) printf("错误编码:%d, 错误信息:%sn", mysql_errno(m_conn), mysql_error(m_conn));
		return false;
	}
	return true;
}

bool mysqlConn::next()
{
	if ((nullptr != m_result) && (nullptr != (m_row = mysql_fetch_row(m_result))))
	{
		return true;
	}

	if(0 != mysql_errno(m_conn)) printf("错误编码:%d, 错误信息:%sn", mysql_errno(m_conn), mysql_error(m_conn));
	return false;
}

string mysqlConn::value(int index)
{
	int colCount = mysql_num_fields(m_result);
	if (index >= colCount || index < 0)
	{
		return string();
	}

	return string(m_row[index], mysql_fetch_lengths(m_result)[index]);
}

bool mysqlConn::execute(string sql)
{
	if (nullptr == m_conn)
	{
		if (0 != mysql_errno(m_conn)) printf("错误信息:m_conn is nullptr!n");
		return false;
	}
	if (mysql_real_query(m_conn, sql.c_str(), sql.length()))
	{
		if (0 != mysql_errno(m_conn)) printf("错误信息:m_conn is nullptr!n");
		return false;
	}
	return true;
}

DWORD mysqlConn::getFileSize(string strFileName)
{
	DWORD fileSize = 0;
	HANDLE hFind = INVALID_HANDLE_VALUE;
	WIN32_FIND_DATA fileInfo;

	hFind = FindFirstFile(strFileName.c_str(), &fileInfo);
	if (hFind != INVALID_HANDLE_VALUE) {
		fileSize = fileInfo.nFileSizeLow;
		FindClose(hFind);
	}

	return fileSize;
}

bool mysqlConn::convertSqlString(char * dataFrom, char * dataTo, unsigned long fromLen, unsigned long* toLen )
{
	if (!m_conn || !dataFrom || !dataTo)
	{
		return false;
	}

	unsigned long dataToSize = mysql_real_escape_string(m_conn, dataTo, dataFrom, fromLen);
	if (toLen) *toLen = dataToSize;

	return true;
}

bool mysqlConn::transaction(bool bAuto )
{
	return mysql_autocommit(m_conn, bAuto);
}

bool mysqlConn::commit()
{
	return mysql_commit(m_conn);
}

bool mysqlConn::rollback()
{
	return mysql_rollback(m_conn);
}

void mysqlConn::init()
{
	m_conn = mysql_init(nullptr);
}

void mysqlConn::release()
{
	freeResult();
	if (m_conn != nullptr)
	{
		mysql_close(m_conn), m_conn = nullptr;
	}
}

void mysqlConn::freeResult()
{
	if (m_result != nullptr)
	{
		mysql_free_result(m_result), m_result = nullptr;
	}
}

bool mysqlConn::options(mysql_option opt, const void * arg)
{
	if (nullptr == m_conn)
	{
		if (0 != mysql_errno(m_conn)) printf("错误信息:m_conn is nullptr!n");
		return false;
	}

	int nRet = mysql_options(m_conn, opt, arg);
	if (nRet != 0)
	{
		if (0 != mysql_errno(m_conn)) printf("错误编码:%d, 错误信息:%sn", mysql_errno(m_conn), mysql_error(m_conn));
		return false;
	}
	return true;
}

2、使用

测试用表：包含int型、varchar型、datetime型、blob型

#include 
#include 
#include 
#include 
using namespace std;


#include "mysqlConn.h"

int main()
{
	mysqlConn sqlCon;
	sqlCon.connect("root", "root", "zzc", "localhost", 3306, 0);

	sqlCon.setCharacter("gbk");

	//查询
	if (0)
	{
		string strSql = "select id, name, time, photo from student";
		sqlCon.query(strSql);

		int index = 0;
		while (sqlCon.next())
		{
			++index;
			for (int i = 0; i < 4; i++)
			{
				if (i == 3)
				{
					stringstream pic_name;
					pic_name << "D:\test" << index << ".jpg";
					ofstream outfile(pic_name.str(), ios::binary);
					outfile.write(sqlCon.value(i).c_str(), sqlCon.value(i).length());
					outfile.close();
				}
				else
				{
					cout << sqlCon.value(i) << "  ";
				}
			}
			cout << endl;
		}
	}

	//插入
	if (0)
	{
		string strFileName = "D:\test1.jpg";
		DWORD dwFileSize = sqlCon.getFileSize(strFileName);
		printf("文件大小%un", dwFileSize);

		char* dataFrom = new char[dwFileSize];
		char* dataTo = new char[dwFileSize * 2 + 1];

		ZeroMemory(dataFrom, dwFileSize);
		ZeroMemory(dataTo, dwFileSize * 2 + 1);

		FILE *f = NULL;
		fopen_s(&f, strFileName.c_str(), "rb");
		if (f != NULL && dataFrom != NULL)
		{
			fread(dataFrom, dwFileSize, 1, f);
		}

		sqlCon.convertSqlString(dataFrom, dataTo, dwFileSize);

		char* pSql = new char[dwFileSize * 2 + 256];
		char szSql[] = "insert into student (id, name, time, photo) values(1013, 'zhaoliu','2022-04-23 16:32:34', '%s')";
		sprintf(pSql, szSql, dataTo);

		sqlCon.execute(pSql);

		if (f) fclose(f), f = NULL;
		if (dataFrom) delete[] dataFrom;
		if (dataTo) delete[] dataTo;
		if (pSql) delete[] pSql;
	}
	

	//更新
	if (0)
	{
		string strFileName = "D:\123.png";
		DWORD dwFileSize = sqlCon.getFileSize(strFileName);
		printf("文件大小%un", dwFileSize);

		char* dataFrom = new char[dwFileSize];
		char* dataTo = new char[dwFileSize * 2 + 1];

		ZeroMemory(dataFrom, dwFileSize);
		ZeroMemory(dataTo, dwFileSize * 2 + 1);

		FILE *f = NULL;
		fopen_s(&f, strFileName.c_str(), "rb");
		if (f != NULL && dataFrom != NULL)
		{
			fread(dataFrom, dwFileSize, 1, f);
		}

		sqlCon.convertSqlString(dataFrom, dataTo, dwFileSize);

		char* pSql = new char[dwFileSize * 2 + 256];
		char szSql[] = "update student set name = '张三丰', time = '2022-04-23 16:43:55', photo = '%s' where id = 1013";
		sprintf(pSql, szSql, dataTo);

		sqlCon.execute(pSql);

		if (f) fclose(f), f = NULL;
		if (dataFrom) delete[] dataFrom;
		if (dataTo) delete[] dataTo;
		if (pSql) delete[] pSql;
	}

	//删除
	if (0)
	{
		char szSql[] = "delete from student where id = 1013";
		sqlCon.execute(szSql);
	}

	//事务操作
	if (1)
	{
		sqlCon.transaction();

		char szSql[MAX_PATH] = { 0 };
		for (int i = 0; i < 10; ++i)
		{
			sprintf(szSql, "insert student(id, name, time) values(%d, 'zhangsan', '2022-04-14 15:39:33')", i);
			sqlCon.execute(szSql);
		}

		sqlCon.commit();
		//sqlCon.rollback();
	}

	return 0;
}