注意初始化FMDatabase和FMDatabaseQueue使用同一个路径，这样既可以通过FMDatabase操作也可以通过FMDatabaseQueue对数据库进行操作。另外FMDatabase的block中执行是同步的,可以如下判断result的执行结果：
//步骤1
BOOL result = NO;
[queue inDatabase:^(FMDatabase * _Nonnull db) {
xxxx
// 步骤2
result = xxxx;
}];
//步骤3
if(result) {
NSLog(@"====");
}
一，封装类
#import "DTDataBase.h"
#import "FMDB.h"
#define CREAT_TABLE_IFNOT_EXISTS @"create table if not exists %@ (id integer primary key AUTOINCREMENT,key text, data blob)"
#define DELETE_DATA_WITH_PRIMARYKEY @"delete from %@ where key = ?"
#define INSERT_TO_TABLE @"insert into %@ (key, data) values (?, ?)"
#define READ_DATA_TABLE_WITH_PRIMARYKEY @"select data from %@ where key = ?"
#define READ_ALL_DATA @"select data from %@ order by id"
#define UPDATE_DATA_WHTH_PRIMARYKEY @"update %@ set data = ? where key = ?"
#define CLEAR_ALL_DATA @"DELETE FROM %@"
#define Delete_limit @"delete from %@ where (select count(key) from %@)> %ld and key in (select key from %@ order by id desc limit (select count(key) from %@) offset %ld )"
#define TropTable @"drop table if exists %@"
#define Delete_before @"delete from %@ where id < (select id from %@ where key = ?)"
@implementation DTDataBase
//增
+ (BOOL)addDataWithTableName:(NSString *)tableName primaryKey:(NSString *)key data:(NSData *)data {
if (tableName.length == 0 || key.length == 0) {
return NO;
}
NSString *dbPath = [self getDbPath];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
BOOL ret = NO;
ret = [self dataBase:dataBase addData:data toTable:tableName forKey:key];
[dataBase close];
return ret;
}
// 增加到指定的数据库
+ (BOOL)dataBase:(FMDatabase *)dataBase addData:(NSData *)data toTable:(NSString *)tableName forKey:(NSString *)key {
if (dataBase == nil || tableName.length == 0 || key.length == 0) {
return NO;
}
BOOL ret = NO;
if ([dataBase open]) {
ret = [self dataBase:dataBase createTable:tableName];
if (ret) {
NSString *deleteSql = [NSString stringWithFormat:DELETE_DATA_WITH_PRIMARYKEY,tableName];
ret = [dataBase executeUpdate:deleteSql,key];
if (ret) {
NSString *storeURL = [NSString stringWithFormat:INSERT_TO_TABLE,tableName];
ret = [dataBase executeUpdate:storeURL,key,data];
}
}
}
#warning // 此处如果close 数据库，用FMDatabaseQueue时，会出现第一条数据无法写入的情况
// [dataBase close];
return ret;
}
//删
+ (BOOL)deleteDataWithTableName:(NSString *)tableName primaryKey:(NSString *)key {
if (tableName.length == 0 || key.length == 0) {
return NO;
}
NSString *dbPath = [self getDbPath];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
return [self dataBase:dataBase deleteDataFromTable:tableName forKey:key];
}
// 删除指定数据库数据
+ (BOOL)dataBase:(FMDatabase *)dataBase deleteDataFromTable:(NSString *)tableName forKey:(NSString *)key {
if (dataBase == nil || tableName.length == 0 || key.length == 0) {
return NO;
}
BOOL ret = NO;
if ([dataBase open]) {
ret = [self dataBase:dataBase createTable:tableName];
if (ret) {
NSString *deleteSql = [NSString stringWithFormat:DELETE_DATA_WITH_PRIMARYKEY,tableName];
ret = [dataBase executeUpdate:deleteSql,key];
}
}
[dataBase close];
return ret;
}
+ (BOOL)tropTable:(NSString *)tableName {
NSString *dbPath = [self getDbPath];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
BOOL ret = NO;
if ([dataBase open]) {
ret = [self dataBase:dataBase createTable:tableName];
if (ret) {
NSString *deleteSql = [NSString stringWithFormat:TropTable,tableName];
ret = [dataBase executeUpdate:deleteSql];
}
}
[dataBase close];
return ret;
}
+ (BOOL)deleteLimite:(long)limit fromTable:(NSString *)tableName {
NSString *dbPath = [self getDbPath];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
BOOL ret = NO;
if ([dataBase open]) {
ret = [self dataBase:dataBase createTable:tableName];
if (ret) {
NSString *deleteSql = [NSString stringWithFormat:Delete_limit,tableName,tableName,limit,tableName,tableName,limit];
ret = [dataBase executeUpdate:deleteSql];
}
}
[dataBase close];
return ret;
}
+ (BOOL)deleteBeforeKey:(NSString *)key fromTable:(NSString *)tableName {
NSString *dbPath = [self getDbPath];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
BOOL ret = NO;
if ([dataBase open]) {
ret = [self dataBase:dataBase createTable:tableName];
if (ret) {
NSString *deleteSql = [NSString stringWithFormat:Delete_before,tableName,tableName];
ret = [dataBase executeUpdate:deleteSql,key];
}
}
[dataBase close];
return ret;
}
//改
+ (BOOL)updateDataWithTableName:(NSString *)tableName primaryKey:(NSString *)key data:(NSData *)data {
if (tableName.length == 0 || key.length == 0) {
return NO;
}
NSString *dbPath = [self getDbPath];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
return [self dataBase:dataBase updateData:data toTable:tableName forKey:key];
}
// 更新指定数据库数据
+ (BOOL)dataBase:(FMDatabase *)dataBase updateData:(NSData *)data toTable:(NSString *)tableName forKey:(NSString *)key {
if (dataBase == nil || tableName.length == 0 || key.length == 0) {
return NO;
}
BOOL ret = NO;
if ([dataBase open]) {
ret = [self dataBase:dataBase createTable:tableName];
if (ret) {
NSString *updateSql = [NSString stringWithFormat:UPDATE_DATA_WHTH_PRIMARYKEY,tableName];
ret = [dataBase executeUpdate:updateSql,data,key];
}
}
[dataBase close];
return ret;
}
//查单条数据
+ (BOOL)readDataWithTableName:(NSString *)tableName primaryKey:(NSString *)key data:(GetDataByPrimaryKey)dataBlock {
if (tableName.length == 0) {
return 0;
}
NSString *dbPath = [self getDbPath];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
return [self dataBaseReadData:dataBase fromTable:tableName forKey:key data:dataBlock];
}
// 读取指定数据库数据
+ (BOOL)dataBaseReadData:(FMDatabase *)dataBase fromTable:(NSString *)tableName forKey:(NSString *)key data:(GetDataByPrimaryKey)dataBlock{
if (dataBase == nil || tableName.length == 0 || key.length == 0) {
return NO;
}
BOOL ret = NO;
if ([dataBase open]) {
ret = [self dataBase:dataBase createTable:tableName];
if (ret) {
NSString *updateSql = [NSString stringWithFormat:READ_DATA_TABLE_WITH_PRIMARYKEY,tableName];
FMResultSet *resultSet = [dataBase executeQuery:updateSql,key];
while ([resultSet next]) {
NSData *data = [resultSet dataForColumn:@"data"];
dataBlock(data);
}
}
}
[dataBase close];
return ret;
}
//查表内所有数据
+ (BOOL)readAllDataWithTableName:(NSString *)tableName data:(GetAllData)dataBlock {
if (tableName.length == 0) {
return NO;
}
NSString *dbPath = [self getDbPath];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
return [self dataBaseReadData:dataBase fromTable:tableName data:dataBlock];
}
+ (BOOL)dataBaseReadData:(FMDatabase *)dataBase fromTable:(NSString *)tableName data:(GetAllData)dataBlock {
if (dataBase == nil || tableName.length == 0) {
return NO;
}
BOOL ret = NO;
if ([dataBase open]) {
ret = [self dataBase:dataBase createTable:tableName];
if (ret) {
NSString *updateSql = [NSString stringWithFormat:READ_ALL_DATA,tableName];
FMResultSet *resultSet = [dataBase executeQuery:updateSql];
NSMutableArray *dataArr = [NSMutableArray array];
NSData *data = [resultSet dataForColumn:@"data"];
if (data) {
[dataArr addObject:data];
}
while ([resultSet next]) {
NSData *data = [resultSet dataForColumn:@"data"];
[dataArr addObject:data];
}
dataBlock([dataArr copy]);
}
}
[dataBase close];
return ret;
}
//清除表内所有数据
+ (BOOL)clearDataBaseWithTableName:(NSString *)tableName {
if (tableName.length == 0) {
return NO;
}
BOOL ret = false;
NSString *dbPath = [self getDbPath];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
[self dataBase:dataBase clearDataWithTableName:tableName];
return ret;
}
+ (BOOL)dataBase:(FMDatabase *)dataBase clearDataWithTableName:(NSString *)tableName {
if (dataBase == nil || tableName.length == 0) {
return NO;
}
BOOL ret = NO;
if ([dataBase open]) {
NSString *clearSql = [NSString stringWithFormat:CLEAR_ALL_DATA,tableName];
ret = [dataBase executeUpdate:clearSql];
}
[dataBase close];
return ret;
}
#pragma mark - private function
+ (NSString *)getDbPath {
NSString *doc= [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
return [doc stringByAppendingPathComponent:@"data.sqlite"];
}
+ (BOOL)dataBase:(FMDatabase *)dataBase createTable:(NSString *)tableName {
NSString *sql = [NSString stringWithFormat:CREAT_TABLE_IFNOT_EXISTS,tableName];
return [dataBase executeUpdate:sql];
}
@end
网友评论