SQLite数据库---将复杂对象存入数据库

1.插入数据---这里BOOK是一个书类

#pragma makr 插入数据
- (void)insetIntoTableWithID:(int)nameID withName:(NSString *)name withSex:(NSString *)sex withBook:(Book *)abook
{

    // 对abook进行归档（先归档）
    NSMutableData *data = [NSMutableData data];
    NSKeyedArchiver *archiver = [[NSKeyedArchiver alloc] initForWritingWithMutableData:data];
    [archiver encodeObject:abook forKey:abook.bookName]; // abook.bookName***key不能一样否则会覆盖
    [archiver finishEncoding];
    // sql语句
    NSString *sqlString = [NSString stringWithFormat:@"INSERT INTO 'user_hh'('id','name','sex','book')VALUES(?, ?, ?,?)"];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(db, [sqlString UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        // 绑定字段
        // sql里面写了字段，字段从一开始
        sqlite3_bind_int(stmt, 1, nameID);
        sqlite3_bind_text(stmt, 2, [name UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3, [sex UTF8String], -1, NULL);
        sqlite3_bind_blob(stmt, 4, [data bytes], (int)[data length], NULL);
        // 执行
        sqlite3_step(stmt);
    }
    // 结束
    sqlite3_finalize(stmt);

    // 插入语句
    /*
     NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO 'user_hh'('id','name','sex','book')VALUES('%d', '%@', '%@','%@')",nameID,name,sex,data];
    // 执行SQL语句
    int result = sqlite3_exec(db, [insertSql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else{
    
        NSLog(@"插入失败");
    }
     */
  
}

2.查询数据库

#pragma mark 查询数据库
- (void)selectDataFromTable
{

    NSString *selectSql = [NSString stringWithFormat:@"SELECT *FROM 'user_hh'"];
   
    // 保存查询到的结果集
    sqlite3_stmt *stmt = nil;
    // 准备查询数据(预存取)
    int result = sqlite3_prepare(db, [selectSql UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        
        // 判断是否是最后一行，有没有必要继续下去
        // 这里用while循环 一行一行执行 ******不用if******
        while(sqlite3_step(stmt) == SQLITE_ROW) {
            // 拿出各列的数据
            
            // 1.拿出id列的数据
            int numberID = sqlite3_column_int(stmt, 0);
            
            // 2.拿出name列的数据
            const unsigned char *nameChar = sqlite3_column_text(stmt, 1);
            NSString *name = [NSString stringWithUTF8String:(const char *)nameChar];
            
            // 3.拿出sex列的数据
            const unsigned char *sexChar = sqlite3_column_text(stmt, 2);
            NSString *sex = [NSString stringWithUTF8String:(const char *)sexChar];
            
            // 4.拿出BOOK列   ******桥接******
            
            // NSData *data = (__bridge NSData *)(sqlite3_column_blob(stmt, 3));
            const void *bytes = (sqlite3_column_blob(stmt, 3));
            int length = sqlite3_column_bytes(stmt, 3);
            NSData *data = [[NSData alloc] initWithBytes:bytes length:length];
            
            // 反归档
            NSKeyedUnarchiver *unArchiver = [[NSKeyedUnarchiver alloc] initForReadingWithData:data];
            Book *thisBook = [unArchiver decodeObjectForKey:@"西游记"];
            [unArchiver finishDecoding]; // 结束反归档
            
            NSLog(@"%d %@ %@ %@",numberID,name,sex,thisBook.bookName);
        }
        
        // 结束查询 --- 重要 ****** 否则无法关闭数据库******
        sqlite3_finalize(stmt);
    }
}

3.这里BOOK类对属性需要编码和反编码（NSCoding协议）

-(void)encodeWithCoder:(NSCoder *)aCoder
{

    [aCoder encodeObject:self.bookName forKey:@"bookName"];
    
}
-(id)initWithCoder:(NSCoder *)aDecoder
{

    self = [super init];
    if (self) {
        self.bookName = [aDecoder decodeObjectForKey:@"bookName"];
    }
    return self;
}