FMDB使用及遇到的坑

之前听说过FMDB,一直没用过,最近项目中需求:随时需要处理上报的设备状态数据,同时UI做相应的改变,展示数据,其中上报的数据中包含需要插入、删除、更新操作。

根据这几个特点,普通的plist文件或者UserDefault(本质也是一个plist文件)不能满足需求,文件操作每次读取的到内存,最后还要保存回去,放弃了。选择在磁盘上操作的数据库存储,至少不用全部数据读取出来就让我很开心。其次,文件的读取线程安全是没有的,自己还要写处理线程安全,不然莫名其妙的崩溃就出现了,之前写的商城购物城就用了文件存储,遇到过坑。不过现在是真的懒,不想写。(文件读写线程安全:解决资源竞争问题,可以用串行队列,同时控制读操作可以并发进行,写操作不能)。

FMDB的使用,FMDB提供了三个类:

1、FMDataBase:代表数据库的类,执行SQL语句。

2、FMResultSet:代表查询结果的类,去遍历此对象获取需要的对象。

3、FMDdateBaseQueue:多线程下操作数据库的类,主要起到线程安全作用。

基于上面的需求,选择了FMDdateBaseQueue来操作数据库。先上代码吧,文章最后说下FMDdateBaseQueue是怎么保证线程安全的。

.h文件:

.m文件

#import "DeviceDBManager.h"

static DeviceDBManager *_dbManager = nil;

@implementation DeviceDBManager{ FMDatabaseQueue *_databaseQue;}+(instancetype)shareInstance{ static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ _dbManager = [[DeviceDBManager alloc]init]; });return_dbManager;}-(instancetype)init{if(self == [super init]) { [self creatDB_Table]; }returnself;}+(NSString *)getDataBasePath{ NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject]; path = [path stringByAppendingPathComponent:@"RedDevices.db"];returnpath;}

#pragma mark - 创建表

-(void)creatDB_Table{ _databaseQue = [FMDatabaseQueue databaseQueueWithPath:[DeviceDBManager getDataBasePath]]; //不存在数据库if(![[NSFileManager defaultManager] fileExistsAtPath:[DeviceDBManager getDataBasePath]]) { } [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { BOOL createTableResult=[db executeUpdate:@"CREATE TABLE IF NOT EXISTS RedDevices (mac text PRIMARY KEY,source text,version text,data text,mode integer,currentTemper integer,setTemper integer,isOn integer,isOnline integer,countDownTime text)"];if(createTableResult) { NSLog(@"创建表成功");}else{ NSLog(@"创建表失败");return; } }];}

#pragma mark - 插入/更新数据

-(void)insertOrUpdateDataWithModel:(DeviceSQLiteModel *)model{ DeviceSQLiteModel *temModel = [self queryDataWithMac:model.mac]; //存在 就更新状态if(temModel.mac) { [self updateDataWithModel:model]; }else//不存在新增 { [self insertDataWithModel:model]; }}

#pragma mark - 单个新增数据

-(void)insertDataWithModel:(DeviceSQLiteModel *)model{ [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { BOOL success = [db executeUpdate:@"INSERT INTO RedDevices (mac, source, version,data,mode,currentTemper,setTemper,isOn,countDownTime,isOnline) VALUES (?,?,?,?,?,?,?,?,?,?)", model.mac.uppercaseString, model.source,model.version,model.data, @(model.mode),@(model.currentTemper),@(model.setTemper),@(model.isOn),model.countDownTime,@(model.isOnline)];if(success) { NSLog(@"插入数据成功");}else{ NSLog(@"插入失败");} }];}

#pragma mark - 批量增加数据

-(void)insertDatasWithArray:(NSArray *)arr{ [_databaseQue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {

for(NSInteger i =; i < arr.count; i++) { //@(model.isManulMode)DeviceModel *model = arr[i];

#pragma mark - 删除指定Mac的设备

-(void)deleteDataWithMac:(NSString *)mac{ mac = mac.uppercaseString; [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *rs = [db executeQuery:@"delete from RedDevices WHERE mac = ?",mac.uppercaseString];[rs close]; }];}

#pragma mark -删除所有设备

-(void)deleteAllData{ [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *rs = [db executeQuery:@"delete from RedDevices"];[rs close]; }];}

#pragma mark - 更新对应Mac的数据

-(void)updateDataWithModel:(DeviceSQLiteModel *)model{ DeviceSQLiteModel *temModel = [self queryDataWithMac:model.mac]; //不存在则更新数据if(!temModel.mac) { [self insertDataWithModel:model]; } [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { [db executeUpdate:@"update RedDevices set source = ?,version = ?,data = ? ,mode = ?,currentTemper = ?,setTemper = ?,isOn = ?,countDownTime = ?,isOnline = ? where mac = ? ",model.source,model.version,model.data, @(model.mode),@(model.currentTemper),@(model.setTemper),@(model.isOn),model.countDownTime,@(model.isOnline),model.mac.uppercaseString];}];}

#pragma mark - 查询所有数据

-(NSArray *)queryAllData{ /*@property(nonatomic,copy)NSString *mac;@property(nonatomic,copy)NSString *source;@property(nonatomic,copy)NSString *version;@property(nonatomic,copy)NSString *data;@property(nonatomic,assign)NSInteger mode;@property(nonatomic,assign)NSInteger currentTemper;@property(nonatomic,assign)NSInteger setTemper;@property(nonatomic,assign)BOOL isOn;@property(nonatomic,copy)NSString *countDownTime;*/ __block NSMutableArray *devices = [NSMutableArray array]; [_databaseQue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) { NSString *sql =@"select * from RedDevices";FMResultSet *rs = [db executeQuery:sql];while([rs next]) { DeviceSQLiteModel *model = [[DeviceSQLiteModel alloc]init]; model.mac = [rs stringForColumn:@"mac"];model.source = [rs stringForColumn:@"source"];model.version = [rs stringForColumn:@"version"];model.data = [rs stringForColumn:@"data"];model.mode = [rs intForColumn:@"mode"];model.currentTemper = [rs intForColumn:@"currentTemper"];model.setTemper = [rs intForColumn:@"setTemper"];model.isOn = [rs intForColumn:@"isOn"];model.isOnline = [rs intForColumn:@"isOnline"];model.countDownTime = [rs stringForColumn:@"countDownTime"];[devices addObject:model]; } [rs close]; }];returndevices;}

#pragma mark - 根据设备mac查找设备

-(DeviceSQLiteModel *)queryDataWithMac:(NSString *)mac{ mac = mac.uppercaseString; DeviceSQLiteModel *model = [[DeviceSQLiteModel alloc]init]; [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { FMResultSet *rs = [db executeQuery:@"select * from RedDevices WHERE mac = ?",mac.uppercaseString];while([rs next]) { model.mac = [rs stringForColumn:@"mac"];model.source = [rs stringForColumn:@"source"];model.version = [rs stringForColumn:@"version"];model.data = [rs stringForColumn:@"data"];model.mode = [rs intForColumn:@"mode"];model.currentTemper = [rs intForColumn:@"currentTemper"];model.setTemper = [rs intForColumn:@"setTemper"];model.isOn = [rs intForColumn:@"isOn"];model.isOnline = [rs intForColumn:@"isOnline"];model.countDownTime = [rs stringForColumn:@"countDownTime"];} [rs close]; }];returnmodel;}-(void)updataModeDataWithModel:(DeviceSQLiteModel *)model{ DeviceSQLiteModel *temModel = [self queryDataWithMac:model.mac]; //不存在则更新数据if(!temModel.mac) { [self insertDataWithModel:model]; } [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { [db executeUpdate:@"update RedDevices set mode = ? where mac = ? ", @(model.mode),model.mac.uppercaseString];}];}-(void)updataSwitchDataWithModel:(DeviceSQLiteModel *)model{ DeviceSQLiteModel *temModel = [self queryDataWithMac:model.mac]; //存在 就更新状态if(!temModel.mac) { [self insertDataWithModel:model]; } [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { BOOL success = [db executeUpdate:@"update RedDevices set isOn = ? where mac = ? ", @(model.isOn),model.mac.uppercaseString];if(success) { NSLog(@"更新成功");}else{ NSLog(@"更新失败");} }];}-(void)updateOnlineDataWithModel:(DeviceSQLiteModel *)model{ DeviceSQLiteModel *temModel = [self queryDataWithMac:model.mac];if(!temModel.mac) { [self insertDataWithModel:model]; } [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { [db executeUpdate:@"update RedDevices set isOnline = ? where mac = ? ", @(model.isOnline),model.mac.uppercaseString];}];}-(void)updateCurrentTemperDataWithModel:(DeviceSQLiteModel *)model{ DeviceSQLiteModel *temModel = [self queryDataWithMac:model.mac];if(!temModel.mac) { [self insertDataWithModel:model]; } [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { [db executeUpdate:@"update RedDevices set currentTemper = ? where mac = ? ", @(model.currentTemper),model.mac.uppercaseString];}];}-(void)updateSetTemperDataWithModel:(DeviceSQLiteModel *)model{ DeviceSQLiteModel *temModel = [self queryDataWithMac:model.mac];if(!temModel.mac) { [self insertDataWithModel:model]; } [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { [db executeUpdate:@"update RedDevices set setTemper = ? where mac = ? ", @(model.setTemper),model.mac.uppercaseString];}];}-(void)updateCountDownTimeDataWithModel:(DeviceSQLiteModel *)model{ DeviceSQLiteModel *temModel = [self queryDataWithMac:model.mac]; //存在 就更新状态if(!temModel.mac) { [self insertDataWithModel:model]; } [_databaseQue inDatabase:^(FMDatabase * _Nonnull db) { [db executeUpdate:@"update RedDevices set countDownTime = ? where mac = ? ", model.countDownTime,model.mac.uppercaseString];}];}

@end

遇到的坑:如果报错都是SQL语句的问题,仔细检查是不是插入的实体里面的属性名称,建表的时候SQL语句里面忘添加了,其他操作一样,我遇到的所有的错误都是来自SQL语句的问题,建议使用FMDdateBaseQueue 就别单独使用FMDataBase,两者混在一起也容易出错。

你可能会问查询接口这么写是否有问题,告诉你没问题。为什么?

FMDdateBaseQueue 内部是使用一个串型队列,执行的所有操作都是GCD同步追加进去的,别看到Block就觉得是异步,怕来不及返回结果。

这是今年的最后一篇文章,笔者近来心力交瘁,累了,想调整一哈子。有人要Demo的加QQ群吧!群号:609389028

  • 发表于:
  • 原文链接:http://kuaibao.qq.com/s/20180201G00S0G00?refer=cp_1026

扫码关注云+社区