在cocos2dx中怎么使用sqlite来纪录数据

roger2001c

转发，请保持地址：http://blog.csdn.net/stalendp/article/details/8862737

花了一些时间来调查sqlite在cocos2dx中的集成，在这个过程中遇到了些问题，特意纪录下来。
　　1）下载sqlite源代码，并解压到工程中［这步不需要了，cocos2dx中已经加入了sqlite，只需引用头文件sqlite3.h就可以了］：
　　源代码位置：http://www.sqlite.org/download.html

　　减压到工程中（shell.c不需要），在xcode中如下：

　　

　　2） 打开数据库：
　　先用sqlite工具创建一个数据库，叫test.db；（工具可以使用sqlite提供的命令行工具；在sqlite下载页面中的Precompiled Binaries）；
　　把test.db拷贝到Resource文件夹下，并加入工程，如下图：

　　然后调用如下代码，打开数据库：

        std::string filename = CCFileUtils::sharedFileUtils()->fullPathForFilename("test.db");
result = sqlite3_open(filename.c_str(), &pDB);
CCLog(filename.c_str());
if( result != SQLITE_OK )
CCLog( "打开数据库失败，错误码:%d ，错误原因:%s\n" , result, errMsg );
else
CCLog("成功地打开了数据库");

3）创建表和插入数据：
        //创建表，设置ID为主键，且自动增加
result=sqlite3_exec( pDB, "create table MyTable_1( ID integer primary key autoincrement, name nvarchar(32) ) " , NULL, NULL, &errMsg );
if( result != SQLITE_OK )
CCLog( "创建表失败，错误码:%d ，错误原因:%s\n" , result, errMsg );
//插入数据
sqlstr=" insert into MyTable_1( name ) values ( '克塞' ) ";
result = sqlite3_exec( pDB, sqlstr.c_str() , NULL, NULL, &errMsg );
if(result != SQLITE_OK )
CCLog( "插入记录失败，错误码:%d ，错误原因:%s\n" , result, errMsg );
//插入数据
sqlstr=" insert into MyTable_1( name ) values ( '葫芦娃' ) ";
result = sqlite3_exec( pDB, sqlstr.c_str() , NULL, NULL, &errMsg );
if(result != SQLITE_OK )
CCLog( "插入记录失败，错误码:%d ，错误原因:%s\n" , result, errMsg );
//插入数据
sqlstr=" insert into MyTable_1( name ) values ( '擎天柱' ) ";
result = sqlite3_exec( pDB, sqlstr.c_str() , NULL, NULL, &errMsg );
if(result != SQLITE_OK )
CCLog( "插入记录失败，错误码:%d ，错误原因:%s\n" , result, errMsg );

4）查询
对数据库进行查询，有两种方式：1）sqlite3_prepare_v2 + sqlite3_step + sqlite3_column_text + sqlite3_finalize; 2) sqlite3_exec;


第一种方式，请参考​http://www.raywenderlich.com/913/sqlite-101-for-iphone-developers-making-our-app



- (NSArray *)failedBankInfos {
NSMutableArray *retval = [[[NSMutableArray alloc] init] autorelease];
NSString *query = @"SELECT id, name, city, state FROM failed_banks
ORDER BY close_date DESC";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(_database, [query UTF8String], -1, &statement, nil)
== SQLITE_OK) {
while (sqlite3_step(statement) == SQLITE_ROW) {
int uniqueId = sqlite3_column_int(statement, 0);
char *nameChars = (char *) sqlite3_column_text(statement, 1);
char *cityChars = (char *) sqlite3_column_text(statement, 2);
char *stateChars = (char *) sqlite3_column_text(statement, 3);
NSString *name = [[NSString alloc] initWithUTF8String:nameChars];
NSString *city = [[NSString alloc] initWithUTF8String:cityChars];
NSString *state = [[NSString alloc] initWithUTF8String:stateChars];
FailedBankInfo *info = [[FailedBankInfo alloc]
initWithUniqueId:uniqueId name:name city:city state:state];                        
[retval addObject:info];
[name release];
[city release];
[state release];
[info release];
}
sqlite3_finalize(statement);
}
return retval;
}
@end



第二种方式，参考​http://forum.pellesc.de/index.php?topic=3669.0



static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
NotUsed = 0;
int i;
for (i = 0; i < argc; i++)
{
printf("%s = %s\n", azColName, argv ? argv : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char **argv)
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
rc = sqlite3_open("test.db3", &db);
if (rc == SQLITE_OK)
{
rc = sqlite3_exec(db,
"CREATE TABLE test (id INTEGER NOT NULL, text VARCHAR(100))"
, callback, 0, &zErrMsg);
if (rc != SQLITE_OK) fprintf(stderr, "SQL error: %s\n", zErrMsg);
rc = sqlite3_exec(db,
"INSERT INTO test VALUES (1, 'text1')"
, callback, 0, &zErrMsg);
if (rc != SQLITE_OK) fprintf(stderr, "SQL error: %s\n", zErrMsg);
rc = sqlite3_exec(db,
"SELECT * FROM test"
, callback, 0, &zErrMsg);
if (rc != SQLITE_OK) fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_close(db);
} else {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
return 0;
}



官方文档​http://sqlite.org/cintro.html


1.1 Typical Usage Of Core Routines And Objects


An application that wants to use SQLite will typically use sqlite3_open() to create a single database connection during initialization. Note that sqlite3_open() can be used to either open existing database files or to create and open new database files. While
many applications use only a single database connection, there is no reason why an application cannot call sqlite3_open() multiple times in order to open multiple database connections - either to the same database or to different databases. Sometimes a multi-threaded
application will create separate database connections for each threads. Note too that is not necessary to open separate database connections in order to access two or more databases. A single database connection can be made to access two or more databases
at one time using the ATTACH SQL command.


Many applications destroy their database connections using calls to sqlite3_close() at shutdown. Or, for example, an application might open database connections in response to a File->Open menu action and then destroy the corresponding database connection in
response to the File->Close menu.


To run an SQL statement, the application follows these steps:


Create a prepared statement using sqlite3_prepare().

Evaluate the prepared statement by calling sqlite3_step() one or more times.

For queries, extract results by calling sqlite3_column() in between two calls to sqlite3_step().

Destroy the prepared statement using sqlite3_finalize().

The foregoing is all one really needs to know in order to use SQLite effectively. All the rest is just ornamentation and detail.


2.0 Convenience Wrappers Around Core Routines


The sqlite3_exec() interface is a convenience wrapper that carries out all four of the above steps with a single function call. A callback function passed into sqlite3_exec() is used to process each row of the result set. The sqlite3_get_table() is another
convenience wrapper that does all four of the above steps. The sqlite3_get_table() interface differs from sqlite3_exec() in that it stores the results of queries in heap memory rather than invoking a callback.


It is important to realize that neither sqlite3_exec() nor sqlite3_get_table() do anything that cannot be accomplished using the core routines. In fact, these wrappers are implemented purely in terms of the core routines.
　　

　　另外，ios上有专门针对sqlite的一个封装，叫做coreData（android也有一个java版本的封装，sqliteHelper吧）；以后有时间在研究一下。
　　

　　再附加一个开源的sqlite的工具吧：SQLite Database Browser
　　

　　参考文章：
　　请参考：​http://4137613.blog.iyunv.com/4127613/772518

　　一个解决方案：​http://stackoverflow.com/questions/10438089/using-sqlite-with-ios-very-beginner-program


两篇质量比较高的入门教程：
​http://www.raywenderlich.com/902/sqlite-101-for-iphone-developers-creating-and-scripting
​http://www.raywenderlich.com/913/sqlite-101-for-iphone-developers-making-our-app