DBUtil 对sqlite3的简单封装

/**********************************************
 * file:   DBUtil.h
 *
 * date:   2016/09/19 10:36
 *
 * TODO:   对sqlite3的简单封装


 * 注意：SQLite3 数据库使用的编码格式为：UTF-8 。如果出现乱码的话，就将其转为 UTF-8 格式！！！！！！！！！！
 *
 **********************************************
 */
#ifndef _DBUTIL_H_
#define _DBUTIL_H_


#include "cocos2d.h"
#include "sqlite3.h"
#include <vector>




USING_NS_CC;
typedef unsigned char       BYTE;
#define HN_SAFE_DELETE(p)           do { delete (p); (p) = nullptr; } while(0)
typedef std::function<void(Ref*)> onSocketMessageCallback;
struct BLOBData
{
void *pData;
int   nLen;
};


struct BLOBDataOneArg
{
BLOBData data;
};


struct BLOBDataTwoArg
{
BLOBData data[2];
};


struct BLOBDataThreeArg
{
BLOBData data[3];
};


class DBUtil : public Ref
{
public:
DBUtil();
~DBUtil();


static DBUtil * getInstance();


/************************************************************************/
/* sqlite封装                                                          */
/************************************************************************/
bool init();
CREATE_FUNC(DBUtil);
//创建一个db数据库，或打开一个数据库
bool InitDB(const char* db);


//判断表格是否存在
bool IsTableExist(std::string tablename);


//创建一个表格
bool CreateTable(std::string sql, std::string tblname);


//删除一个表格
bool DeleteTable(std::string sql, std::string tblname);


//用来执行一个语句
bool ExecSql(std::string sql);


//exec blob
//read
std::vector<BLOBDataOneArg> * GetSqlBLOBOneArg(std::string sql);
std::vector<BLOBDataTwoArg> * GetSqlBLOBTwoArg(std::string sql);
std::vector<BLOBDataThreeArg> * GetSqlBLOBThreeArg(std::string sql);
//write
//one arg
/*bool ExecSqlBlob1(std::string sql, BYTE *pData1, int nLen1);
bool ExecSqlBlob2(std::string sql, BYTE *pData1, int nLen1, BYTE *pData2, int nLen2);
bool ExecSqlBlob3(std::string sql, BYTE *pData1, int nLen1, BYTE *pData2, int nLen2, BYTE *pData3, int nLen3);*/
bool ExecSqlBlob(std::string sql);


//获取一个sql的行数
int GetDataCount(std::string sql);


//读取记录 pData,查询结果，r行号，col列
void GetDataInfoDB(std::string sql, int type = 0);
void GetDataInfo(std::string sql, char *** pData, int &row, int &col);


//读取记录 pData,查询结果，r行号，col列
void GetDataInfoValue(std::string sql, cocos2d::Value & val, int &row, int &col);


//获取结果
std::string GetResult(cocos2d::Value val, int index, const char* name);
std::string GetResultValue(int index, const char* name);
std::string GetResultPreValue(int index, const char* name);
int getResultSize();


//释放结果集合,读取记得关闭！！！！！！！
void FreeDataInfo(char**pData);


//关闭数据库
void CloseDB();


bool getSeqIsVaild();


void freeSeq();
void getData1(onSocketMessageCallback &_callback);
BYTE *getData2();
int getData1Len();
int getData2Len();
private:
sqlite3 * m_pDB;                        //数据库指针


//查询结果
cocos2d::Value m_val;
cocos2d::Value m_preVal;


char ** m_pData;
std::vector<BLOBDataTwoArg> *m_seq;
public:
int row;
int col;
//HNSocketMessage m_message;
};

#endif // !_DBUTIL_H_

#include "DBUtil.h"


struct GameOutSeq
{
BYTE cbHandCardData[4][26];
BYTE byBanker;
BYTE nCount;
BYTE bVal[1];
};


BYTE *_resultSave = nullptr;          //存储结果
int   _resultSaveLen = 0;       //结果
void saveData(void *pData1, int nLen1)
{
/*GameOutSeq * temp = (GameOutSeq *)pData1;




CCLOG("temp %d ", sizeof(*temp));
CCLOG("GameOutSeq %d ", sizeof(GameOutSeq));*/


_resultSave = (BYTE*)malloc(nLen1);
memcpy(_resultSave, pData1, nLen1);
/*GameOutSeq * temp1 = (GameOutSeq *)_resultSave;
CCLOG("temp1 %d ", sizeof(temp1));*/
_resultSaveLen = nLen1;
CCLOG("数据保存成功,data save success %d ", _resultSaveLen);
}


DBUtil * _instance = nullptr;


DBUtil * DBUtil::getInstance()
{
return _instance;
}


DBUtil::DBUtil()
: m_pDB(nullptr),
m_pData(nullptr)
{
_instance = this;
}


DBUtil::~DBUtil()
{
}


bool DBUtil::init()
{
retain();
return true;
}


//创建一个db数据库，或打开一个数据库
bool DBUtil::InitDB(const char* db)
{
CCLOG("create sqlite3 db");
int result = sqlite3_open(db, &m_pDB);


if (result != SQLITE_OK)
{
//打开数据库失败
CCLOG("打开数据失败,错误码:%d\n", result);
}
else
{
//CCLOG("数据库创建成功");
CCLOG("create success");
}


return result == SQLITE_OK;
}


//tableIsExist的回调函数
int isExisted(void * para, int n_column, char ** column_value, char ** column_name)
{
bool *isExisted_ = (bool*)para;
*isExisted_ = (**column_value) != '0';
return 0;
}


//判断表是否存在
bool DBUtil::IsTableExist(std::string tablename)
{
if (m_pDB)
{
bool tableIsExisted = false;
std::string sqlstr = "select count(type) from sqlite_master where type='table' and name ='" + tablename + "'";
char * errMsg = nullptr;
int result = sqlite3_exec(m_pDB, sqlstr.c_str(), isExisted, &tableIsExisted, &errMsg);
return tableIsExisted;
}


return false;
}


//创建一个表格
//在数据库中判断名为name的表示否存在，如果不存在则创建这张表
//@示例语句string sqls = "create table user(id integer,username text,password text)";
bool DBUtil::CreateTable(std::string sql, std::string tblname)
{
if (m_pDB == nullptr)
return false;


if (!IsTableExist(tblname))
{
//创建表
char *errmsg = nullptr;
int result = sqlite3_exec(m_pDB, sql.c_str(), nullptr, nullptr, &errmsg);


if (result != SQLITE_OK)
{
CCLOG("创建表失败，错误码:%d ，错误原因:%s\n", result, errmsg);
return false;
}
}


return true;
}


//删除表格
//@示例语句sqlstr="drop table name";
bool DBUtil::DeleteTable(std::string sql, std::string tblname)
{
if (m_pDB == nullptr)
return false;


if (IsTableExist(tblname))
{
char *errMsg = nullptr;
int result = sqlite3_exec(m_pDB, sql.c_str(), NULL, NULL, &errMsg);


if (result != SQLITE_OK)
{
CCLOG("创建表失败，错误码:%d ，错误原因:%s\n", result, errMsg);
return false;
}
}


return true;
}


//执行语句
//@示例语句sqlstr=" insert into MyTable_1( name ) values ( '擎天柱' ) ";
//@示例语句sqlstr="delete from MyTable_1 where ID = 2";
//@示例语句        sqlstr="update MyTable_1 set name='威震天' where ID = 3";
bool DBUtil::ExecSql(std::string sql)
{
if (m_pDB == nullptr)
return false;


char *errMsg = nullptr;
int result = sqlite3_exec(m_pDB, sql.c_str(), NULL, NULL, &errMsg);


if (result != SQLITE_OK)
CCLOG("执行sql失败，错误码:%d ，错误原因:%s\n", result, errMsg);


return (result == SQLITE_OK);
}








//getDataCount的回调函数
int loadRecordCount(void * para, int n_column, char ** column_value, char ** column_name)
{
int *count = (int*)para;
*count = n_column;
return 0;
}




//
//blob
std::vector<BLOBDataOneArg> * DBUtil::GetSqlBLOBOneArg(std::string sql)
{
std::vector<BLOBDataOneArg> *vBlob = new std::vector<BLOBDataOneArg>();;
sqlite3_stmt * stat2;
sqlite3_prepare(m_pDB, sql.c_str(), -1, &stat2, 0);
int result2 = sqlite3_step(stat2);


//此处取出
while (result2 == SQLITE_ROW)
{
const void * pFileContent = sqlite3_column_blob(stat2, 0);
int len = sqlite3_column_bytes(stat2, 0);
BLOBDataOneArg arg;
memset(&arg, 0, sizeof(arg));


if (pFileContent != nullptr)
{
arg.data.pData = malloc(len);
memcpy(arg.data.pData, pFileContent, len);
}


arg.data.nLen = len;
vBlob->push_back(arg);
result2 = sqlite3_step(stat2);
}


sqlite3_reset(stat2);
sqlite3_finalize(stat2); //把刚才分配的内容析构掉
return vBlob;
}


std::vector<BLOBDataTwoArg>* DBUtil::GetSqlBLOBTwoArg(std::string sql)
{
std::vector<BLOBDataTwoArg> *vBlob = new std::vector<BLOBDataTwoArg>();
sqlite3_stmt * stat2;
sqlite3_prepare(m_pDB, sql.c_str(), -1, &stat2, 0);
int result2 = sqlite3_step(stat2);


//此处取出
while (result2 == SQLITE_ROW)
{
const void * pFileContent = sqlite3_column_blob(stat2, 0);
int len = sqlite3_column_bytes(stat2, 0);
const void * pFileContent1 = sqlite3_column_blob(stat2, 1);
int len1 = sqlite3_column_bytes(stat2, 1);
BLOBDataTwoArg arg;
memset(&arg, 0, sizeof(arg));


if (pFileContent != nullptr)
{
arg.data[0].pData = malloc(len);
memcpy(arg.data[0].pData, pFileContent, len);
arg.data[0].nLen = len;
}


if (pFileContent1 != nullptr)
{
arg.data[1].pData = malloc(len1);
memcpy(arg.data[1].pData, pFileContent1, len1);
arg.data[1].nLen = len1;
}


vBlob->push_back(arg);
result2 = sqlite3_step(stat2);
}


sqlite3_reset(stat2);
sqlite3_finalize(stat2); //把刚才分配的内容析构掉
m_seq = vBlob;
return vBlob;
}


bool DBUtil::getSeqIsVaild()
{
if (m_seq == nullptr)
{
CCLOG("m_seq == nullptr");
}


if (m_seq->empty())
{
CCLOG("m_seq->empty()");
}


if (m_seq->at(0).data[0].pData == nullptr)
{
CCLOG("m_seq->at(0).data[0].pData == nullptr");
}


if (m_seq->at(0).data[0].nLen <= 0)
{
CCLOG(" m_seq->at(0).data[0].nLen <= 0");
}


if (m_seq == nullptr || m_seq->empty() || m_seq->at(0).data[0].pData == nullptr ||  m_seq->at(0).data[0].nLen <= 0)
{
return false;
}


return true;
}


void DBUtil::freeSeq()
{
HN_SAFE_DELETE(m_seq);
}


std::vector<BLOBDataThreeArg> *DBUtil::GetSqlBLOBThreeArg(std::string sql)
{
std::vector<BLOBDataThreeArg> *vBlob = new std::vector<BLOBDataThreeArg>();
sqlite3_stmt * stat2;
sqlite3_prepare(m_pDB, sql.c_str(), -1, &stat2, 0);
int result2 = sqlite3_step(stat2);


//此处取出
while (result2 == SQLITE_ROW)
{
const void * pFileContent = sqlite3_column_blob(stat2, 0);
int len = sqlite3_column_bytes(stat2, 0);
const void * pFileContent1 = sqlite3_column_blob(stat2, 1);
int len1 = sqlite3_column_bytes(stat2, 1);
const void * pFileContent2 = sqlite3_column_blob(stat2, 2);
int len2 = sqlite3_column_bytes(stat2, 2);
BLOBDataThreeArg arg;
memset(&arg, 0, sizeof(arg));


if (pFileContent != nullptr)
{
arg.data[0].pData = malloc(len);
memcpy(arg.data[0].pData, pFileContent, len);
arg.data[0].nLen = len;
}


if (pFileContent1 != nullptr)
{
arg.data[1].pData = malloc(len1);
memcpy(arg.data[1].pData, pFileContent1, len1);
arg.data[1].nLen = len1;
}


if (pFileContent2 != nullptr)
{
arg.data[2].pData = malloc(len2);
memcpy(arg.data[2].pData, pFileContent2, len2);
arg.data[2].nLen = len2;
}


vBlob->push_back(arg);
result2 = sqlite3_step(stat2);
}


sqlite3_reset(stat2);
sqlite3_finalize(stat2); //把刚才分配的内容析构掉
return vBlob;
}


bool DBUtil::ExecSqlBlob(std::string sql)
{
if (m_pDB == nullptr || _resultSave == nullptr)
{
CCLOG("数据错误,保存失败error-------");
return false;
}


sqlite3_stmt * stat;
sqlite3_prepare(m_pDB, sql.c_str(), -1, &stat, 0);
sqlite3_bind_blob(stat, 1, _resultSave, _resultSaveLen, NULL);
int result = sqlite3_step(stat);
sqlite3_finalize(stat); //把刚才分配的内容析构掉


_resultSave = nullptr;
_resultSaveLen = 0;
return true;
}


//bool DBUtil::ExecSqlBlob1(std::string sql, BYTE *pData1, int nLen1)
//{
// if (m_pDB == nullptr || pData1 == nullptr)
// return false;


// sqlite3_stmt * stat;
// sqlite3_prepare(m_pDB, sql.c_str(), -1, &stat, 0);
// sqlite3_bind_blob(stat, 1, pData1, nLen1, NULL);
// int result = sqlite3_step(stat);
// sqlite3_finalize(stat); //把刚才分配的内容析构掉
// return true;
//}


//bool DBUtil::ExecSqlBlob2(std::string sql, BYTE *pData1, int nLen1, BYTE *pData2, int nLen2)
//{
// if (m_pDB == nullptr || pData1 == nullptr || pData2 == nullptr)
// return false;


// sqlite3_stmt * stat;
// sqlite3_prepare(m_pDB, sql.c_str(), -1, &stat, 0);
// sqlite3_bind_blob(stat, 1, pData1, nLen1, NULL);
// sqlite3_bind_blob(stat, 2, pData2, nLen2, NULL);
// int result = sqlite3_step(stat);
// sqlite3_finalize(stat); //把刚才分配的内容析构掉
// return true;
//}


//bool DBUtil::ExecSqlBlob3(std::string sql, BYTE *pData1, int nLen1, BYTE *pData2, int nLen2, BYTE *pData3, int nLen3)
//{
// if (m_pDB == nullptr)
// return false;


// sqlite3_stmt * stat;
// sqlite3_prepare(m_pDB, sql.c_str(), -1, &stat, 0);
// sqlite3_bind_blob(stat, 1, pData1, nLen1, NULL);
// sqlite3_bind_blob(stat, 2, pData2, nLen2, NULL);
// sqlite3_bind_blob(stat, 3, pData3, nLen3, NULL);
// int result = sqlite3_step(stat);
// sqlite3_finalize(stat); //把刚才分配的内容析构掉
// return true;
//}


//


//获取记录的条数
//@示例语句string sqlsssss = "select count(*) from user";
//@示例语句  取得表格字段的语句string sqlsssss = "select * from user";
int DBUtil::GetDataCount(std::string sql)
{
int count = 0;


if (m_pDB)
{
char * errMsg = nullptr;
int result = sqlite3_exec(m_pDB, sql.c_str(), loadRecordCount, &count, &errMsg);


if (result != SQLITE_OK)
{
CCLOG("执行sql失败，错误码:%d ，错误原因:%s\n", result, errMsg);
return 0;
}
}


return count;
}


void DBUtil::GetDataInfoDB(std::string sql, int type)
{
if (type == 0)
{
GetDataInfoValue(sql, m_val, row, col);
}
else
{
m_pData = nullptr;
GetDataInfo(sql, &m_pData, row, col);
}
}
// pData 的字段值是连续的，从第0索引到第 nColumn - 1索引都是字段名称，从第 nColumn 索引开始，后面都是字段值，它把一个二维的表（传统的行列表示法）用一个扁平的形式来表示
void DBUtil::GetDataInfo(std::string sql, char *** pData, int &row, int &col)
{
if (m_pDB)
{
char * errMsg = NULL;
sqlite3_get_table(m_pDB, sql.c_str(), pData, &row, &col, &errMsg); //1


if (pData)
{
cocos2d::ValueVector vc;


for (int i = 1; i <= row; ++i)
{
cocos2d::ValueMap tmpVal;


for (int j = 0; j < col; ++j)
{
tmpVal.insert(std::make_pair(m_pData[j], cocos2d::Value(std::string(m_pData[i * col + j]))));
}


vc.push_back(cocos2d::Value(tmpVal));
}


m_preVal = m_val;
m_val = vc;
FreeDataInfo(m_pData);
}
}
}


//val as vector ,element as valuemap
void DBUtil::GetDataInfoValue(std::string sql, cocos2d::Value & val, int &row, int &col)
{
if (m_pDB)
{
char * errMsg = NULL;
char ** pValue = nullptr;
char *** pData = &pValue;
sqlite3_get_table(m_pDB, sql.c_str(), pData, &row, &col, &errMsg); //1


if (pData)
{
cocos2d::ValueVector vc;


for (int i = 1; i <= row; ++i)
{
cocos2d::ValueMap tmpVal;


for (int j = 0; j < col; ++j)
{
tmpVal.insert(std::make_pair(pValue[j], cocos2d::Value(std::string(pValue[i * col + j]))));
}


vc.push_back(cocos2d::Value(tmpVal));
}


m_preVal = val;
val = vc;
FreeDataInfo(pValue);
}
}
}


std::string DBUtil::GetResult(cocos2d::Value val, int index, const char* name)
{
if (val.asValueVector().size() <= 0)
{
CCLOG("val size is 0 error");
return "";
}
return (val.asValueVector()[index].asValueMap()[name]).asString();
}

std::string DBUtil::GetResultValue(int index, const char* name)
{
CCLOG("m_val.asValueVector()  size %d", m_val.asValueVector().size());
if (m_val.asValueVector().size() <= 0)
{
CCLOG("m_val size is 0 error");
return "";
}
return (m_val.asValueVector()[index].asValueMap()[name]).asString();
}


std::string DBUtil::GetResultPreValue(int index, const char* name)
{
CCLOG("m_preVal.asValueVector()  size %d", m_preVal.asValueVector().size());
if (m_preVal.asValueVector().size() <= 0)
{
CCLOG("m_preVal size is 0 error");
return "";
}
return (m_preVal.asValueVector()[index].asValueMap()[name]).asString();
}


int DBUtil::getResultSize()
{
return m_val.asValueVector().size();
}


void DBUtil::FreeDataInfo(char**pData)
{
if (pData)
{
sqlite3_free_table(pData);
}
}


void DBUtil::CloseDB()
{
if (m_pDB)
{
sqlite3_close(m_pDB);
}
}

void DBUtil::getData1(onSocketMessageCallback &_callback)
{
//memcpy(m_message.object, m_seq->at(0).data[0].pData, getData1Len());
//m_message.p = m_message.object;
//GameOutSeq * temp = (GameOutSeq *)m_message.object;
//CCLOG("temp %d ", sizeof(*temp));
//CCLOG("GameOutSeq %d ", sizeof(GameOutSeq));
//_callback(&m_message);
//return (BYTE*)(m_seq->at(0).data[0].pData);
}


BYTE *DBUtil::getData2()
{
return (BYTE*)(m_seq->at(0).data[1].pData);
}


int DBUtil::getData1Len()
{
return m_seq->at(0).data[0].nLen;
}
int DBUtil::getData2Len()
{
return m_seq->at(0).data[1].nLen;
}