cocos2dx sqlite3封装使用

/* * 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 = 2bool 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 =2int 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 instancebool 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 testif(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();}}