概述
描述MySql中通用的查询及查询结果处理流程。
数据库示例
假定有一个名为test的数据库,包括两个table:tableA, tableB;各自包括一些数据。详细情况如下:
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> CREATE TABLE tableA (name VARCHAR(10), age int, score int);
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE tableB (_id int NOT NULL AUTO_INCREMENT, name VARCHAR(10), pet VARCHAR(10), PRIMARY KEY (_id));
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tablea |
| tableb |
+----------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO tablea VALUES ('John', 10, 70);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tablea VALUES ('Tom', 11, 80);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tablea VALUES ('Mary', 9, 90);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tablea;
+------+------+-------+
| name | age | score |
+------+------+-------+
| John | 10 | 70 |
| Tom | 11 | 80 |
| Mary | 9 | 90 |
+------+------+-------+
3 rows in set (0.00 sec)
mysql> INSERT INTO tableb (name, pet) VALUES ('Mary', 'puppy');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO tableb (name) VALUES ('John');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tableb;
+-----+------+-------+
| _id | name | pet |
+-----+------+-------+
| 1 | Mary | puppy |
| 2 | John | NULL |
+-----+------+-------+
2 rows in set (0.00 sec)
mysql>
需求
- 要查询数据库中有哪些table,列出table的名称;
- 查询每个table中的数据,并对数据做特殊的处理,比如都放到一个容器中。
分析
参考
拷贝如下:
bool GetTableNames(MYSQL *pConn, std::list<std::string> &names)
{
MYSQL_RES *result = NULL;
MYSQL_ROW row;
if (mysql_query(pConn, "SHOW TABLES")) {
printf("query failed: %s\n", mysql_error(pConn));
return false;
}
result = mysql_store_result(pConn); // number of field = 1
while (row = mysql_fetch_row(result)) {
names.push_back(std::string(row[0]));
}
mysql_free_result(result);
return true;
}
通用处理步骤
对于以上这些查询,基本上都遵循如下的步骤:
- mysql_query()
- mysql_store_result()
- mysql_fetch_row()
- mysql_free_result()
这个步骤是完全一致的,差异仅仅在于对mysql_fetch_row()返回的row的差异化处理。
代码重用
对于每次查询,如果重复上述的步骤,是比较郁闷的。一方面源于对mysql的API不是那么熟悉,而更重要的在原因在于“重复”意味着低效。所以我们考虑一种较优的方式,尽量实现代码复用、尽量实现和mysql解耦。
如上所述,差异仅在于对row的处理。所以,我们把处理row的代码独立出来。为此,考虑到下面这种函数原型:
bool handle(MYSQL_ROW row);
返回值bool的考虑:用户可以在某条记录处理异常(不符合用户预期)时,停止遍历剩余的row。如此,衍生出一个需求,是否在出错时退出循环。
通常在处理过程中,需要把row转换成某种数据对象,并且存储起来。所以,可能优化成下面的样子:
bool handle(MYSQL_ROW row, void *data);
void*是为了适应不同的数据需求。但由于void*不是类型安全的,所以进一步优化为:
class IRowHandler {
public:
virtual bool operator()(MYSQL_ROW row) = 0;
};
即抽象出一个functor接口,然后每种使用场景各自实例化这个接口,自然存储数据等都不在话下。
简单原型(思路细化)
下面的代码忽略了一些异常,以突出主要思路。
通用查询流程
class IRowHandler;
bool Query(MYSQL *pConn, const char* command, IRowHandler *pHandler, bool ignoreError/* = true*/)
{
MYSQL_RES *result = NULL;
MYSQL_ROW row;
if (mysql_query(pConn, command)) {
printf("query failed: %s\n", mysql_error(pConn));
return false;
}
result = mysql_store_result(pConn);
bool success = true;
while (row = mysql_fetch_row(result)) {
success = pHandler(row);
if (!ignoreError && !success) break;
}
mysql_free_result(result);
return success;
}
通用接口
class IRowHandler {
public:
virtual bool operator()(MYSQL_ROW row) = 0;
};
接口实例化
先给出一个示例的接口实例。
class QueryTableName: public IRowHandler {
public:
virtual bool operator()(MYSQL_ROW row) {
printf("QueryTableName(), ....");
names.push_back(std::string(row[0]));
return true;
}
int getCount() const {
return names.size();
}
//...
private:
std::list<std::string> names;
};
调用程序
QueryTableName obj;
MYSQL *pConn = null;
//...
if (!Query(pConn, "show tables", &obj)) return;
int count = obj.getCount();
示例代码(可运行)
接下来给出针对一开始提出的问题的可运行代码,涉及文件较多,一一列出。
main.cpp
#include <stdio.h>
#include "MySqlDemo.h"
#include "QueryTableName.h"
#include "QueryTableA.h"
#include "QueryTableB.h"
#pragma comment(lib, "libmysql.lib")
int main () {
CMySqlDemo mysql;
if (!mysql.Connect("127.0.0.1", "test", "root", "admin")) {
return 1;
}
CQueryTableName queryTableName;
mysql.Query("show tables", &queryTableName);
queryTableName.Print();
CQueryTableA queryTableA;
mysql.Query("select * from tablea", &queryTableA);
queryTableA.Print();
CQueryTableB queryTableB;
mysql.Query("select * from tableb", &queryTableB);
queryTableB.Print();
return 0;
}
MySqlDemo.h,.cpp
头文件:
#pragma once
#include <string>
#include <WinSock2.h>
#include <mysql.h>
class IRowHandler;
class CMySqlDemo
{
public:
CMySqlDemo(void);
~CMySqlDemo(void);
bool Connect(const char* host, const char* dbName, const char* user, const char* password);
void Close();
bool Query(const char *command, IRowHandler *pHandler, bool ignoreError = true);
private:
MYSQL *m_pConn;
};
实现文件:
#include "MySqlDemo.h"
#include "RowHandler.h"
CMySqlDemo::CMySqlDemo(void) : m_pConn(NULL)
{
}
CMySqlDemo::~CMySqlDemo(void)
{
Close();
}
bool CMySqlDemo::Connect(const char* host, const char* dbName, const char* user, const char* password)
{
if (m_pConn != NULL) Close();
m_pConn = mysql_init(NULL);
if (!mysql_real_connect(m_pConn, host, user, password, dbName, 0, NULL, 0)) {
printf("Cannot connect to db: %s", mysql_error(m_pConn));
return false;
}
return true;
}
void CMySqlDemo::Close()
{
if (m_pConn != NULL) {
mysql_close(m_pConn);
m_pConn = NULL;
}
}
bool CMySqlDemo::Query(const char *command, IRowHandler *pHandler, bool ignoreError/* = true*/)
{
MYSQL_RES *result = NULL;
MYSQL_ROW row;
if (mysql_query(m_pConn, command)) {
printf("query failed: %s\n", mysql_error(m_pConn));
return false;
}
result = mysql_store_result(m_pConn);
bool success = true;
while (row = mysql_fetch_row(result)) {
success = (*pHandler)(row);
if (!ignoreError && !success) break;
}
mysql_free_result(result);
return success;
}
RowHandler.h
该接口只需要头文件,无实现文件。接下来分别给出三个查询对应的实现类。
#pragma once
#include <mysql.h>
class IRowHandler {
public:
virtual ~IRowHandler(){}
virtual bool operator()(MYSQL_ROW row) = 0;
};
QueryTableName.h, .cpp
头文件:
#pragma once
#include <list>
#include <string>
#include <WinSock2.h>
#include "RowHandler.h"
class CQueryTableName: public IRowHandler
{
public:
CQueryTableName(void);
virtual ~CQueryTableName(void);
virtual bool operator()(MYSQL_ROW row);
int getCount() const {
return names.size();
}
void Print();
private:
std::list<std::string> names;
};
实现文件:
#include "QueryTableName.h"
#include <algorithm>
CQueryTableName::CQueryTableName(void)
{
}
CQueryTableName::~CQueryTableName(void)
{
}
bool CQueryTableName::operator()(MYSQL_ROW row)
{
names.push_back(std::string(row[0]));
return true;
}
static void LogString(const std::string& value)
{
printf("%s\n", value.c_str());
}
void CQueryTableName::Print()
{
std::for_each(names.begin(), names.end(), LogString);
}
QueryTableA.h,.cpp
头文件:
#pragma once
#include <string>
#include <list>
#include <WinSock2.h>
#include "RowHandler.h"
typedef struct tagRecordA {
std::string name;
int age;
int score;
}RecordA;
class CQueryTableA: public IRowHandler
{
public:
CQueryTableA(void);
virtual ~CQueryTableA(void);
virtual bool operator()(MYSQL_ROW row);
void Print();
private:
std::list<RecordA> records;
};
实现文件:
#include "QueryTableA.h"
#include <algorithm>
CQueryTableA::CQueryTableA(void)
{
}
CQueryTableA::~CQueryTableA(void)
{
}
bool CQueryTableA::operator()(MYSQL_ROW row)
{
RecordA record;
record.name = std::string(row[0]);
record.age = atoi(row[1]);
record.score = atoi(row[2]);
records.push_back(record);
return true;
}
static void LogString(const RecordA& value)
{
printf("name=%s, age=%d, score=%d\n", value.name.c_str(), value.age, value.score);
}
void CQueryTableA::Print()
{
std::for_each(records.begin(), records.end(), LogString);
}
QueryTableB.h,.cpp
头文件:
#pragma once
#include <string>
#include <list>
#include <WinSock2.h>
#include "RowHandler.h"
typedef struct tagRecordB {
std::string name;
std::string pet;
}RecordB;
class CQueryTableB: public IRowHandler
{
public:
CQueryTableB(void);
virtual ~CQueryTableB(void);
virtual bool operator()(MYSQL_ROW row);
void Print();
private:
std::list<RecordB> records;
};
实现文件:
#include "QueryTableB.h"
#include <algorithm>
CQueryTableB::CQueryTableB(void)
{
}
CQueryTableB::~CQueryTableB(void)
{
}
bool CQueryTableB::operator()(MYSQL_ROW row)
{
RecordB record;
// 0 is for _id
record.name = std::string(row[1]);
record.pet = row[2] == NULL ? "NULL" : std::string(row[2]);
records.push_back(record);
return true;
}
static void LogString(const RecordB& value)
{
printf("name=%s, pet=%s\n", value.name.c_str(), value.pet.c_str());
}
void CQueryTableB::Print()
{
std::for_each(records.begin(), records.end(), LogString);
}
VS2010工程
以上代码对应的VS2010工程如下: