mysql使用数据库预处理_数据库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的方式，实现增、删、改、查

首先，实现一个PrepareDBService，继承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种：增删改对应PrepareOperatorUpdate，查找对应PrepareOperatorSelect)

// 对应增、删、改、查四种操作的预处理的绑定参数/绑定结果

void PrepareSelectAccount();

void PrepareInsertAccount();

void PrepareUpdateAccount();

void PrepareDeleteAccount();

// 对应增、删、改、查四种操作对象

common::db::PrepareOperatorSelect m_select_account;

common::db::PrepareOperatorUpdate m_insert_account;

common::db::PrepareOperatorUpdate m_update_account;

common::db::PrepareOperatorUpdate m_delete_account;

主要的类关系图如下：

主函数：

1、插入一个账号，账号名=Test001，密码=0000001的账号

2、更新这个账号的密码，改为1111111

3、查询这个账号名=Test001的账号信息(账号名，密码，id)，此时密码应为第2步已经修改后的密码，如果该表此前没有插入过记录，此时id应该为1，每执行一次插入id+1

4、删除这个账号名=Test001的账号

5、再次查询这个账号名=Test001的账号信息，此时应该没有对应的数据

执行结果截图：

DBService的子类实现：

PrepareDBService.h：

#ifndef __PrepareDBService_H__

#define__PrepareDBService_H__

#include "DBService.h"

#include "PrepareOperatorSelect.h"

#include "PrepareOperatorUpdate.h"

class PrepareDBService : public common::db::DBService

{

public:

PrepareDBService();

virtual ~PrepareDBService();

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:

// 账号属性类型

enum AccountPropertyType

{

account_name = 0,// 帐号名称

account_key,// 账号密码

account_id,// 账号id

account_property_count,

};

unsigned long m_property_len[account_property_count];

void InitPropertyLen();

// 账号信息绑定值

char m_account_name[MaxAccountLen];

char m_account_key[MaxAccountLen];

unsigned int m_account_id;

// 各个预处理的绑定参数/绑定结果

void PrepareSelectAccount();

void PrepareInsertAccount();

void PrepareUpdateAccount();

void PrepareDeleteAccount();

// 对应增、删、改、查四种操作对象

common::db::PrepareOperatorSelect m_select_account;

common::db::PrepareOperatorUpdate m_insert_account;

common::db::PrepareOperatorUpdate m_update_account;

common::db::PrepareOperatorUpdate m_delete_account;

};

#endif

PrepareDBService.cpp：

#include "PrepareDBService.h"

PrepareDBService::PrepareDBService()

{

}

PrepareDBService::~PrepareDBService()

{

}

bool PrepareDBService::ProcessStart()

{

//预处理sql

InitPropertyLen();

PrepareSelectAccount();

PrepareInsertAccount();

PrepareUpdateAccount();

PrepareDeleteAccount();

return true;

}

void PrepareDBService::ProcessStop()

{

m_select_account.Release();

m_insert_account.Release();

m_update_account.Release();

m_delete_account.Release();

}

void PrepareDBService::InitPropertyLen()

{

m_property_len[account_name] = sizeof(m_account_name);

m_property_len[account_key] = sizeof(m_account_key);

m_property_len[account_id] = sizeof(m_account_id);

}

void PrepareDBService::PrepareSelectAccount()

{

//预处理sql

m_select_account.BindSql(m_Connect,

"select * from account where account_name = ?;");

m_select_account.BindResult("%s,%s,%u",

m_account_name, &m_property_len[account_name],

m_account_key, &m_property_len[account_key],

&m_account_id);

m_select_account.BindParameter("%s",

m_account_name, &m_property_len[account_name]);

}

bool PrepareDBService::SelectAccount(const char(&name)[MaxAccountLen], char(&key)[MaxAccountLen], unsigned int& id)

{

boost::mutex::scoped_lock lock(m_Lock);

strcpy(m_account_name, name);

if (m_select_account.DoOperator())

{

if (m_select_account.FetchResult())

{

strcpy(key, m_account_key);

id = m_account_id;

m_select_account.FreeResult();

return true;

}

else

{

return false;

}

}

else

{

return false;

}

}

void PrepareDBService::PrepareInsertAccount()

{

m_insert_account.BindSql(m_Connect,

"insert into account(account_name, account_key) values(?, ?);");

m_insert_account.BindParameter("%s,%s",

m_account_name, &m_property_len[account_name],

m_account_key, &m_property_len[account_key]);

}

bool PrepareDBService::InsertAccount(const char(&name)[MaxAccountLen], const char(&key)[MaxAccountLen])

{

boost::mutex::scoped_lock lock(m_Lock);

strcpy(m_account_name, name);

strcpy(m_account_key, key);

return m_insert_account.DoOperator();

}

void PrepareDBService::PrepareUpdateAccount()

{

m_update_account.BindSql(m_Connect,

"update account set account_key = ? where account_name = ?;");

m_update_account.BindParameter("%s,%s",

m_account_key, &m_property_len[account_key],

m_account_name, &m_property_len[account_name]);

}

bool PrepareDBService::UpdateAccount(const char(&name)[MaxAccountLen], const char(&key)[MaxAccountLen])

{

boost::mutex::scoped_lock lock(m_Lock);

strcpy(m_account_name, name);

strcpy(m_account_key, key);

return m_update_account.DoOperator();

}

void PrepareDBService::PrepareDeleteAccount()

{

m_delete_account.BindSql(m_Connect,

"delete from account where account_name = ?;");

m_delete_account.BindParameter("%s",

m_account_name, &m_property_len[account_name]);

}

bool PrepareDBService::DeleteAccount(const char(&name)[MaxAccountLen])

{

boost::mutex::scoped_lock lock(m_Lock);

strcpy(m_account_name, name);

return m_delete_account.DoOperator();

}

主函数TestDB.cpp：

#include

#include "PrepareDBService.h"

void PrepareAccount()

{

char accountName[PrepareDBService::MaxAccountLen] = { 0 };

char accountKey[PrepareDBService::MaxAccountLen] = { 0 };

unsigned int accountId = 0;

PrepareDBService 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[])

{

PrepareAccount();

system("pause");

return 0;

}