本來應用裡很多代碼，都是用原生的sqlite3 API，確實感到很不方便，API極不友好。昨天看到唐巧的博客，知道了FMDB，試用一下果然不錯，記錄一下

這個開源項目的github地址是：FMDB

安裝最方便的方式是用CocoaPods來安裝，見官方文檔。FMDB把SQL操作分為update和query，所以API不是executeUpdate，就是executeQuery，下面是幾個簡單的例子：

-(void) clearDeleteRecordForTable:(NSString*)tableName withRecords:(NSMutableArray*)deleteIds
{
    NSString *dbFilePath = [YLSGlobalUtils getDatabaseFilePath];
    FMDatabase *db = [FMDatabase databaseWithPath:dbFilePath];
    
    NSString *sql = @"delete from tb_deleteRecord where table_name = ? and id = ?";
    
    [db open];
    for(NSString *deleteId in deleteIds){
        [db executeUpdate:sql, tableName, deleteId];
    }
    [db close];
}

這個是executeUpdate的例子，挺簡單的。下面是一個executeQuery的例子：

-(NSMutableArray*) queryDeleteData:(NSString*)tableName
{
    NSMutableArray *result = [NSMutableArray new];
    
    NSString *dbFilePath = [YLSGlobalUtils getDatabaseFilePath];
    FMDatabase *db = [FMDatabase databaseWithPath:dbFilePath];
    
    [db open];
    FMResultSet *rs = [db executeQuery:@"select id from tb_deleteRecord where upper(table_name) = upper(?)", tableName];
    while ([rs next]) {
        [result addObject:[rs objectForColumnName:@"id"]];
    }
    [db close];
    
    return result;
}

值得注意的是，以前用原生sqlite3 API，拼接sql語句的時候，placeholder用的是%@，但這個方式是FMDB所反對的：

Thus, you SHOULD NOT do this (or anything like this):

[db executeUpdate:[NSString stringWithFormat:@"INSERT INTO myTable VALUES (%@)", @"this has \" lots of ' bizarre \" quotes '"]];

Instead, you SHOULD do:

[db executeUpdate:@"INSERT INTO myTable VALUES (?)", @"this has \" lots of ' bizarre \" quotes '"];

總的來說，FMDB不推薦使用%@拼接sql，而是要求使用?或者:name來占位。這點我很喜歡，因為我本來寫的N個bug，都是因為在sql裡忘記加引號造成的，比如說：

@"select * from table where name = %@"

這個sql就寫錯了，應該寫成：

@"select * from table where name = '%@'"

非常麻煩，一不小心寫漏了SQL執行就會報錯，現在用FMDB，錯誤的幾率就減小了很多。另外FMResultSet的objectForColumnName等方法也十分方便。下面還有一個更方便的：

-(NSMutableArray*) queryUpdateData:(NSString*)tableName
{
    NSMutableArray *result = [NSMutableArray new];
    
    NSString *dbFilePath = [YLSGlobalUtils getDatabaseFilePath];
    FMDatabase *db = [FMDatabase databaseWithPath:dbFilePath];
    
    [db open];
    long now = [[NSDate date] timeIntervalSince1970];
    FMResultSet *rs = [db executeQuery:@"select * from %@ where modify_date between ? and ? and (create_date not between ? and ?)", tableName, latestBackupTime, now, latestBackupTime, now];
    while ([rs next]) {
        [result addObject:[rs resultDictionary]];
    }
    [db close];
    
    return result;
}

就是[FMResultSet resultDictionary]方法，可以直接將一行的記錄轉換成NSDictionary，比原生sqlite3 API好用很多

總的來說，FMDB的API很直觀，也很方便。項目切換到FMDB的成本也非常低，強烈推薦試一下