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