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];
}
}
這裡刻意用 ? 佔位符搭配 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];
}
- (void)deleteUserWithId:(NSString *)uid {
[_db executeUpdate:@"DELETE FROM SUser WHERE uid = ?", uid];
}
- (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];
}
以 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] 之間,大量寫入時效能會有明顯改善。