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

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

 

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

标签：