FMDB實戰

FMDB對 sqlite3 的api進行了簡單封裝，支援MRC和ARC，不需要設定flag。

1. 首先，從 github 上 clone源代碼，或者用 cocoapods（推薦），并導入到項目中

2. 添加 libsqlite3.dylib 到連結庫中

這樣就可以使用FMDB來管理資料庫了。

1. 建立db

建立一個 DBManager，用來統一管理db的操作。

#import <Foundation/Foundation.h>

@interface DBManager : NSObject

+ (DBManager*)sharedInstance;
+ (void)destory;

@end
           

DBManager是一個單例。在擷取執行個體時建立db，并在第一次使用時建表。

<pre name="code" class="objc">static DBManager *sharedDBManager = nil;
static dispatch_once_t token;

@implementation DBManager

- (void)dealloc {
    DDLogDebug(@"======= DBManager DEALLOC ========");
    [_db close];
}
           

+(DBManager*)sharedInstance {
    dispatch_once(&token, ^{
        sharedDBManager = [[DBManager alloc]init];
    });
    
    return sharedDBManager;
}

+ (void)destory {
    if (sharedDBManager) {
        sharedDBManager = nil;
        token = 0;
    }
}

- (id)init {
    if (self = [super init]) {
        [self initDataBase];
    }
    
    return self;
}

- (void)initDataBase {
    NSString *dbPath = [self defaultDbPath]; // ugirls.db
    BOOL created = NO;
    if (![FCFileManager existsItemAtPath:dbPath]) {
        created = [FCFileManager createDirectoriesForFileAtPath:[self defaultDbDir]];
    }
    //_db = [[FMDatabase alloc] initWithPath:dbPath];
    _db = [FMDatabase databaseWithPath:dbPath];
    if (![_db open]) {
        DDLogError(@"open db fail : %@", dbPath);
    } else {
        if (created) {
            //第一次要建立table
            [_db beginTransaction];
            
            // read message ids
            NSString *dropReadSql = [NSString stringWithFormat:@"DROP TABLE IF EXISTS '%@';", readPublicIds];
            // create readPublicIds
            NSString *readIdsSql = [NSString stringWithFormat:@"CREATE TABLE '%@' ('msg_id' integer NOT NULL PRIMARY KEY AUTOINCREMENT);", readPublicIds];
            // delete message ids
            NSString *dropDeleteSql = [NSString stringWithFormat:@"DROP TABLE IF EXISTS '%@';", deletePublicIds];
            NSString *deleteIdsSql  = [NSString stringWithFormat:@"CREATE TABLE '%@' ('msg_id' integer NOT NULL PRIMARY KEY AUTOINCREMENT);", deletePublicIds];
            // public message models
            NSString *dropPubMsgSql = [NSString stringWithFormat:@"DROP TABLE IF EXISTS '%@';", publicMessages];
            // 以blob形式儲存消息: UGMessageModel
            NSString *pubMessageSql = [NSString stringWithFormat:@"CREATE TABLE '%@' ('msg_id' integer NOT NULL PRIMARY KEY AUTOINCREMENT, 'msg_model' blob NOT NULL);", publicMessages];
            // private messages models
            NSString *dropPriMsgSql = [NSString stringWithFormat:@"DROP TABLE IF EXISTS '%@';", privateMessages];
            // 以blob形式儲存消息: UGMessageModel
            NSString *priMessageSql = [NSString stringWithFormat:@"CREATE TABLE '%@' ('msg_id' integer NOT NULL PRIMARY KEY AUTOINCREMENT, 'user_id' integer NOT NULL, 'msg_model' TEXT NOT NULL);", privateMessages];

            [_db executeUpdate:dropReadSql];
            [_db executeUpdate:readIdsSql];
            [_db executeUpdate:dropDeleteSql];
            [_db executeUpdate:deleteIdsSql];
            [_db executeUpdate:dropPubMsgSql];
            [_db executeUpdate:pubMessageSql];
            [_db executeUpdate:dropPriMsgSql];
            [_db executeUpdate:priMessageSql];
            
            if (![_db commit]) {
                DDLogError(@"create table error %@", [_db lastError]);
                [_db close];
                [FCFileManager removeItemAtPath:[self defaultDbDir]];
            }
        }
    }
}
           

在擴充中申明db：

@interface UGDBManager () {
    FMDatabase      *_db;
}
           

資料庫的路徑：

///每個使用者一個DB目錄
- (NSString *)defaultDbDir  {
    NSString *userId = [NSString stringWithFormat:@"%lld", [LoginManager sharedInstance].userid];
    NSString *dbDir = [FCFileManager pathForDocumentsDirectoryWithPath:userId];
    return dbDir;
}

- (NSString *)defaultDbPath {
    return [[self defaultDbDir] stringByAppendingPathComponent:localDbName];
}
           

2. CRUD操作

添加一條記錄：

- (BOOL)insertOneMessage:(MessageModel *)msg{
    BOOL result = NO;
    NSString *sql = [NSString stringWithFormat:@"SELECT msg_id FROM %@ WHERE msg_id = '%@';",
                     privateMessages, [NSNumber numberWithInteger:msg.iId]];
    FMResultSet *rs = [_db executeQuery:sql];
    // if data does not exist, then insert a item
    if (![rs next]) {
        NSData *data  = [NSKeyedArchiver archivedDataWithRootObject:msg];
        NSString *base64 = [data base64EncodedStringWithOptions:NSDataBase64EncodingEndLineWithLineFeed];
        sql = [NSString stringWithFormat:@"INSERT INTO %@ (msg_id, user_id, msg_model) VALUES(%@, %@, '%@');",
               privateMessages,
               [NSNumber numberWithInteger: msg.iId],
               [NSNumber numberWithInteger: msg.iSenderId],
                base64];
        result = [_db executeUpdate:sql];
    }

    if (!result) {
        DDLogError(@"insertMsgs error: %@", [_db lastError]);
    }
    
    return result;
}
           

注意：這裡如果直接用 Blob 存儲自定義的對象在unarchive時會直接崩潰：

reason: -[__NSCFData objectForKey:]: unrecognized selector sent to instance 0x7f9e1c03d940
           

是以，這裡我先把data轉成NSString然後再存到db中，這樣就行了。而且，自定義對象要實作 NSCoding 協定裡的兩個方法：

@interface MessageModel : NSObject <NSCoding>

/**************shared message fields******************/
@property (nonatomic, assign) NSInteger iId;
@property (nonatomic, copy) NSString* sHeader;
@property (nonatomic, copy) NSString* sUri;

@end
           

@implementation MessageModel

- (id)initWithCoder:(NSCoder *)coder{
    if (self = [super initWithCoder:coder]) {
        self.sUri = [coder decodeObjectForKey:@"sUri"];
        self.sHeader = [coder decodeObjectForKey:@"sHeader"];
        self.iId = [coder decodeIntegerForKey:@"iId"];
    }
    return self;
}

- (void)encodeWithCoder:(NSCoder *)coder{
    [coder encodeObject:self.sHeader forKey:@"sHeader"];
    [coder encodeObject:self.sUri forKey:@"sUri"];
    [coder encodeInteger:self.iId forKey:@"iId"];

}
           

@end
           

删除一條記錄：

- (BOOL)deleteOneMessage:(MessageModel *)msg{
    BOOL result = NO;
    NSString *sql = [NSString stringWithFormat:@"SELECT msg_id FROM %@ WHERE msg_id = '%@';",
                     privateMessages, [NSNumber numberWithInteger:msg.iId]];
    FMResultSet *rs = [_db executeQuery:sql];
    // if data does not exist, then insert a item
    if ([rs next]) {
        sql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE msg_id = '%@';",
               privateMessages,
               [NSNumber numberWithInteger: msg.iId]];
        result = [_db executeUpdate:sql];
    }
    
    if (!result) {
        DDLogError(@"deleteprivateMsgs error: %@", [_db lastError]);
    }
    
    return result;
}
           

當删除多條記錄時，可以用事務：

- (BOOL)deleteMessages:(NSArray *)msgs{
    [_db beginTransaction];
    for (MessageModel *msg in msgs) {
        [self deletePrivateMessage:msg];
    }
    return [_db commit];
}
           

查詢記錄：

- (NSMutableArray *)fetchMessageByUserId:(NSInteger)userId{
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@;", privateMessages];
    FMResultSet *rs = [_db executeQuery:sql];
    NSMutableArray *messages = [NSMutableArray array];
    while([rs next]) {
        //NSData *data = [rs dataForColumn:@"msg_model"];
        NSString *text = [rs stringForColumn:@"msg_model"];
        NSData *data = [[NSData alloc]initWithBase64EncodedString:text options:NSDataBase64DecodingIgnoreUnknownCharacters];
        MessageModel *msg = [NSKeyedUnarchiver unarchiveObjectWithData:data];
        //NSString *ss = NSStringFromClass([msg class]);
        [messages addObject: msg];
    }
    //NSLog(@"database:messages===>%@", messages);
    return messages;
}
           

一般，executeQuery 和 executeUpdate 這兩個方法就能應對所有的操作了，隻是改改sql語句而已。

總得來說，是比apple提供的原始api友好，但是還是沒有CoreData封裝的徹底，不過比CoreData效率高。這個主要是sql語句比較繁瑣，還能繼續封裝成完全的面向對象，

不用寫sql語句，也有相關的開源庫。