iOS 中SQLite3的使用（Demo）

根据前两篇的文章和网上的一些资料写了一个ios中SQLite3的使用示例，下面直接贴出代码，code中会有必要的注释

实现了数据库表的创建 插入 查询 删除等功能

数据类型：

NULL

INTEGER 整形

REAL 浮点型

TEXT 文本类型

BLOB 二进制对象

———————————————————————更新——————————————————————

sqlite3_prepare() 编码方式：UTF-8

sqlite3_prepare_v2() 编码方式：UTF-8

（PS:看到有资料说sqlite3_prepare()是为了前向兼容，推荐使用sqlite3_prepare_v2()）

sqlite3_prepare_v16() 编码方式：UTF-16

———————————————————————————————————————————————

- (void)viewDidLoad{    [super viewDidLoad];    [self createStudentTable];    [self insertTable];    [self inquire];    [self deleteTable];}- (void)didReceiveMemoryWarning{    [super didReceiveMemoryWarning];}-(NSString *) databasePath{    NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);    NSString *pathname = [path objectAtIndex:0];    return [pathname stringByAppendingPathComponent:@"database.sqlite3"];}-(BOOL) opendatabase{    if (sqlite3_open([[self databasePath] UTF8String], &database) != SQLITE_OK) { //根据指定目录打开数据库文件，如果没有就创建一个新的        sqlite3_close(database);         printf("failed to open the database.\n");        return NO;    }    else {        printf("open the database successfully.\n");        return YES;    }}-(BOOL) createStudentTable{    if ([self opendatabase] == YES) {        char *erroMsg;        NSString *TableName = @"StudentTable";        NSString *createSQL = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(userid INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, math INTEGER, iconurl BLOB, virbrate INTEGER, status INTEGER, message TEXT)", TableName];//创建一个表    AUTOINCREMENT 这里userid的值是创建表是自动生成的，从1开始依次自增                if (sqlite3_exec(database, [createSQL UTF8String], NULL, NULL, &erroMsg) != SQLITE_OK) {            sqlite3_close(database);            printf("create table failed.\n");            return NO;        }        else {            printf("table was created.\n");            return YES;        }    }    else        return NO;}-(void) ErrorReport:(NSString *)item{    char *errorMsg;    if (sqlite3_exec(database, [item UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK) {        NSLog(@"%@ ok\n", item);    }    else {        printf("error: %s", errorMsg);        sqlite3_free(errorMsg);    }}-(void) insertTable{    char *errorMsg;        NSString *insertSql = @"insert into StudentTable (name) values ('Mr_Guo')"; //插入语句    if (sqlite3_exec(database, [insertSql UTF8String], NULL, NULL, &errorMsg) == SQLITE_OK) {        printf("insert ok.\n");    }    else {        printf("can't insert it to table\n");        [self ErrorReport:insertSql];    }}//查询数据库-(void) inquire{    //char *errMsg;    NSString *inquireSQL = @"select userid, name from StudentTable";    sqlite3_stmt *statement;    /*     [注：]    sqlite3_prepare_v2把一条SQL语句（这里是inquireSQL）解析到sqlite3_stmt结构中    试了一下这里使用sqlite3_prepare的结果是完全一样的    [最后面有这两个函数的实现]     */    if (sqlite3_prepare_v2(database, [inquireSQL UTF8String], -1, &statement, nil) == SQLITE_OK) {        printf("select ok.\n");        while (sqlite3_step(statement) == SQLITE_ROW) {            int _id = sqlite3_column_int(statement, 0); //这里的0是userid在sql语句中的索引，因为我们要查询的内容有userid和name，所以userid的索引为0，name的索引为1            NSString *_time = [[NSString alloc] initWithCString:(char *)sqlite3_column_text(statement, 1) encoding:NSUTF8StringEncoding];            printf("userid = %d, _time = %s.\n",_id, [_time UTF8String]);        }    }    else {        [self ErrorReport:inquireSQL];    }    sqlite3_finalize(statement);}-(void) deleteTable{    char *errMsg;    //[self opendatabase];        NSString *deleteSQL = @"delete from StudentTable where userid=4"; //删除userid为4的表    if (sqlite3_exec(database, [deleteSQL UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {        printf("delete ok.\n");    }    else {        printf("can't delete it\n");        [self ErrorReport:deleteSQL];    }}/* SQLITE_API int sqlite3_prepare(    sqlite3 *db,              // Database handle.     const char *zSql,         // UTF-8 encoded SQL statement.     int nBytes,               // Length of zSql in bytes.     sqlite3_stmt **ppStmt,    // OUT: A pointer to the prepared statement     const char **pzTail       // OUT: End of parsed string     ){        int rc;        rc = sqlite3LockAndPrepare(db,zSql,nBytes,0,0,ppStmt,pzTail);        assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 );  // VERIFY: F13021         return rc;    }SQLITE_API int sqlite3_prepare_v2(    sqlite3 *db,              // Database handle.     const char *zSql,         // UTF-8 encoded SQL statement.     int nBytes,               // Length of zSql in bytes.     sqlite3_stmt **ppStmt,    // OUT: A pointer to the prepared statement     const char **pzTail       // OUT: End of parsed string     ){        int rc;        rc = sqlite3LockAndPrepare(db,zSql,nBytes,1,0,ppStmt,pzTail);        assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 );  // VERIFY: F13021         return rc;    }*/