sqlite3是一个简单的前端数据库，对于一些动作和前段保存数据比较多的游戏使用还是很方便
#ifndef __Sqlite3Test__DataBaseHelper__
#define __Sqlite3Test__DataBaseHelper__
#include <iostream>
#include "sqlite3.h"
#include <vector>
#include <string>
#define DBNOTFOUND INT_MAX
class DataBaseHelper
{ //=====数据库操作手柄======
public:
static DataBaseHelper* sharedDataBaseHelper();
~DataBaseHelper();
int countForTable(const char * table);
sqlite3_stmt * queryTable(const char * table, const char *fields, const char *condition, int offset=0, int count=0);
static void destroy();
void openSqliteInAndroid();
private:
DataBaseHelper();
static DataBaseHelper *dataBaseHelper;
sqlite3 *database;
};
template <class T>
class DataBaseTable
{ //数据库表类
protected:
DataBaseTable(){}
virtual void parseStatement(sqlite3_stmt *) = 0;
public:
static T findDataById(int tid)
{ //=====根据ID找到数据=======
char condition[20];
sprintf(condition, "id=%d", tid);
sqlite3_stmt * stmt = DataBaseHelper::sharedDataBaseHelper()->queryTable(T::tableName(), NULL, condition,0,1);
T t;
t._id = DBNOTFOUND;
if ((sqlite3_step(stmt)==SQLITE_ROW))
{
t.parseStatement(stmt);
}
sqlite3_finalize(stmt);
return t;
}
static T findDataByTmp(const char *tmp,int tid)
{
char condition[30];
sprintf(condition, "%s=%d",tmp,tid); //根据条件判断
sqlite3_stmt * stmt = DataBaseHelper::sharedDataBaseHelper()->queryTable(T::tableName(), NULL, condition);
T t;
// t._id = DBNOTFOUND;
if ((sqlite3_step(stmt)==SQLITE_ROW))
{
t.parseStatement(stmt);
}
sqlite3_finalize(stmt);
return t;
}
static T findDataByIdAndName(int tid, const char* name)
{ //根据ID和名字找到数据
char condition[30];
sprintf(condition, "id=%d and name=‘%s‘", tid, name);
sqlite3_stmt * stmt = DataBaseHelper::sharedDataBaseHelper()->queryTable(T::tableName(), NULL, condition);
T t;
t._id = DBNOTFOUND;
if ((sqlite3_step(stmt)==SQLITE_ROW))
{
t.parseStatement(stmt);
}
sqlite3_finalize(stmt);
return t;
}
//vector容器模板拿到数据
static std::vector<T> findData(const char *condition=NULL, int offset=0, int count=0)
{
std::vector<T> res;
sqlite3_stmt * stmt = DataBaseHelper::sharedDataBaseHelper()->queryTable(T::tableName(), NULL, condition);
while ((sqlite3_step(stmt)==SQLITE_ROW))
{
T t;
t.parseStatement(stmt);
res.push_back(t);
}
sqlite3_finalize(stmt);
return res;
}
static int count()
{ //返回数据库表的大小
return DataBaseHelper::sharedDataBaseHelper()->countForTable(T::tableName());
}
};
#endif /* defined(__Sqlite3Test__DataBaseHelper__) */
#include "DataBaseHelper.h"
#include <string>
#include "../CCFileUtils.h"
#include <stdlib.h>
#include <stdio.h>
#include "cocos2d.h"
using namespace std;
using namespace cocos2d;
DataBaseHelper *DataBaseHelper::dataBaseHelper = NULL;
DataBaseHelper::DataBaseHelper()
{
#if (CC_TARGET_PLATFORM == CC_PLATFORM_IOS)
//如果在IOS平台需要打开相应的数据库路径
std::string path = cocos2d::CCFileUtils::sharedFileUtils()->fullPathForFilename("data/gameDataBean.db");
CCLog("------%s-------------",path.c_str());
int res = sqlite3_open(path.c_str(), &database);
if (res != SQLITE_OK)
{
CCLog("-->>open db fail,error code is %d", res);
}
#elif (CC_TARGET_PLATFORM == CC_PLATFORM_ANDROID)
openSqliteInAndroid();
#endif
}
void DataBaseHelper::openSqliteInAndroid()
{
// android 系统不能对assets目录下的文件进行fopen操作，所以copy到 /data/data/包名/files/ 下面再操作
std::string path = cocos2d::CCFileUtils::sharedFileUtils()->fullPathForFilename("data/gameDataBean.db");
std::string writalePath = CCFileUtils::sharedFileUtils()->getWritablePath() + "gameDataBean.db";
unsigned long len = 0;
unsigned char *data = NULL;
data = CCFileUtils::sharedFileUtils()->getFileData(path.c_str(), "r", &len);
FILE *fp = fopen(writalePath.c_str(),"r");
if(!fp)
{
// 数据库存在的话就别再copy过去了
FILE *fp1 = fopen(writalePath.c_str(), "w+");
fwrite(data, sizeof(char), len, fp1);
fclose(fp1);
} else{
fclose(fp);
}
int res = sqlite3_open(writalePath.c_str(), &database);
if (res != SQLITE_OK)
{
CCLog("-->>open db fail,error code is %d", res);
}
}
DataBaseHelper* DataBaseHelper::sharedDataBaseHelper() {
if (!dataBaseHelper) {
dataBaseHelper = new DataBaseHelper();
::atexit(destroy);
}
return dataBaseHelper;
}
void DataBaseHelper::destroy()
{
if (dataBaseHelper)
{
delete dataBaseHelper;
}
}
DataBaseHelper::~DataBaseHelper()
{
sqlite3_close(database);
}
int DataBaseHelper::countForTable(const char * table) { //返回表的数量
char *sql = (char *)malloc(strlen(table)+22);
int count = -1;
sprintf(sql, "select count(*) from %s", table);
// 在sqlite中并没有定义sqlite3_stmt这个结构的具体内容，它只是一个抽象类型，在使用过程中一般以它的指针进行操作，
//而sqlite3_stmt类型的指针在实际上是一个指向Vdbe的结构体得指针
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL)==SQLITE_OK) { //sqlite3_prepare_v2 查询数据库接口
if (sqlite3_step(statement)==SQLITE_ROW)
{
count = sqlite3_column_int(statement, 0);
}
}
free(sql);
return count;
}
//查询表
sqlite3_stmt * DataBaseHelper::queryTable(const char * table, const char *fields, const char *condition, int offset, int count)
{
string sql = string("select ");
if (fields) {
sql.append(fields);
} else {
sql.append("*");
}
sql.append(" from ");
sql.append(table);
if (condition) {
sql.append(" where ");
sql.append(condition);
}
if (count)
{
sql.append(" limit ");
char tmp[20];
sprintf(tmp, "%d,%d", offset, count);
sql.append(tmp);
}
sql.append(";");
// sqlite3_stmt 它是一个已经把sql语句解析了的、用sqlite自己标记记录的内部数据结构。
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, sql.c_str(), -1, &statement, NULL)==SQLITE_OK) {
return statement;
}
return NULL;
}