cocos2d-x 3.2 Sqlite封装类（一）

最新推荐文章于 2020-04-14 16:04:32 发布

球球_2014

最新推荐文章于 2020-04-14 16:04:32 发布

阅读量1.2k

点赞数

分类专栏： cocos2d-x 文章标签： cocos2d-x sqlite

本文链接：https://blog.csdn.net/qhg2014/article/details/38568717

版权

cocos2d-x 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

学习操作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: : 我是二个