学习操作Sqlite时,简单封装了一个SQLiteHelper操作类。
SQLiteHelper.h
#include "cocos2d.h"
#include "sqlite3.h"
using namespace std;
USING_NS_CC;
#define DBFILENAME "NotesList.db3"
#define CREATESQL "CREATE TABLE IF NOT EXISTS Note (cdate TEXT PRIMARY KEY, content TEXT)"
class SQLiteHelper
{
private:
sqlite3* db;
public:
//获得数据库文件路径
static string dbDirectoryFile();
//初始化数据库
static int initDB();
//插入Note
static int create(char* tbName, Map<string, __String*> fieldValMap);
//删除Note
static int remove(char* tbName, Map<string, __String*> fieldValMap = Map<string, __String*>());
//更新Note
static int modify(char* tbName, Map<string, __String*> fieldValMap, Map<string, __String*> whereMap = Map<string, __String*>());
//查询所有数据
static ValueVector findAll(char* tbName, Vector<__String*> fields, Map<string, __String*> whereMap = Map<string, __String*>());
//按主键查询数据
static ValueMap finById(char* tbName, Vector<__String*> fields, Map<string, __String*> whereMap);
};
SQLiteHelper.cpp
#include "SQLiteHelper.h"
//获得数据库文件路径
string SQLiteHelper::dbDirectoryFile()
{
auto sharedFileUtils = FileUtils::getInstance();
string writablePath = sharedFileUtils->getWritablePath();
string fullPath = writablePath + DBFILENAME;
return fullPath;
}
//初始化数据库
int SQLiteHelper::initDB()
{
auto sharedFileUtils = FileUtils::getInstance();
string path = dbDirectoryFile();
bool isExists = sharedFileUtils->isFileExist(path);
if (!isExists)
{
log("NotesList.sqlite3 don't exists");
return -1;
}
sqlite3* db = NULL;
//&db函数结束时db有值回传回来
if (sqlite3_open(path.c_str(), &db) != SQLITE_OK)
{
sqlite3_close(db);
CCASSERT(false, "db open failure");
}
else
{
char* err;
//string createSQL = CREATESQL;
if (sqlite3_exec(db, CREATESQL, NULL, NULL, &err) != SQLITE_OK)
{
CCASSERT(false, "create table failure");
}
sqlite3_close(db);
}
return 0;
}
//插入Note
int SQLiteHelper::create(char* tbName, Map<string, __String*> fieldValMap)
{
initDB();
sqlite3* db = NULL;
string path = dbDirectoryFile();
if (sqlite3_open(path.c_str(), &db) != SQLITE_OK)
{
sqlite3_close(db);
CCASSERT(false, "DB open failure.");
}
else
{
string keys = "";
string values = "";
vector <string> mapKeys;
mapKeys = fieldValMap.keys();
for (auto key : mapKeys)
{
keys += key + ",";
values += "'" + static_cast<string>(fieldValMap.at(key)->getCString()) + "',";
}
keys = keys.substr(0, keys.length() - 1);
values = values.substr(0, values.length() - 1);
__String* sqlStr = __String::createWithFormat("INSERT OR REPLACE INTO %s (%s) VALUES (%s)", tbName, keys.c_str(), values.c_str());
char* err;
if (sqlite3_exec(db, sqlStr->getCString(), NULL, NULL, &err) != SQLITE_OK)
{
CCASSERT(false, "Insert Data failure.");
}
sqlite3_close(db);
}
return 0;
}
//删除Note
int SQLiteHelper::remove(char* tbName, Map<string, __String*> fieldValMap /* = Map<string, __String*>() */)
{
initDB();
sqlite3* db = NULL;
string path = dbDirectoryFile();
if (sqlite3_open(path.c_str(), &db) != SQLITE_OK)
{
sqlite3_close(db);
CCASSERT(false, "DB open failure.");
}
else
{
string values = "";
if (fieldValMap.size() > 0)
{
vector <string> mapKeys;
mapKeys = fieldValMap.keys();
for (auto key : mapKeys)
{
values += key + "='" + static_cast<string>(fieldValMap.at(key)->getCString()) + "' AND ";
}
values = " WHERE " + values.substr(0, values.length() - 5);
}
__String* sqlStr = __String::createWithFormat("DELETE FROM %s%s", tbName, values.c_str());
char* err;
if (sqlite3_exec(db, sqlStr->getCString(), NULL, NULL, &err) != SQLITE_OK)
{
CCASSERT(false, "Delete Data failure.");
}
sqlite3_close(db);
}
return 0;
}
//更新Note
int SQLiteHelper::modify(char* tbName, Map<string, __String*> fieldValMap, Map<string, __String*> whereMap /* = Map<string, __String*>() */)
{
initDB();
string path = dbDirectoryFile();
sqlite3* db = NULL;
if (sqlite3_open(path.c_str(), &db) != SQLITE_OK)
{
sqlite3_close(db);
CCASSERT(false, "DB open failure.");
}
else
{
string values = "";
vector <string> mapKeys;
mapKeys = fieldValMap.keys();
for (auto key : mapKeys)
{
values += key + "='" + static_cast<string>(fieldValMap.at(key)->getCString()) + "',";
}
values = values.substr(0, values.length() - 1);
string wheres = "";
if (whereMap.size() > 0)
{
mapKeys = whereMap.keys();
for (auto key : mapKeys)
{
wheres += key + "='" + static_cast<string>(whereMap.at(key)->getCString()) + "' AND ";
}
wheres = " WHERE " + wheres.substr(0, wheres.length() - 5);
}
__String* sqlStr = __String::createWithFormat("UPDATE %s SET %s%s", tbName, values.c_str(), wheres.c_str());
char* err;
if (sqlite3_exec(db, sqlStr->getCString(), NULL, NULL, &err) != SQLITE_OK)
{
CCASSERT(false, "Upate Data failure.");
}
sqlite3_close(db);
}
return 0;
}
//查询所有数据
ValueVector SQLiteHelper::findAll(char* tbName, Vector<__String*> fields, Map<string, __String*> whereMap /* = Map<string, __String*>() */)
{
initDB();
string path = dbDirectoryFile();
sqlite3* db = NULL;
ValueVector dataList;
if (sqlite3_open(path.c_str(), &db) != SQLITE_OK) {
sqlite3_close(db);
CCASSERT(false, "DB open failure.");
}
else
{
string wheres = "";
if (whereMap.size() > 0)
{
vector <string> mapKeys = whereMap.keys();
for (auto key : mapKeys)
{
wheres += key + "='" + static_cast<string>(whereMap.at(key)->getCString()) + "' AND ";
}
wheres = " WHERE " + wheres.substr(0, wheres.length() - 5);
}
__String* sqlStr = __String::createWithFormat("SELECT * FROM %s%s", tbName, wheres.c_str());
sqlite3_stmt *statement;
//预处理过程
if (sqlite3_prepare_v2(db, sqlStr->getCString(), -1, &statement, NULL) == SQLITE_OK)
{
while (sqlite3_step(statement) == SQLITE_ROW)
{
ValueMap dict;
for (int i = 0, length = fields.size(); i < length; i++)
{
char* val = (char*)sqlite3_column_text(statement, i);
dict[fields.at(i)->getCString()] = Value(val);
}
dataList.push_back(Value(dict));
}
}
sqlite3_finalize(statement);
sqlite3_close(db);
}
return dataList;
}
//按主键查询数据
ValueMap SQLiteHelper::finById(char* tbName, Vector<__String*> fields, Map<string, __String*> whereMap)
{
ValueVector dataList = findAll(tbName, fields, whereMap);
ValueMap vm;
if (dataList.size() > 0)
{
for (auto& v : dataList)
{
vm = v.asValueMap();
break;
}
}
return vm;
}
测试调用:
HelloWorldScene.cpp
#include "HelloWorldScene.h"
#include "SQLiteHelper.h"
USING_NS_CC;
Scene* HelloWorld::createScene()
{
// 'scene' is an autorelease object
auto scene = Scene::create();
// 'layer' is an autorelease object
auto layer = HelloWorld::create();
// add layer as a child to scene
scene->addChild(layer);
// return the scene
return scene;
}
// on "init" you need to initialize your instance
bool HelloWorld::init()
{
//
// 1. super init first
if (!Layer::init())
{
return false;
}
Size visibleSize = Director::getInstance()->getVisibleSize();
auto label1 = Label::createWithBMFont("fonts/fnt8.fnt", "Init DB");
auto label2 = Label::createWithBMFont("fonts/fnt8.fnt", "Insert Data");
auto label3 = Label::createWithBMFont("fonts/fnt8.fnt", "Delete Data");
auto label4 = Label::createWithBMFont("fonts/fnt8.fnt", "Read Data");
auto labelMn1 = MenuItemLabel::create(label1, CC_CALLBACK_1(HelloWorld::onClickInit, this));
auto labelMn2 = MenuItemLabel::create(label2, CC_CALLBACK_1(HelloWorld::onClickInsert, this));
auto labelMn3 = MenuItemLabel::create(label3, CC_CALLBACK_1(HelloWorld::onClickDel, this));
auto labelMn4 = MenuItemLabel::create(label4, CC_CALLBACK_1(HelloWorld::onClickRead, this));
auto mn = Menu::create(labelMn1, labelMn2, labelMn3, labelMn4, NULL);
mn->alignItemsVertically();
this->addChild(mn);
return true;
}
void HelloWorld::onClickInit(Ref* pSender)
{
//NoteDAO::initDB();
Map<std::string, __String*> map = Map<std::string, __String*>();
map.insert("cdate", __String::create("2014-08-04"));
//map.insert("content", __String::create("www"));
//SQLiteHelper::remove("note", map);
Map<std::string, __String*> whereMap = Map<std::string, __String*>();
whereMap.insert("cdate", __String::create("2014-08-34"));
whereMap.insert("content", __String::create("ttt"));
SQLiteHelper::modify("note", map, whereMap);
}
void HelloWorld::onClickInsert(Ref* pSender)
{
//NoteDAO::create("2015-03-08", "qqqqqqqq");
Vector<__String*> fs = Vector<__String*>();
fs.pushBack(__String::create("cdate"));
fs.pushBack(__String::create("content"));
SQLiteHelper::findAll("note", fs);
}
void HelloWorld::onClickDel(Ref* pSender)
{
}
void HelloWorld::onClickRead(Ref* pSender)
{
Vector<__String*> fields = Vector<__String*>();
fields.pushBack(__String::create("cdate"));
fields.pushBack(__String::create("content"));
Map<std::string, __String*> whereMap = Map<std::string, __String*>();
whereMap.insert("cdate", __String::create("2014-08-14"));
//whereMap.insert("content", __String::create("ttt"));
auto row = SQLiteHelper::finById("note", fields, whereMap);
string date = row["cdate"].asString();
string content = row["content"].asString();
log("===>cdate: : %s", date.c_str());
log("===>content: : %s", content.c_str());
//for (auto& v : arry)
//{
// log("============================");
// ValueMap row = v.asValueMap();
// string date = row["cdate"].asString();
// string content = row["content"].asString();
// log("===>date: : %s", date.c_str());
// log("===>content: : %s", content.c_str());
//}
}
结果:
===>cdate: : 2014-08-14
===>content: : 我是二个