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

 

SQLite資料庫---將複雜物件存入資料庫