作为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 表创建好了,还需要一个对象
.h
#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