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语句，也有相关的开源库。