iOS FMDB小结

来源：互联网发布：owncloud php 编辑：程序博客网时间：2024/06/05 22:44

作为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.csdn.net/detail/rpf2014/9679162

参考文档

http://blog.csdn.net/zhangao0086/article/details/44223007

0 0