FMDB封装

注意初始化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