数据库MySql类库系列(八)-预处理执行Sql方式的示例
来源:互联网 发布:350淘宝装修 编辑:程序博客网 时间:2024/05/03 14:01
本文是对之前预处理执行Sql方式的示例程序TestDB
基于前文的DBService,PrepareOperatorSelect,PrepareOperatorUpdate
首先是数据表定义:
还是一个简单的账号表,包括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,// 账号idaccount_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(){//预处理sqlInitPropertyLen();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(){//预处理sqlm_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 <iostream>#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;}
0 0
- 数据库MySql类库系列(八)-预处理执行Sql方式的示例
- 数据库MySql类库系列(五)-直接执行Sql方式的示例
- MySQL---数据库从入门走向大神系列(八)-在java中执行MySQL的存储过程
- MySQL数据库优化的八种方式
- MySQL数据库优化的八种方式
- MySQL数据库优化的八种方式
- MySQL 数据库优化的八种方式
- MySQL数据库优化的八种方式
- Castle学习系列(八)---ActiveRecord 执行的SQL监测
- MySQL直接执行和预处理执行效率比较示例
- mysql数据库存储过程带预处理sql的模板
- mysql,sql的Java连接方法(简单应用)执行sql查询与预处理
- MySQL数据库优化的八种方式(经典必看)
- MySQL数据库优化的八种方式(经典必看)
- MySQL数据库优化的八种方式(经典必看)
- 详解MySQL数据库优化的八种方式
- MySQL数据库优化的八种方式(经典必看)
- MySQL数据库优化的八种方式(经典必看)
- MyBatis传入多种参数及MYSQL大数据写入错误
- Bootstrap媒体对象
- python-orm django学习-脑图
- PHP-Zend引擎剖析之词法分析(一)
- 1123.Is It a Complete AVL Tree (30)
- 数据库MySql类库系列(八)-预处理执行Sql方式的示例
- java调用Weka中神经网络的算法(从数据库中取数据)
- 分享Window服务项目脚手架
- BZOJ3293: [Cqoi2011]分金币
- STM32F407ZET6 USART DMA方式收发数据
- 关于约瑟夫问题
- Ubuntu下设置环境变量有三种方法
- H5缓存-Manifes
- 【数值计算】数值解析--n元一次联立方程组