FMDB实战

最新推荐文章于 2018-11-17 20:06:27 发布

qskycolor

最新推荐文章于 2018-11-17 20:06:27 发布

阅读量484

点赞数

分类专栏： Objective-C 文章标签： OC iOS sqlite3 FMDB

本文链接：https://blog.csdn.net/qskycolor/article/details/48525355

版权

Objective-C 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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