欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

cocos2dx sqlite3封装使用

程序员文章站 2022-05-15 23:53:14
...
DB.h


/*
* DB.h
*
* Created on: 2013-6-8
* Author: zhuang
*/


#ifndef _DB_H_
#define _DB_H_
#include "cocos2d.h"
// DB
#include "sqlite3.h"

using namespace cocos2d;
using namespace std;

class DB
{
public:
DB();
~DB();

static DB* sharedDB();

sqlite3 *pDB;//数据库指针
std::string sqlstr;//SQL指令
char * errMsg;//错误信息
int results;//sqlite3_exec返回值

bool OpenDBWithFileName(char *dbName);
bool CreateTableWithContent(char *dbExec);
bool IsTableExistedWithTableName(std::string dbExec);
bool GetTableDataWithContent(std::string dbExec);
bool InsertTableDataWithContent(std::string dbExec);
bool DeleteTableDataWithContent(std::string dbExec);
bool UpdateTableDataWithContent(std::string dbExec);
bool ClearTableData(std::string dbExec);
void CloseDB();

void DeleteTable(string sql,string name );


int GetPlayerInfoScores(std::string dbExec);
bool GetPassInfoIsUnlockedWithIndex(std::string dbExec);
int GetPassInfoStartsWithIndex(std::string dbExec);


};
#endif




DB.cpp


#include "DB.h"


DB::DB()
{

//=================DB========================[
pDB =NULL;//数据库指针
sqlstr="";//SQL指令
errMsg = NULL;//错误信息
results=-1;//sqlite3_exec返回值


}

DB::~DB()
{
}


DB* DB::sharedDB()
{
static DB sharedDb;
return &sharedDb;
}


/*
* //在数据库中判断名为name的表示否存在,如果不存在则创建这张表
//@示例语句string sqls = "create table user(id integer,username text,password text)";
*
* //删除表格
//@示例语句sqlstr="drop table name";
*
* **/


//====================================================================
//============================ 数据库 ====================================
//====================================================================

//打开一个数据库,如果该数据库不存在,则创建一个数据库文件
/*
* data.db
* */
bool DB::OpenDBWithFileName(char *dbName)
{
bool success=false;
std::string path = CCFileUtils::sharedFileUtils()->getWritablePath()
+ dbName;
int result = sqlite3_open(path.c_str(), &pDB);
if( result != SQLITE_OK )
{
CCLog("SQLITE_OK:%d",SQLITE_OK);
CCLog( "open db failed ,error :%d ,cause: %s " , result, errMsg );
success=false;
}else
{
CCLog( "open db success ");
success=true;
}

return success;
}


//创建表,设置ID为主键,且自动增加
//create table playerinfo( ID integer primary key autoincrement, playername nvarchar(32),playerscores int )
bool DB::CreateTableWithContent(char *dbExec)
{
bool success=false;

int result=sqlite3_exec( pDB, dbExec , NULL, NULL, &errMsg );
if( result != SQLITE_OK )
{
CCLog( "create table failed ,error :%d ,cause: %s " , result, errMsg );
success=false;
}
else
{
CCLog( "create table success ");
success=true;
}

return success;
}


//判断表是否存在

bool DB::IsTableExistedWithTableName(std::string dbExec)
{
bool success=false;

std::string dbExecs="";
dbExecs.append("select count(type) from sqlite_master where type='table' and name='");
dbExecs.append(dbExec);
dbExecs.append("'");

int result=sqlite3_exec( pDB, dbExecs.c_str() , NULL, NULL, &errMsg );
if( result != SQLITE_OK )
{
CCLog( "table not exist ");
success=false;
}
else
{
CCLog( "table is existed ");
success=true;
}

return success;
}
int isExisted( void * para, int n_column, char ** column_value, char ** column_name )
{
bool *isExisted_=(bool*)para;
*isExisted_=(**column_value)!='0';
return 0;
}


// 获取数据
bool DB::GetTableDataWithContent(std::string dbExec)
{
bool success=false;

int result = sqlite3_exec( pDB, dbExec.c_str() , NULL, NULL, &errMsg ); // loadRecord
if(result != SQLITE_OK )
{
CCLog( "get GetTableDataWithContent failed,error :%d ,cause:%s " , result, errMsg );
success=false;
}
else
{
CCLog( "get GetTableDataWithContent success ");
success=true;
}

return success;
}


//插入数据
//insert into playerinfo( playername,playerscores ) values ( '忘川之水', 683500 )
bool DB::InsertTableDataWithContent(std::string dbExec)
{
bool success=false;

int result = sqlite3_exec( pDB, dbExec.c_str() , NULL, NULL, &errMsg );
if(result != SQLITE_OK )
{
CCLog( "insert failed,error :%d ,cause:%s " , result, errMsg );
success=false;
}
else
{
CCLog( "insert success ");
success=true;
}

return success;
}

//删除数据 delete from playerinfo where playername = 'default2'
bool DB::DeleteTableDataWithContent(std::string dbExec)
{
bool success=false;

int result = sqlite3_exec( pDB, dbExec.c_str() , NULL, NULL, &errMsg );
if(result != SQLITE_OK )
{
CCLog( "delete failed,error :%d ,cause:%s " , result, errMsg );
success=false;
}
else
{
CCLog( "delete success ");
success=true;
}

return success;
}


//更新数据 update gamepass set passisunlocked=1 where passindex = 2
bool DB::UpdateTableDataWithContent(std::string dbExec)
{
bool success=false;

int result = sqlite3_exec( pDB, dbExec.c_str() , NULL, NULL, &errMsg );
if(result != SQLITE_OK )
{
CCLog( "update failed,error :%d ,cause:%s " , result, errMsg );
success=false;
}
else
{
CCLog( "update success ");
success=true;
}

return success;
}

// 清空数据
bool DB::ClearTableData(std::string dbExec)
{
bool success=false;

std::string dbExecs="";
dbExecs.append("delete from ");
dbExecs.append(dbExec);
dbExecs.append(" ");

int result = sqlite3_exec( pDB, dbExecs.c_str() , NULL, NULL, &errMsg );
if(result != SQLITE_OK )
{
CCLog( "clear failed,error:%d ,cause :%s " , result, errMsg );
success=false;
}
else
{
CCLog( " clear db success ");
success=true;
}

return success;
}

//关闭数据库
void DB::CloseDB()
{
sqlite3_close(pDB);
}


//=================================================
int DB::GetPlayerInfoScores(std::string dbExec)
{
bool success=false;
int scores=0;

sqlite3_stmt *statement=NULL;
int result = sqlite3_prepare(pDB, dbExec.c_str() , dbExec.length(), &statement, 0);
if(result != SQLITE_OK )
{
CCLog( "get GetPlayerInfo failed,error :%d ,cause:%s " , result, errMsg );
success=false;
}
else
{
CCLog( "get GetPlayerInfo success ");
success=true;

while(sqlite3_step(statement) == SQLITE_ROW)
{
scores=sqlite3_column_int(statement, 2);
};

}

return scores;
}

bool DB::GetPassInfoIsUnlockedWithIndex(std::string dbExec)
{
bool success=false;
bool isUnlocked=false;

sqlite3_stmt *statement=NULL;
int result = sqlite3_prepare(pDB, dbExec.c_str() , dbExec.length(), &statement, 0);
if(result != SQLITE_OK )
{
CCLog( "get GetPlayerInfo failed,error :%d ,cause:%s " , result, errMsg );
success=false;
}
else
{
CCLog( "get GetPlayerInfo success ");
success=true;

while(sqlite3_step(statement) == SQLITE_ROW)
{
(sqlite3_column_int(statement, 3)==1)?(isUnlocked=true):(isUnlocked=false);
};

}

return isUnlocked;
}

//select * from gamepass where passindex =2
int DB::GetPassInfoStartsWithIndex(std::string dbExec)
{
bool success=false;
int starts=0;

sqlite3_stmt *statement=NULL;
int result = sqlite3_prepare(pDB, dbExec.c_str() , dbExec.length(), &statement, 0);
if(result != SQLITE_OK )
{
CCLog( "get GetPlayerInfo failed,error :%d ,cause:%s " , result, errMsg );
success=false;
}
else
{
CCLog( "get GetPlayerInfo success ");
success=true;

while(sqlite3_step(statement) == SQLITE_ROW)
{
starts=sqlite3_column_int(statement, 3);
};

}

return starts;
}

//@示例语句sqlstr="drop table name";
void DB::DeleteTable(string sql,string name){

if (IsTableExistedWithTableName(name))
{
int result = sqlite3_exec(pDB,sql.c_str(),NULL,NULL,&errMsg);
if( result != SQLITE_OK )
CCLog( "创建表失败,错误码:%d ,错误原因:%s\n" , result, errMsg );
}
}



test


#include "HelloWorldScene.h"
#include "SimpleAudioEngine.h"
#include "DB.h"

using namespace cocos2d;
using namespace CocosDenshion;

CCScene* HelloWorld::scene()
{
// 'scene' is an autorelease object
CCScene *scene = CCScene::create();

// 'layer' is an autorelease object
HelloWorld *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 ( !CCLayer::init() )
{
return false;
}
this->db();
/////////////////////////////
// 2. add a menu item with "X" image, which is clicked to quit the program
// you may modify it.

// add a "close" icon to exit the progress. it's an autorelease object
CCMenuItemImage *pCloseItem = CCMenuItemImage::create(
"CloseNormal.png",
"CloseSelected.png",
this,
menu_selector(HelloWorld::menuCloseCallback) );
pCloseItem->setPosition( ccp(CCDirector::sharedDirector()->getWinSize().width - 20, 20) );

// create menu, it's an autorelease object
CCMenu* pMenu = CCMenu::create(pCloseItem, NULL);
pMenu->setPosition( CCPointZero );
this->addChild(pMenu, 1);

/////////////////////////////
// 3. add your codes below...

// add a label shows "Hello World"
// create and initialize a label
CCLabelTTF* pLabel = CCLabelTTF::create("Hello World", "Thonburi", 34);

// ask director the window size
CCSize size = CCDirector::sharedDirector()->getWinSize();

// position the label on the center of the screen
pLabel->setPosition( ccp(size.width / 2, size.height - 20) );

// add the label as a child to this layer
this->addChild(pLabel, 1);

// add "HelloWorld" splash screen"
CCSprite* pSprite = CCSprite::create("HelloWorld.png");

// position the sprite on the center of the screen
pSprite->setPosition( ccp(size.width/2, size.height/2) );

// add the sprite as a child to this layer
this->addChild(pSprite, 0);

return true;
}

void HelloWorld::menuCloseCallback(CCObject* pSender)
{
CCDirector::sharedDirector()->end();

#if (CC_TARGET_PLATFORM == CC_PLATFORM_IOS)
exit(0);
#endif
}

void HelloWorld::db(){
// DB test
if(DB::sharedDB()->OpenDBWithFileName("save.db")) //打开一个数据库,如果该数据库不存在,则创建一个数据库文件
{
//创建表,设置ID为主键,且自动增加 ———— OK
DB::sharedDB()->CreateTableWithContent("create table playerinfo( ID integer primary key autoincrement, playername nvarchar(32),playerscores int ) ");
DB::sharedDB()->CreateTableWithContent("create table gamepass( ID integer primary key autoincrement, passindex int, passstarts int ,passisunlocked int ) ");

//插入数据 ———— OK
DB::sharedDB()->InsertTableDataWithContent(" insert into playerinfo( playername,playerscores ) values ( '北京', 683500 ) ");
DB::sharedDB()->InsertTableDataWithContent(" insert into playerinfo( playername,playerscores ) values ( '上海', 445555 ) ");
DB::sharedDB()->InsertTableDataWithContent(" insert into playerinfo( playername,playerscores ) values ( '深圳', 8556548 ) ");

DB::sharedDB()->InsertTableDataWithContent(" insert into gamepass( passindex,passstarts,passisunlocked ) values ( 1, 2, 1 ) ");
DB::sharedDB()->InsertTableDataWithContent(" insert into gamepass( passindex,passstarts,passisunlocked ) values ( 2, 3, 0 ) ");
DB::sharedDB()->InsertTableDataWithContent(" insert into gamepass( passindex,passstarts,passisunlocked ) values ( 3, 0, 0 ) ");

// 获取数据 ———— OK
int scores=DB::sharedDB()->GetPlayerInfoScores(" select * from playerinfo where playername ='default' ");
int starts=DB::sharedDB()->GetPassInfoStartsWithIndex(" select * from gamepass where passindex =2 ");
bool isLocked1=DB::sharedDB()->GetPassInfoIsUnlockedWithIndex(" select * from gamepass where passindex =1 ");
bool isLocked3=DB::sharedDB()->GetPassInfoIsUnlockedWithIndex(" select * from gamepass where passindex =3 ");
CCLog("= %d =",scores);
CCLog("= %d =",starts);
(isLocked1==true)?( CCLog("= has unlock =")):(CCLog("= is locked ="));
(isLocked3==true)?( CCLog("= has unlock =")):(CCLog("= is locked ="));


DB::sharedDB()->DeleteTable("drop table gamepass","gamepass");
// 删除数据 ———— OK
//DB::sharedDB()->DeleteTableDataWithContent("delete from playerinfo where playername = 'default2' ");

// 更新数据 ———— OK
//DB::sharedDB()->UpdateTableDataWithContent("update gamepass set passisunlocked=1 where passindex = 2 ");


//关闭数据库 ———— OK
DB::sharedDB()->CloseDB();
}
}



参考:http://blog.csdn.net/ym19860303/article/details/8531998
http://blog.sina.com.cn/s/blog_6b154dd301012ann.html
相关标签: sqlite