- /*
- * 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();
- }
- }