#ifndef _PRE_STATEMENT_H_
#define _PRE_STATEMENT_H_
#include "mysql.h"
#include <string>
class pre_statement
{
public:
pre_statement();
~pre_statement();
int init(MYSQL* mysql, string& sql);
int set_param_bind(int index, int& value);
int set_param_bind(int index, string& value);
int set_param_bind(int index, MYSQL_TIME& value);
int execute();
int query();
int set_param_result(int index, enum_field_types type, char* buffer, unsigned long buffer_length, unsigned long *length);
int get_result();
int fetch_result();
void free();
private:
MYSQL_STMT* m_stmt;
MYSQL_BIND* m_param_bind;
MYSQL_BIND* m_result_bind;
MYSQL_RES* m_result;
unsigned long m_param_count;
unsigned int m_result_count;
};
#endif
#include "pre_statement.h"
pre_statement::pre_statement()
{
m_stmt = NULL;
m_param_bind = NULL;
m_result_bind = NULL;
m_result = NULL;
m_param_count = 0;
m_result_count = 0;
}
pre_statement::~pre_statement()
{
free();
}
int pre_statement::init(MYSQL* mysql, string& sql)
{
m_stmt = mysql_stmt_init(mysql);
if (!m_stmt)
{
return 1;
}
if (mysql_stmt_prepare(m_stmt, sql.c_str(), sql.size()))
{
return 1;
}
m_param_count = mysql_stmt_param_count(m_stmt);
if (m_param_count > 0)
{
m_param_bind = new MYSQL_BIND[m_param_count];
if (!m_param_bind)
{
return 1;
}
memset(m_param_bind, 0, sizeof(MYSQL_BIND)*m_param_count);
}
return 0;
}
int pre_statement::set_param_bind(int index, int& value)
{
if (index >= m_param_count)
{
perror("索引超出总数!\n");
return 1;
}
m_param_bind[index].buffer_type = MYSQL_TYPE_LONG;
m_param_bind[index].buffer = &value;
return 0;
}
int pre_statement::set_param_bind(int index, string& value)
{
if (index >= m_param_count)
{
perror("索引超出总数!\n");
return 1;
}
m_param_bind[index].buffer_type = MYSQL_TYPE_STRING;
m_param_bind[index].buffer = (void*)(value.c_str());
m_param_bind[index].buffer_length = value.size();
return 0;
}
int pre_statement::set_param_bind(int index, MYSQL_TIME& value)
{
if (index >= m_param_count)
{
perror("索引超出总数!\n");
return 1;
}
m_param_bind[index].buffer_type = MYSQL_TYPE_TIMESTAMP;
m_param_bind[index].buffer = &value;
m_param_bind[index].buffer_length = sizeof(value);
return 0;
}
int pre_statement::set_param_result(int index, enum_field_types type, char* buffer, unsigned long buffer_length, unsigned long *length)
{
if (index >= m_result_count)
{
perror("索引超出总数!\n");
return 1;
}
m_result_bind[index].buffer_type = type;
m_result_bind[index].buffer = buffer;
m_result_bind[index].buffer_length = buffer_length;
m_result_bind[index].length = length;
return 0;
}
int pre_statement::execute()
{
if (!m_stmt)
{
return 1;
}
if (mysql_stmt_bind_param(m_stmt, m_param_bind))
{
//mysql_stmt_errno(m_stmt);
return 1;
}
if (mysql_stmt_execute(m_stmt))
{
//mysql_stmt_errno(m_stmt);
return 1;
}
if (0 == mysql_stmt_affected_rows(m_stmt))
{
perror("更新失败没有改变!\n");
return 1;
}
return 0;
}
int pre_statement::query()
{
if (!m_stmt)
{
return 1;
}
if (mysql_stmt_bind_param(m_stmt, m_param_bind))
{
//mysql_stmt_errno(m_stmt);
return 1;
}
m_result = mysql_stmt_result_metadata(m_stmt);
if (NULL == m_result)
{
//mysql_stmt_errno(m_stmt);
return 1;
}
else
{
m_result_count = mysql_num_fields(m_result);
m_result_bind = new MYSQL_BIND[m_result_count];
if (!m_result_bind)
{
return 1;
}
memset(m_result_bind, 0, sizeof(MYSQL_BIND)*m_result_count);
}
if (mysql_stmt_execute(m_stmt))
{
//mysql_stmt_errno(m_stmt);
return 1;
}
return 0;
}
int pre_statement::get_result()
{
if (mysql_stmt_bind_result(m_stmt, m_result_bind))
{
return 1;
}
if (mysql_stmt_store_result(m_stmt))
{
return 1;
}
return 0;
}
int pre_statement::fetch_result()
{
if (!mysql_stmt_fetch(m_stmt))
{
return 0;
}
else
{
return 1;
}
}
void pre_statement::free()
{
if (m_stmt)
{
mysql_stmt_close(m_stmt);
m_stmt = NULL;
}
if (m_param_bind)
{
delete[]m_param_bind;
m_param_bind = NULL;
}
if (m_result_bind)
{
delete[]m_result_bind;
m_result_bind = NULL;
}
if (NULL != m_result)
{
mysql_free_result(m_result);
m_result = NULL;
}
m_param_count = 0;
m_result_count = 0;
}
使用步骤说明:
1. init是将SQL语句和MySQL连接实例绑定在一起的,并且检测sql语句中含有多少个“?”,然后对应的创建bind数组,后面对数组内容赋值,就相当于对每个“?”赋值。
2. set_param_bind就是对SQL语句中的问号进行赋值,第一个索引index为0,和数组下标规则一样。
3. 执行execute就是执行SQL语句,先是把之前赋值了的bind带入到SQL语句中,然后execute执行,最后会检测是否对数据库行数造成了影响。
4. 如果此时SQL语句不是增删改,而是查,那么我们就不是步骤3调用execute了而是调用query,也是先把之前赋值了的bind带入到SQL语句中,然后metadata会计算这个查询语句得到的结果集的一个元数据(不带数据的只是结果集的属性列表),我们通过这个元数据要计算结果集列的数目,所以就num_fields。
5. 为结果集绑定bind,使用set_param_result,这里的buffer通过后面的执行,会得到列值。
6. 执行get_result获取结果集。
7. 执行fetch_result对结果集逐行来进行获取。
8. 最后关闭预处理语句,释放bind的内存,释放结果集。