数据库MySql类库系列(五)-直接执行Sql方式的示例

本文是对之前直接执行Sql方式的示例程序TestDB

基于前文的DBService，QueryOperatorSelect，QueryOperatorUpdate

首先是数据表定义：

一个简单的账号表，包括3个字段：帐号名（最长20个字符，主键），账号密码（最长20个字符），账号id（无符号整数，自增字段）

sql如下：

CREATE TABLE `account` (  `account_name` varchar(20) NOT NULL,  `account_key` varchar(20) NOT NULL,  `account_id` int(11) unsigned NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`account_name`),  UNIQUE KEY `account_id_index` (`account_id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;下面演示对这张表，以直接执行sql的方式，实现增、删、改、查
首先，实现一个QueryDBService，继承DBService
实现4个接口：
	// 分别实现增、删、改、查	bool SelectAccount(const char(&name)[MaxAccountLen], char(&key)[MaxAccountLen], unsigned int& id);	bool InsertAccount(const char(&name)[MaxAccountLen], const char(&key)[MaxAccountLen]);	bool UpdateAccount(const char(&name)[MaxAccountLen], const char(&key)[MaxAccountLen]);	bool DeleteAccount(const char(&name)[MaxAccountLen]);对应4个操作对象（2种：增删改对应QueryOperatorUpdate，查找对应QueryOperatorSelect）
	// 对应增、删、改、查四种操作对象	common::db::QueryOperatorSelect m_select_account;	common::db::QueryOperatorUpdate m_insert_account;	common::db::QueryOperatorUpdate m_update_account;	common::db::QueryOperatorUpdate m_delete_account;主函数：
1、插入一个账号，账号名=Test001，密码=0000001的账号
2、更新这个账号的密码，改为1111111
3、查询这个账号名=Test001的账号信息（账号名，密码，id），此时密码应为第2步已经修改后的密码，如果该表此前没有插入过记录，此时id应该为1，每执行一次插入id+1
4、删除这个账号名=Test001的账号
5、再次查询这个账号名=Test001的账号信息，此时应该没有对应的数据
执行结果截图：
DBService的子类实现：
QueryDBService.h：
#ifndef __QueryDBService_H__#define	__QueryDBService_H__#include "DBService.h"#include "QueryOperatorSelect.h"#include "QueryOperatorUpdate.h"class QueryDBService : public common::db::DBService{public:	QueryDBService();	virtual ~QueryDBService();public:	// 最大账号，密码字符串长度为20个字符	static const unsigned int MaxAccountLen = 20;public:	virtual bool ProcessStart();	virtual void ProcessStop();	// 分别实现增、删、改、查	bool SelectAccount(const char(&name)[MaxAccountLen], char(&key)[MaxAccountLen], unsigned int& id);	bool InsertAccount(const char(&name)[MaxAccountLen], const char(&key)[MaxAccountLen]);	bool UpdateAccount(const char(&name)[MaxAccountLen], const char(&key)[MaxAccountLen]);	bool DeleteAccount(const char(&name)[MaxAccountLen]);private:	// 对应增、删、改、查四种操作对象	common::db::QueryOperatorSelect m_select_account;	common::db::QueryOperatorUpdate m_insert_account;	common::db::QueryOperatorUpdate m_update_account;	common::db::QueryOperatorUpdate m_delete_account;};#endifQueryDBService.cpp：
#include "QueryDBService.h"QueryDBService::QueryDBService(){}QueryDBService::~QueryDBService(){}bool QueryDBService::ProcessStart(){	return true;}void QueryDBService::ProcessStop(){	m_select_account.Release();	m_insert_account.Release();	m_update_account.Release();	m_delete_account.Release();}bool QueryDBService::SelectAccount(const char(&name)[MaxAccountLen], char(&key)[MaxAccountLen], unsigned int& id){	boost::mutex::scoped_lock lock(m_Lock);	char sql[1024] = { 0 };	sprintf_s(sql, "select * from account where account_name = /"%s/";", name);	if (m_select_account.DoOperator(m_Connect, sql))	{		if (m_select_account.FetchResult())		{			m_select_account.GetColumn(key, 1);			m_select_account.GetColumn(id, 2);			m_select_account.FreeResult();			return true;		}		else		{			return false;		}	}	else	{		return false;	}}bool QueryDBService::InsertAccount(const char(&name)[MaxAccountLen], const char(&key)[MaxAccountLen]){	boost::mutex::scoped_lock lock(m_Lock);	char sql[1024] = { 0 };	sprintf_s(sql, "insert into account (account_name, account_key) values (/"%s/", /"%s/");", name, key);	return m_insert_account.DoOperator(m_Connect, sql);}bool QueryDBService::UpdateAccount(const char(&name)[MaxAccountLen], const char(&key)[MaxAccountLen]){	boost::mutex::scoped_lock lock(m_Lock);	char sql[1024] = { 0 };	sprintf_s(sql, "update account set account_key = /"%s/" where account_name = /"%s/";", key, name);	return m_update_account.DoOperator(m_Connect, sql);}bool QueryDBService::DeleteAccount(const char(&name)[MaxAccountLen]){	boost::mutex::scoped_lock lock(m_Lock);	char sql[1024] = { 0 };	sprintf_s(sql, "delete from account where account_name = /"%s/";", name);	return m_delete_account.DoOperator(m_Connect, sql);}主函数TestDB.cpp：
#include <iostream>#include "QueryDBService.h"void QueryAccount(){	char accountName[QueryDBService::MaxAccountLen] = { 0 };	char accountKey[QueryDBService::MaxAccountLen] = { 0 };	unsigned int accountId = 0;	QueryDBService service;	service.Start("127.0.0.1", 3306, "root", "root", "account");	/////////////////////////Insert/////////////////////////	memset(accountName, 0x00, sizeof(accountName));	strcpy(accountName, "Test001");	memset(accountKey, 0x00, sizeof(accountKey));	strcpy(accountKey, "0000001");	if (service.InsertAccount(accountName, accountKey))	{		std::cout << "InsertAccount name = " << accountName << ", key = " << accountKey << " success" << std::endl;	}	/////////////////////////Update/////////////////////////	memset(accountName, 0x00, sizeof(accountName));	strcpy(accountName, "Test001");	memset(accountKey, 0x00, sizeof(accountKey));	strcpy(accountKey, "1111111");	if (service.UpdateAccount(accountName, accountKey))	{		std::cout << "UpdateAccount name = " << accountName << ", key = " << accountKey << " success" << std::endl;	}	/////////////////////////Select/////////////////////////	memset(accountName, 0x00, sizeof(accountName));	strcpy(accountName, "Test001");	if (service.SelectAccount(accountName, accountKey, accountId))	{		std::cout << "SelectAccount name = " << accountName << ", key = " << accountKey << " , id = " << accountId << std::endl;	}	else	{		std::cout << "no result" << std::endl;	}	/////////////////////////Delete/////////////////////////	memset(accountName, 0x00, sizeof(accountName));	strcpy(accountName, "Test001");	if (service.DeleteAccount(accountName))	{		std::cout << "DeleteAccount name = " << accountName << " success" << std::endl;	}	/////////////////////////Select/////////////////////////	memset(accountName, 0x00, sizeof(accountName));	strcpy(accountName, "Test001");	if (service.SelectAccount(accountName, accountKey, accountId))	{		std::cout << "SelectAccount name = " << accountName << ", key = " << accountKey << " , id = " << accountId << std::endl;	}	else	{		std::cout << "no result" << std::endl;	}	service.Stop();}int main(int argc, char* argv[]){	QueryAccount();	system("pause");	return 0;}