您现在的位置: IOS教程網 >> IOS系統 >  >> iOS 程式開發 >> iOS 開發綜合

FMDB 入門:用 Objective-C 完成 SQLite 的增刪改查

編輯:iOS 開發綜合

FMDB 是 iOS 上最常被使用的 SQLite 封裝庫,它把 C 風格的 SQLite API 包裝成 Objective-C 物件,讓我們不必直接處理 sqlite3 指標與一堆 malloc 的記憶體細節。

一、以單例管理連線

實務上通常會包一層 manager 負責「開啟/關閉連線」,並以單例確保全 App 共用同一條連線:

@interface SDBManager : NSObject
@property (nonatomic, readonly) FMDatabase *dataBase;
+ (SDBManager *)defaultDBManager;
- (void)close;
@end

#define kDefaultDBName @"voice.sqlite"
static SDBManager *_sharedDBManager;

@implementation SDBManager

+ (SDBManager *)defaultDBManager {
    if (!_sharedDBManager) {
        _sharedDBManager = [[SDBManager alloc] init];
    }
    return _sharedDBManager;
}

// 把資料庫放在沙盒的 Documents 目錄
- (int)initializeDBWithName:(NSString *)name {
    if (!name) return -1;

    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
                                                            NSUserDomainMask, YES) objectAtIndex:0];
    _name = [docPath stringByAppendingPathComponent:name];

    BOOL exist = [[NSFileManager defaultManager] fileExistsAtPath:_name];
    [self connect];
    return exist ? 1 : 0;   // 1:已存在;0:新建
}

- (void)connect {
    if (!_dataBase) {
        _dataBase = [[FMDatabase alloc] initWithPath:_name];
    }
    if (![_dataBase open]) {
        NSLog(@"不能打開數據庫");
    }
}

- (void)close {
    [_dataBase close];
}
@end

二、建立資料表

先查詢 sqlite_master 確認表是否存在,不存在才建立,這是避免重複建表的標準寫法:

- (void)createDataBase {
    FMResultSet *set = [_db executeQuery:@"select count(*) from sqlite_master "
                       @"where type='table' and name='SUser'"];
    [set next];
    BOOL existTable = !!([set intForColumnIndex:0]);

    if (!existTable) {
        NSString *sql = @"CREATE TABLE SUser (uid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                         "name VARCHAR(50), description VARCHAR(100))";
        [_db executeUpdate:sql];
    }
}

三、新增(INSERT)

這裡刻意用 ? 佔位符搭配 withArgumentsInArray: 綁定參數,而不是把變數直接拼進 SQL 字串。這樣做能避免 SQL Injection,也能正確處理引號等特殊字元:

- (void)saveUser:(SUser *)user {
    NSMutableString *keys   = [NSMutableString stringWithString:@"("];
    NSMutableString *values = [NSMutableString stringWithString:@"("];
    NSMutableArray *arguments = [NSMutableArray array];

    if (user.name) {
        [keys appendString:@"name,"];
        [values appendString:@"?,"];
        [arguments addObject:user.name];
    }
    if (user.description) {
        [keys appendString:@"description,"];
        [values appendString:@"?,"];
        [arguments addObject:user.description];
    }

    NSString *query = [NSString stringWithFormat:@"INSERT INTO SUser %@ VALUES %@",
                       [[keys stringByReplacingOccurrencesOfString:@",)" withString:@")"]
                           stringByReplacingOccurrencesOfString:@")" withString:@""],
                       values];

    [_db executeUpdate:query withArgumentsInArray:arguments];
}

四、刪除(DELETE)

- (void)deleteUserWithId:(NSString *)uid {
    [_db executeUpdate:@"DELETE FROM SUser WHERE uid = ?", uid];
}

五、修改(UPDATE)

- (void)mergeWithUser:(SUser *)user {
    if (!user.uid) return;

    NSMutableString *temp = [NSMutableString string];
    if (user.name)        [temp appendFormat:@" name = '%@',", user.name];
    if (user.description) [temp appendFormat:@" description = '%@',", user.description];

    NSString *query = [NSString stringWithFormat:@"UPDATE SUser SET %@ WHERE uid = '%@'",
                       [temp stringByReplacingOccurrencesOfString:@",$" withString:@""],
                       user.uid];
    [_db executeUpdate:query];
}

六、查詢(SELECT)與分頁

以 uid 作為游標模擬分頁:第一次不帶 uid 取前 limit 筆,之後帶入上一頁最後一筆的 uid 繼續取:

- (NSArray *)findWithUid:(NSString *)uid limit:(int)limit {
    NSString *query = @"SELECT uid, name, description FROM SUser";
    if (!uid) {
        query = [query stringByAppendingFormat:@" ORDER BY uid DESC LIMIT %d", limit];
    } else {
        query = [query stringByAppendingFormat:@" WHERE uid > %@ ORDER BY uid DESC LIMIT %d", uid, limit];
    }

    FMResultSet *rs = [_db executeQuery:query];
    NSMutableArray *array = [NSMutableArray array];
    while ([rs next]) {
        SUser *user = [SUser new];
        user.uid         = [rs stringForColumn:@"uid"];
        user.name        = [rs stringForColumn:@"name"];
        user.description = [rs stringForColumn:@"description"];
        [array addObject:user];
    }
    [rs close];
    return array;
}

呼叫方式

SUserDB *db = [[SUserDB alloc] init];
[db createDataBase];

SUser *user = [SUser new];
user.name = @"小明";
[db saveUser:user];

NSArray *list = [db findWithUid:nil limit:20];
NSLog(@"數據數量:%lu", (unsigned long)[list count]);

小提醒:FMResultSet 用完務必 close,寫入操作若包在 [db beginTransaction] 與 [db commit] 之間,大量寫入時效能會有明顯改善。

  1. 上一篇文章:
  2. 下一篇文章:
蘋果刷機越獄教程| IOS教程問題解答| IOS技巧綜合| IOS7技巧| IOS8教程
Copyright © IOS教程網 All Rights Reserved