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

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

首先是数据表定义：

一个简单的账号表，包括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;

};

#endif

QueryDBService.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

#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;

}