C++ OleDB数据库驱动（MySql驱动姊妹篇）

老狼主

于 2023-10-11 08:26:00 发布

阅读量265

点赞数

文章标签：数据库 c++ mysql

本文链接：https://blog.csdn.net/u012156872/article/details/133759766

版权

上一篇我介绍了MySQL驱动，这里只介绍OelDB调用数据的方法。OleDB因为兼容ODBC和ADO，所以受大众追捧。大家也都非常熟悉了，这里就直接上代码吧。

接口声明：

/************************************************************************/
/*  OleDatabase_Driver MySQL_Connection                                       */
/*  利用MySql API接口，将其进行封装，旨在提供一个方便、快捷的访问接口   */
/*  作者：老狼                                                          */
/*  时间：2023/08/26                                                    */
/*  版本：1.0		                                                    */
/************************************************************************/
#ifndef OLEDATABASE_DRIVER_H
#define OLEDATABASE_DRIVER_H

#include <string>
#include <map>
#include <vector>
#include <iostream>

#ifndef __AFXADO_H
#define __AFXADO_H
#import "C:\\Program Files\\Common Files\\System\\ado\\msado20.tlb" no_namespace rename("EOF","EndOfFile") rename("BOF","FirstOfFile")
#endif

using namespace std;
typedef std::map<std::string, std::string> FieldsValue;

class OleDatabase_Driver
{
protected:
	//代表一个到数据库的连接
	_ConnectionPtr* m_pConn;	//连接数据库的连接对象
	//操作的结果集
	_RecordsetPtr* dataSet;
	//返回查询得到的结果数
	unsigned long num;
	//错误提示信息
	std::string error;
	//查询语句返回一条结果
	FieldsValue record;
	//查询结果集
	std::vector<FieldsValue> records;
	//查询表结构的字段名称
	std::vector<std::string> fields;

public:
	OleDatabase_Driver(void);
	virtual ~OleDatabase_Driver(void);
	
	// 连接驱动
	int connect(std::string host, std::string database, std::string user, std::string password, unsigned int port=3306);
	//执行sql语句
	int exec(std::string sql);
	//执行sql语句
	int select(std::string sql);
	//返回查询得到的结果
	std::vector<FieldsValue> fieldValues();
	//返回错误信息
	std::string errorCode();
	//返回查询表结构字段名称
	std::vector<std::string> fieldsName();
	//向数据库中插入一条数据
	int insert(std::string table, FieldsValue *data);
	//返回最后一个自动增量的值
	unsigned long getlastid();
	//返回一条sql语句影响的行数
	unsigned long numRowsAffected();
	//根据条件修改一条数据
	unsigned int update(std::string table, FieldsValue *data, std::string condition);
	//根据条件删除数据
	unsigned int remove(std::string table, std::string condition);

	//判断数据库是否连接
	bool connected();
	//断开数据库连接
	void disconnected();
};

class MySQL_Connection
{
public:
	MySQL_Connection(OleDatabase_Driver* pOleDatabaseDriver);
	virtual ~MySQL_Connection(void);
	//执行sql语句
	bool exec(std::string sql);	
	//取字符串值
	bool stringValue(std::string &str, std::string fieldName,int nFieldLen=-1);
	//取整型值
	bool intValue(long &lVal, std::string fieldName);
	//取浮点型值
	bool floatValue(double &lVal, std::string fieldName);
	//返回一条sql语句影响的行数
	long numRowsAffected();
	//记录下移
	bool next();
	//记录上移
	bool previous();
	//记录开头
	bool first();
	//记录结尾
	bool last();
	//是否到记录尾
	bool eof();
	//是否到记录头
	bool bof();

private:
	//当前值在记录集中的问题
	int index;
	//记录集
	std::vector<FieldsValue> records;
	//驱动
	OleDatabase_Driver*m_pOleDatabaseDriver;
};

#endif

接口定义：

#include "OleDatabase_Driver.h"
#include <assert.h>
#include <string>
#include <vector>
#include <stdlib.h>
#include <iostream>
#include <algorithm>
#include <sstream>
#include <iomanip>
#include <locale>
#include <windows.h>

namespace util
{
	class ArgBase
	{
	public:
		ArgBase() {}
		virtual ~ArgBase() {}
		virtual void Format(std::ostringstream& ss, const std::string& fmt) = 0;
	};

	template <class T>
	class Arg : public ArgBase
	{
	public:
		Arg(T arg) : m_arg(arg) {}
		virtual ~Arg() {}
		virtual void Format(std::ostringstream& ss, const std::string& fmt)
		{
			ss << m_arg;
		}
	private:
		T m_arg;
	};

	class ArgArray : public std::vector < ArgBase* >
	{
	public:
		ArgArray() {}
		~ArgArray()
		{
			std::for_each(begin(), end(), [](ArgBase* p) { delete p; });
		}
	};

	static void FormatItem(std::ostringstream& ss, const std::string& item, const ArgArray& args)
	{
		long index = 0;
		int alignment = 0;
		std::string fmt;

		char* endptr = nullptr;
		index = strtol(&item[0], &endptr, 10);
		if (index < 0 || index >= (long)args.size())
		{
			return;
		}

		if (*endptr == ',')
		{
			alignment = strtol(endptr + 1, &endptr, 10);
			if (alignment > 0)
			{
				ss << std::right << std::setw(alignment);
			}
			else if (alignment < 0)
			{
				ss << std::left << std::setw(-alignment);
			}
		}

		if (*endptr == ':')
		{
			fmt = endptr + 1;
		}

		args[index]->Format(ss, fmt);

		return;
	}

	template <class T>
	static void Transfer(ArgArray& argArray, T t)
	{
		argArray.push_back(new Arg<T>(t));
	}

	template <class T, typename... Args>
	static void Transfer(ArgArray& argArray, T t, Args&&... args)
	{
		Transfer(argArray, t);
		Transfer(argArray, args...);
	}

	template <typename... Args>
	std::string Format(const std::string& format, Args&&... args)
	{
		if (sizeof...(args) == 0)
		{
			return format;
		}

		ArgArray argArray;
		Transfer(argArray, args...);
		size_t start = 0;
		size_t pos = 0;
		std::ostringstream ss;
		while (true)
		{
			pos = format.find('{', start);
			if (pos == std::string::npos)
			{
				ss << format.substr(start);
				break;
			}

			ss << format.substr(start, pos - start);
			if (format[pos + 1] == '{')
			{
				ss << '{';
				start = pos + 2;
				continue;
			}

			start = pos + 1;
			pos = format.find('}', start);
			if (pos == std::string::npos)
			{
				ss << format.substr(start - 1);
				break;
			}

			FormatItem(ss, format.substr(start, pos - start), argArray);
			start = pos + 1;
		}

		return ss.str();
	}

	static std::wstring ConvertToWideString(const std::string& str) {
		int wstrLength = MultiByteToWideChar(CP_UTF8, 0, str.c_str(), -1, NULL, 0);
		std::wstring wstr(wstrLength, L'\0');
		MultiByteToWideChar(CP_UTF8, 0, str.c_str(), -1, &wstr[0], wstrLength);
		return wstr;
	}

	static std::string ConvertToAnsiString(const std::wstring& wstr) {
		int strLength = WideCharToMultiByte(CP_UTF8, 0, wstr.c_str(), -1, NULL, 0, NULL, NULL);
		std::string str(strLength, '\0');
		WideCharToMultiByte(CP_UTF8, 0, wstr.c_str(), -1, &str[0], strLength, NULL, NULL);
		return str;
	}
}

//
OleDatabase_Driver::OleDatabase_Driver(void)
{
	m_pConn = NULL;
	num = 0;
	error = "";
	dataSet = NULL;
	// 初始化 COM 库
	CoInitialize(NULL);
}

OleDatabase_Driver::~OleDatabase_Driver(void)
{
	// 释放 COM 库资源
	CoUninitialize();
}

// 设置数据库连接参数
#define ADO_CONNECT_STRING_ACCESS "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s; Persist Security Info=True; Jet OLEDB:Database Password=%s"
int OleDatabase_Driver::connect(std::string host,std::string database, std::string user, std::string password,unsigned int port/*=3306*/)
{
	if (connected())
		return 0;
	
	if (CoInitialize(NULL) == RPC_E_CHANGED_MODE)	
		return -1;
	
	m_pConn = new _ConnectionPtr;
	m_pConn->CreateInstance(__uuidof(Connection));
	if (m_pConn == NULL)
		return -2;

	//打开数据库连接
	try
	{
		if (m_pConn == NULL)
		{
			m_pConn = new _ConnectionPtr;
			HRESULT hr = m_pConn->CreateInstance(__uuidof(Connection));
			if (FAILED(hr))
			{
				return -3;
			}
		}
		else
		{
			if ((*m_pConn)->State != adStateClosed)//原来已有连接，则先关闭原来的数据库连接
				(*m_pConn)->Close();
		}

		TCHAR lpConnectString[1024] = { 0 };
		std::string connectString = util::Format(ADO_CONNECT_STRING_ACCESS, database, password);
		HRESULT hr = (*m_pConn)->Open(connectString.c_str(), user.c_str(), password.c_str(), -1);
		if (FAILED(hr))
			return -4;

		(*m_pConn)->CursorLocation = adUseClient;
		exec(" set names 'gb2312' ");

		return 0;
	}
	catch (_com_error err)
	{
		_bstr_t Source(err.ErrorMessage());
		error = util::ConvertToAnsiString(static_cast<const wchar_t*>(err.Description()));
		return -5;
	}
	catch (...)
	{
		return -6;
	}
	
	return 0;
}

//查询数据库，返回记录条数
int OleDatabase_Driver::exec(std::string sql)
{
	try
	{
		VARIANT recordsAffected;
		(*m_pConn)->BeginTrans();
		(*m_pConn)->Execute(sql.c_str(), &recordsAffected, adCmdText | adExecuteNoRecords);
		HRESULT hr = (*m_pConn)->CommitTrans();
		if (FAILED(hr))
		{
			(*m_pConn)->RollbackTrans(); 
			return 0;//建立实例失败
		}
		else
		{			
			record.clear();
			records.clear();
			fields.clear();
			num = recordsAffected.ulVal;
			return num;
		}
	}
	catch (_com_error err)
	{
		(*m_pConn)->RollbackTrans();
		_bstr_t Source(err.ErrorMessage());
		error = util::ConvertToAnsiString(static_cast<const wchar_t*>(err.Description()));
		return 0;
	}
	catch (...)
	{
		(*m_pConn)->RollbackTrans();
		return 0;
	}

	return -1;	
}

//执行sql语句
int OleDatabase_Driver::select(std::string sql)
{
	if (dataSet == NULL)
	{
		dataSet = new _RecordsetPtr;
		HRESULT hr = dataSet->CreateInstance(__uuidof(Recordset));
		if (FAILED(hr))
		{
			return 0;//建立实例失败
		}
	}
	else
	{
		if ((*dataSet)->State != adStateClosed)
			(*dataSet)->Close();//若记录已存在，则关闭记录,并重新使用打开新的记录
	}

	HRESULT hr = (*dataSet)->Open(sql.c_str(), m_pConn->GetInterfacePtr(),
		adOpenStatic, adLockOptimistic/*adLockBatchOptimistic*/, adCmdText);

	if (FAILED(hr))
	{
		return 0;//打开连接失败
	}

	record.clear();
	records.clear();
	fields.clear();
	num = (long)(*dataSet)->RecordCount;

	this->fieldValues();	
	return num;//成功返回
}

/*获取查询得到的所有结果*/
std::vector<FieldsValue> OleDatabase_Driver::fieldValues()
{
	FieldsValue tmp;
	if(records.size() > 0) return records;

	this->fieldsName();

	(*dataSet)->MoveFirst();
	while (!(*dataSet)->EndOfFile)
	{
		FieldsValue tmp;
		// 获取字段集合
		FieldsPtr fieldsPtr = (*dataSet)->GetFields();
		for (unsigned long i = 0; i < fields.size(); i++)
		{
			// 获取字段值
			_variant_t fieldValue = (*dataSet)->GetCollect(_variant_t(fields[i].c_str()));
			// 检查字段值的类型并提取值
			if (fieldValue.vt == VT_NULL)
			{
				tmp[fields[i]] = "";
			}
			else
			{
				std::wstring fieldValueStr = static_cast<wchar_t*>(_bstr_t(fieldValue));
				tmp[fields[i]] = util::ConvertToAnsiString(fieldValueStr);
			}
		}

		records.push_back(tmp);
		(*dataSet)->MoveNext();
	}

	return records;
}

//返回错误信息
std::string OleDatabase_Driver::errorCode()
{
	return error;
}

//返回查询后的列值
std::vector<std::string> OleDatabase_Driver::fieldsName()
{
	// 循环迭代字段集合并输出字段名称
	FieldsPtr fieldsPtr = (*dataSet)->GetFields();
	for (long i = 0; i < fieldsPtr->Count; i++) {
		FieldPtr field = fieldsPtr->Item[i];
		std::wstring fieldName = (BSTR)field->Name;
		fields.push_back(util::ConvertToAnsiString(fieldName));
	}

	return fields;
}

//向数据库中插入一条数据
int OleDatabase_Driver::insert(std::string table, FieldsValue *data)
{
	FieldsValue::const_iterator iter;
	std::string sql;
	int flag=0;
	assert(!table.empty());
	assert(data != NULL);
	for(iter = data->begin();iter!= data->end();iter++)
	{
		if(flag == 0)
		{
			sql = "insert into ";
			sql += table;
			sql += " set ";
			sql += iter->first;
			sql += "='";
			sql += iter->second;
			sql += "'";
			flag++;
		}
		else
		{
			sql += ",";
			sql += iter->first;
			sql += "='";
			sql += iter->second;
			sql += "'";
		}
	}

	return exec(sql);
}

//返回最后一个自动增量的值
unsigned long OleDatabase_Driver::getlastid()
{
	// 执行获取最后插入ID的查询
	VARIANT recordsAffected;
	_RecordsetPtr rs = (*m_pConn)->Execute("SELECT LAST_INSERT_ID()", &recordsAffected, adCmdText | adExecuteNoRecords);

	// 获取结果集中的第一行和第一列的值（即最后插入的ID值）
	long lastInsertId = rs->GetCollect(0);

	return (unsigned long)lastInsertId;
}

//返回一条sql语句影响的行数
unsigned long OleDatabase_Driver::numRowsAffected()
{
	return (unsigned long)num;
}

//根据条件修改一条数据
unsigned int OleDatabase_Driver::update(std::string table, FieldsValue *data, std::string condition)
{
	FieldsValue::const_iterator iter;
	std::string sql;
	int flag=0;
	assert(!table.empty());
	assert(data != NULL);
	for(iter = data->begin();iter!= data->end();iter++)
	{
		if(flag == 0)
		{
			sql  = "update ";
			sql += table;
			sql += " set ";
			sql += iter->first;
			sql += "='";
			sql += iter->second;
			sql += "'";
			flag++;
		}
		else
		{
			sql += ",";
			sql += iter->first;
			sql += "='";
			sql += iter->second;
			sql += "'";
		}
	}

	if(!condition.empty())
	{
		sql += " where ";
		sql += condition;
	}

	return exec(sql);
}

//根据条件删除数据
unsigned int OleDatabase_Driver::remove(std::string table, std::string condition)
{
	std::string sql;
	assert(!table.empty());
	sql = "delete from ";
	sql += table;
	if(!condition.empty())
	{
		sql += " where ";
		sql += condition;
	}

	return exec(sql);
}

bool OleDatabase_Driver::connected()
{
	if (m_pConn == NULL || ((*m_pConn)->State == adStateClosed))
		return false;

	return true;
}

void OleDatabase_Driver::disconnected()
{
	try
	{
		if (m_pConn != NULL)
		{
			if ((*m_pConn)->State != adStateClosed)
				(*m_pConn)->Close();

			delete m_pConn;
			m_pConn = NULL;
		}
	}
	catch (_com_error err)
	{
		_bstr_t Source(err.ErrorMessage());
		error = util::ConvertToAnsiString(static_cast<const wchar_t*>(err.Description()));
	}
	catch (...)
	{
	}
}

//

MySQL_Connection::MySQL_Connection(OleDatabase_Driver* pOleDatabaseDriver)
{
	m_pOleDatabaseDriver = pOleDatabaseDriver;
}


MySQL_Connection::~MySQL_Connection(void)
{
}

bool MySQL_Connection::exec(std::string sql)
{
	if (m_pOleDatabaseDriver != NULL)
	{	
		if (m_pOleDatabaseDriver->exec(sql)==0)
		{
			records = m_pOleDatabaseDriver->fieldValues();
			index = 0;
			if (records.size() > 0)
				return true;
			else
				return false;
		}
		else
			return false;
	}

	return false;
}

bool MySQL_Connection::stringValue(std::string &str, std::string fieldName,int nFieldLen/*=-1*/)
{
	str = records[index][fieldName];
	return true;
}

bool MySQL_Connection::intValue(long &lVal, std::string fieldName)
{
	const char* pszVal = records[index][fieldName].c_str();
	lVal = atoi(pszVal);
	return true;
}

bool MySQL_Connection::floatValue(double &lVal, std::string fieldName)
{
	const char* pszVal = records[index][fieldName].c_str();
	lVal = atof(pszVal);
	return true;
}

long MySQL_Connection::numRowsAffected()
{
	if (m_pOleDatabaseDriver != NULL)
	{
		return m_pOleDatabaseDriver->numRowsAffected();
	}

	return 0;
}

bool MySQL_Connection::next()
{
	if (m_pOleDatabaseDriver == NULL) return false;
	if (++index >= (int)m_pOleDatabaseDriver->numRowsAffected()) return false;
	
	return true;
}

bool MySQL_Connection::previous()
{
	if (m_pOleDatabaseDriver == NULL) return false;
	if (--index < 0) return false;

	return true;
}

bool MySQL_Connection::first()
{
	if (m_pOleDatabaseDriver == NULL) return false;
	index= 0;
	if (index >= (int)m_pOleDatabaseDriver->numRowsAffected()) return false;

	return true;
}

bool MySQL_Connection::last()
{
	if (m_pOleDatabaseDriver == NULL) return false;
	index= m_pOleDatabaseDriver->numRowsAffected() - 1;
	if (index >= (int)m_pOleDatabaseDriver->numRowsAffected()) return false;
	if (index < 0) return false;

	return true;
}

bool MySQL_Connection::eof()
{
	if (m_pOleDatabaseDriver == NULL) return false;
	if (index == m_pOleDatabaseDriver->numRowsAffected()) return true;
	return false;
}

bool MySQL_Connection::bof()
{
	if (m_pOleDatabaseDriver == NULL) return false;
	if (index == 0 && m_pOleDatabaseDriver->numRowsAffected()>0) return true;
	return false;
}