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