iOS FMDB小结

最新推荐文章于 2022-07-20 16:29:24 发布

guaHa

最新推荐文章于 2022-07-20 16:29:24 发布

阅读量469

点赞数

CC 4.0 BY-SA版权

分类专栏： iOS 文章标签： iOS 数据库 FMDB

本文链接：https://blog.youkuaiyun.com/rpf2014/article/details/53088629

iOS 专栏收录该内容

69 篇文章

订阅专栏

作为iOS传统的数据库封装工具库FMDB，大家都很熟悉，原来也用过，主要没有系统的总结，这次总结一下吧，其实是看了好几篇别人的日志然后自己再加工了一下。

首先是库的导入有两种选择

1 从gitHub上下载 https://github.com/ccgus/fmdb 然后导入系统库 libSqlite3.tbd

2 pod上面下载 pod 'FMDB'

然后是使用

数据库最基本的操作就是大家所知道的增删改查

1 数据库作为整个项目中都可能使用的本地存储方案，可以用一个单例来创建全局共享

+ (DataBaseManager *)sharedDatabaseManager{
    static DataBaseManager *manager = nil;
    @synchronized(self){
        if (manager == nil) {
            manager = [[self alloc] init];
        }
    }
    return manager;
}

2 因为数据库要保存在本地，一定要有路径

-(NSString *)getDBPath{
    NSString* docsdir = [NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSFileManager *filemanage = [NSFileManager defaultManager];
    docsdir = [docsdir stringByAppendingPathComponent:@"FMDBDemo"];
    BOOL isDir;
    BOOL exit =[filemanage fileExistsAtPath:docsdir isDirectory:&isDir];
    if (!exit || !isDir) {
        [filemanage createDirectoryAtPath:docsdir withIntermediateDirectories:YES attributes:nil error:nil];
    }
    NSString *dbpath = [docsdir stringByAppendingPathComponent:@"work.sqlite"];
    return dbpath;
}

3 准备工作都做好了，然后就是创建数据库了

//先获取work.sqlite在沙盒中的路径
        NSString *fileName = [self getDBPath];
        //创建数据库对象
        _database = [[FMDatabase alloc] initWithPath:fileName];
        NSLog(@"databasePath:%@",_database.databasePath);

4 然后是创建表，如果是第一次就是创建，第二次就是访问数据表了，所以需要一个简单的判断，当前表是否已经存在

// 判断是否存在表
- (BOOL)isTableOK:(NSString *)tableName{
    FMResultSet *rs = [_database executeQuery:@"select count(*) as 'count' from sqlite_master where type ='table' and name = ?", tableName];
    while ([rs next]){
        // just print out what we've got in a number of formats.
        NSInteger count = [rs intForColumn:@"count"];
        NSLog(@"isTableOK %ld", count);
        if (0 == count)
        {
            return NO;
        }
        else
        {
            return YES;
        }
    }
    
    return NO;
}

5 如果不存在就创建表

-(void)createTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS workTable"
    @"(serial integer PRIMARY KEY AUTOINCREMENT,"
    @"workId Varchar(1024),"
    @"age integer NOT NULL,"
    @"name Varchar(1024));";
    BOOL isSuccess = [_database executeUpdate:sql];
    if (!isSuccess) {
        NSLog(@"%@",[_database lastErrorMessage]);
    }else{
        NSLog(@"创建数据表成功");
    }
}

- (id)init{
    if (self = [super init]) {
        //先获取work.sqlite在沙盒中的路径
        NSString *fileName = [self getDBPath];
        //创建数据库对象
        _database = [[FMDatabase alloc] initWithPath:fileName];
        NSLog(@"databasePath:%@",_database.databasePath);
        //打开数据库
        if ([_database open]) {//第一次打开如果没有那么就创建并打开 如果有直接打开
            //打开成功
            //如果没有表就创建表
            if (![self isTableOK:@"workTable"]) {
                [self createTable];
            }else{
                NSLog(@"表已经存在");
            }
        }else{
            NSLog(@"open database:%@",[_database lastErrorMessage]);
        }
    }
    return self;
}

6 表创建好了，还需要一个对象

#import <Foundation/Foundation.h>

@interface Worker : NSObject

@property (nonatomic,copy) NSString *workId;
@property (nonatomic,copy) NSString *name;
@property (nonatomic,assign) NSInteger age;

@end

7 数据库的操作了

1 增加

其中需要注意的是，加入int类型的数据格式，需要用@()

-(void)insertDataFromModel:(Worker *)model{
    if ([self isExistDataFromId:model.workId]) {
        return;
    }else{
        NSString *sql = @"insert into workTable (workId,age,name) values(?,?,?)";
        BOOL isSuccess = [_database executeUpdate:sql,model.workId,@(model.age),model.name];
        if (!isSuccess) {
            NSLog(@"insert error:%@",[_database lastErrorMessage]);
        }else{
            NSLog(@"插入成功");
        }
    }
}

-(BOOL)isExistDataFromId:(NSString *)workId{
    NSString *sql = @"select * from workTable where workId = ?";
    FMResultSet *rs = [_database executeQuery:sql,workId];
    if([rs next]){
        return YES;
    }else{
        return NO;
    }
}

2 删除

-(void)deleteFronWorkId:(NSString *)workId{
    NSString *sql = @"delete from workTable where workId = ?";
    BOOL isSuccess = [_database executeUpdate:sql,workId];
    if (!isSuccess) {
        NSLog(@"delete Error:%@",[_database lastErrorMessage]);
    }else{
        NSLog(@"删除成功");
    }
}

3 修改

-(void)updateSetName:(NSString *)name age:(NSInteger)age WhereworId:(NSString *)workId{
    NSString *sql = @"update workTable set name = ?,age = ? where workId = ?";
    BOOL isSuccess = [_database executeUpdate:sql,name,@(age),workId];
    if (!isSuccess) {
        NSLog(@"update Error:%@",[_database lastErrorMessage]);
    }else{
        NSLog(@"更新成功");
    }
}

4 查询

-(NSMutableArray *)getAllData{
    NSString *sql = @"select * from workTable";
    FMResultSet *rs = [_database executeQuery:sql];
    NSMutableArray *arr = [[NSMutableArray alloc] init];
    while ([rs next]) {
        Worker *model = [[Worker alloc] init];
        model.workId = [rs stringForColumn:@"workId"];
        model.name = [rs stringForColumn:@"name"];
        model.age = [rs intForColumn:@"age"];
        [arr addObject:model];
    }
    return arr;
}

8 多线程的操作

1 因为FMDB是非线程安全，所以需要同步处理数据，FMDB就用了inDataBase和inTransaction这两个函数

2 还有一个问题是主线程访问数据库的时候，后台正在刷新或者添加数据，需要等待，可以把inDataBase和inTransaction放在异步里面

等更新完了再刷新UI，就跟图片下载再加载的道理是一样的。

首先要导入FMDataBaseQueue.h

inDataBase例子

-(void)testDataBaseQueueFrom:(NSMutableArray *)arr{
    //1.创建队列
    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:[self getDBPath]];
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        [queue inDatabase:^(FMDatabase *db) {
            TICK;
            for (Worker *model in arr) {
                NSString *sql = @"insert into workTable (workId,age,name) values(?,?,?)";
                BOOL isSuccess = [db executeUpdate:sql,model.workId,@(model.age),model.name];
                if (!isSuccess) {
                    NSLog(@"insert error:%@",[_database lastErrorMessage]);
                }else{
                    NSLog(@"插入成功");
                }
            }
            TOCK;
        }];
        //主队列获取数据
        dispatch_async(dispatch_get_main_queue(), ^{
            NSLog(@"finish");
            for (Worker *model in [self getAllData]) {
                NSLog(@"workId:%@ name:%@ age:%li",model.workId,model.name,(long)model.age);
            }
        });
    });
}

inTransaction例子

-(void)testDataTransactionFrom:(NSMutableArray *)arr{
    //1.创建队列
    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:[self getDBPath]];
    
    //2.把任务包装到事务里
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        
        [queue inTransaction:^(FMDatabase *db, BOOL *rollback){
            TICK;
            for (Worker *model in arr) {
                NSString *sql = @"insert into workTable (workId,age,name) values(?,?,?)";
                BOOL isSuccess = [db executeUpdate:sql,model.workId,@(model.age),model.name];
                if (!isSuccess) {//如果有错误 返回
                    NSLog(@"insert error:%@",[_database lastErrorMessage]);
                    *rollback = YES;
                    return;
                }else{
                    NSLog(@"插入成功");
                }
            }
            TOCK;
        }];
        //主队列获取数据
        dispatch_async(dispatch_get_main_queue(), ^{
            NSLog(@"finish");
            for (Worker *model in [self getAllData]) {
                NSLog(@"workId:%@ name:%@ age:%li",model.workId,model.name,(long)model.age);
            }
        });
    });
}

两者运行的目的都是一样的，但是运行的效率差别还是比较大的，因为后者用了数据库的事务处理，在插入5000条数据的时候，都是模拟器运行的条件下

前者需要14秒，后者需要1秒

其中的时间运算宏定义

#define TICK   NSDate *startTime = [NSDate date]
#define TOCK   NSLog(@"Time: %f", -[startTime timeIntervalSinceNow])

Demo

http://download.youkuaiyun.com/detail/rpf2014/9679162

参考文档

http://blog.youkuaiyun.com/zhangao0086/article/details/44223007