iOS FMDB框架的使用以及简单的封装

在开发过程中，我们经常会使用到手机本地数据库的使用，但是数据库的使用，在app也只是基本的增删改查而已。

FMDB一个很好的数据库框架，为我们开发提供更好的操作。

首先我们想导入一下FMDB，有两种方式

第一种Cocoapods安装

pod 'FMDB'

第二种

我们只需要直接将FMDB拖入到工程即可，附上下载链接

FMDB GitHub链接: https://github.com/ccgus/fmdb

这就两种安装方式，安装完成后我们，需要倒入一个依赖

sqlite3.0.tbd

到此，FMDB就集成完成了

FMDB的使用

1、首先就是初始化FMDB并创建数据库，app侧很明确，基本创建的数据库保存到沙盒路径下

初始化并创建数据库

//SQLiteString 为数据库名称 这里是一个宏定义（名字随便）#define SQLiteString @"sx_equipment_abnormal.sqlite"
NSString *filePath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject] stringByAppendingPathComponent:SQLiteString];
NSLog(@"filePath - - - - - - -%@",filePath);
//初始化
_fmdb = [FMDatabase databaseWithPath:filePath];

打开数据库并创建一个表

    BOOL successOpen = [_fmdb open];
    if ( successOpen ) {
        NSLog(@"数据库创建成功");
        NSString *creatString = @"CREATE TABLE IF NOT EXISTS abnormal_data(id INTEGER PRIMARY KEY, user_id TEXT, creat_time TEXT, type TEXT);";
        BOOL stateFordb = [_fmdb executeUpdate:creatString];
        if (stateFordb) {
            NSLog(@"数据库已打开");
        } else {
            NSLog(@"数据库打开失败");
        }
    }

2、接下来就是对表的增删改查了

插入

NSString *insertString = [NSString stringWithFormat:@"INSERT INTO abnormal_data(user_id,creat_time,type)  VALUES('%@','%@','%@');",user_id,.creat_time,type];
BOOL stateForinsert = [_fmdb executeUpdate:insertString];
if (stateForinsert) {
    NSLog(@"插入数据库成功");
} else {
    NSLog(@"插入数据库失败");
}

查询

NSString *selectString = [NSString stringWithFormat:@"SELECT * FROM abnormal_data WHERE user_id = '%@' order by creat_time desc;",userId];
NSMutableArray *arrM = [NSMutableArray array];
FMResultSet *set =  [_fmdb executeQuery:selectString];
while ([set next]) {
    NSString *user_id    = [set stringForColumn: @"user_id"];
    [arrM addObject: user_id];
}
NSLog(@"%@",arrM);

删除

NSString *deleteString = [NSString stringWithFormat:@"DELETE FROM abnormal_data WHERE user_id = '%@';",userId];
BOOL state = [_fmdb executeUpdate:deleteString];

更新 

    NSString *selectString = [NSString stringWithFormat:@"SELECT * FROM abnormal_data WHERE user_id = '%@';",userId];
    FMResultSet *set =  [_fmdb executeQuery:selectString];
    while ([set next]) {
        NSString *uSql = [NSString stringWithFormat:@"UPDATE abnormal_data SET type = '%@' WHERE user_id='%@';",userId,type];
        BOOL res = [_fmdb executeUpdate:uSql];
        if (!res) {
            NSLog(@"error to UPDATE data");
        } else {
            NSLog(@"succ to UPDATE data");
        }
    }

关闭数据库

[_fmdb close];

到此基本的增删改查

这样使用其实对我们来说已经很方便了，但是还不够，我又在这个基础上进行了简单的封装，封装了一个管理类SXFmdbObject

首先是.h

SXFmdbObject.h

#import <Foundation/Foundation.h>
#import "SXFmdbModel.h"
NS_ASSUME_NONNULL_BEGIN

typedef enum : NSUInteger {
    SQLiteToolsInsertSuccees = 0,
    SQLiteToolsInsertFail,
    SQLiteToolsDeleteSuccees,
    SQLiteToolsDeleteFail,
    SQLiteToolsUpdateSuccees,
    SQLiteToolsUpdateFail,
    SQLiteToolsSelectSuccees,
    SQLiteToolsSelectFail,
} SQLiteToolsState;

@interface SXFmdbObject : NSObject

typedef void (^successBlock)(SQLiteToolsState successState);
typedef void (^failBlock)(SQLiteToolsState failState);
typedef void (^valueBlock) (NSMutableArray *dataArray);

@property (nonatomic, strong) successBlock successblock;
@property (nonatomic, strong) failBlock failBlock;

+ (SXFmdbObject *)shareInstance;

#pragma mark - 储存睡眠展示信息 

/// 插入信息
/// @param model 数据
/// @param success 成功
/// @param fail 失败
- (void)insertData:(SXFmdbModel *)model success:(successBlock)success fail:(failBlock)fail;


/// 查询单条
/// @param userId 用户id
/// @param success 成功
/// @param fail 失败
- (void)selectData:(NSString*)userId success:(valueBlock)success fail:(failBlock)fail;

/// 删除指定设备
/// @param userId 用户ID
- (BOOL)deleteData:(NSString*)userId;

/// 获取所有数据
/// @param success 成功
/// @param fail 失败
- (void)getAllDataSuccess:(valueBlock)success fail:(failBlock)fail;

/// 删除数据
/// @param userId 用户ID
- (void)deleteDataUserId:(NSString*)userId;

/// 更新数据
/// @param userId 用户ID
/// @param type 类型
- (void)updateDataWithUserId:(NSString*)userId withType:(NSString*)type;

/// 关闭数据库
- (void)closeFmdb;
@end

NS_ASSUME_NONNULL_END

接下来是.m

SXFmdbObject.m

#import "SXFmdbObject.h"
#import "FMDB.h"

#define SQLiteString @"sx_equipment_abnormal.sqlite"

#define IS_DB_OPEN if (![_fmdb open]) {\
return;\
}

#define IS_BLOCK_SUCCESS if (success) {\
self.successblock = success;\
}

#define IS_BLOCK_FAIL if (fail) {\
self.failBlock = fail;\
}
@implementation SXFmdbObject

static FMDatabase *_fmdb;

+ (SXFmdbObject *)shareInstance{
    static SXFmdbObject *instance;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [[self alloc] init];
    });
    return instance;
}

- (instancetype)init{
    NSString *filePath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject] stringByAppendingPathComponent:SQLiteString];
    NSLog(@"filePath - - - - - - -%@",filePath);
    //初始化
    _fmdb = [FMDatabase databaseWithPath:filePath];
    [self creatEquipmentAbnormalDB];
    return self;
}

#pragma mark - 创建数据异常数据库
- (void)creatEquipmentAbnormalDB{
    BOOL successOpen = [_fmdb open];
    if ( successOpen ) {
        NSLog(@"数据库创建成功");
        NSString *creatString = @"CREATE TABLE IF NOT EXISTS abnormal_data(id INTEGER PRIMARY KEY, user_id TEXT, creat_time TEXT, type TEXT);";
        BOOL stateFordb = [_fmdb executeUpdate:creatString];
        if (stateFordb) {
            NSLog(@"数据库已打开");
        } else {
            NSLog(@"数据库打开失败");
        }
    }
}

//插入数据
- (void)insertData:(SXFmdbModel *)model success:(successBlock)success fail:(failBlock)fail{
    IS_DB_OPEN
    IS_BLOCK_SUCCESS
    IS_BLOCK_FAIL
    NSString *insertString = [NSString stringWithFormat:@"INSERT INTO abnormal_data(user_id,creat_time,type)  VALUES('%@','%@','%@');",model.user_id,model.creat_time,model.type];
    BOOL stateForinsert = [_fmdb executeUpdate:insertString];
    if (stateForinsert) {
        NSLog(@"插入数据库成功");
        self.successblock(SQLiteToolsInsertSuccees);
    } else {
        NSLog(@"插入数据库失败");
        self.failBlock(SQLiteToolsInsertFail);
    }
}

//查询数据
- (void)selectData:(NSString*)userId success:(nonnull valueBlock)success fail:(nonnull failBlock)fail{
    IS_DB_OPEN
    IS_BLOCK_FAIL
    NSString *selectString = [NSString stringWithFormat:@"SELECT * FROM abnormal_data WHERE user_id = '%@' order by creat_time desc;",userId];
    NSMutableArray *arrM = [NSMutableArray array];
    FMResultSet *set =  [_fmdb executeQuery:selectString];
    while ([set next]) {
        NSString *user_id    = [set stringForColumn: @"user_id"];
        NSString *type       = [set stringForColumn: @"type"];
        NSString *creat_time = [set stringForColumn: @"creat_time"];
        SXFmdbModel *model   = [[SXFmdbModel alloc] init];
        model.user_id        = user_id;
        model.type           = type;
        model.creat_time     = creat_time;
        [arrM addObject:model];
    }
    success(arrM);
}

//删除
- (BOOL)deleteData:(NSString*)userId{
    NSString *deleteString = [NSString stringWithFormat:@"DELETE FROM abnormal_data WHERE user_id = '%@';",userId];
    BOOL state = [_fmdb executeUpdate:deleteString];
    return state;
}

/**
 获取所有数据

 @param success 成功
 @param fail 失败
 */
- (void)getAllDataSuccess:(valueBlock)success fail:(failBlock)fail{
    NSString *deleteString = [NSString stringWithFormat:@"SELECT * FROM abnormal_data;"];
    FMResultSet *set =  [_fmdb executeQuery:deleteString];
    NSMutableArray *arrM = [NSMutableArray array];
    while ([set next]) {
        NSString *user_id    = [set stringForColumn: @"user_id"];
        NSString *type       = [set stringForColumn: @"type"];
        NSString *creat_time = [set stringForColumn: @"creat_time"];
        SXFmdbModel *model = [[SXFmdbModel alloc] init];
        model.user_id      = user_id;
        model.type         = type;
        model.creat_time   = creat_time;
        [arrM addObject:model];
    }
    success(arrM);
}

//删除
- (void)deleteDataUserId:(NSString *)userId{
    NSString *selectString = [NSString stringWithFormat:@"SELECT * FROM abnormal_data;"];
    FMResultSet *set =  [_fmdb executeQuery:selectString];
    while ([set next]) {
        NSString *deleteString = [NSString stringWithFormat:@"DELETE FROM abnormal_data WHERE user_id = '%@';",userId];
        BOOL state = [_fmdb executeUpdate:deleteString];
        NSLog(@"state - - - - - - - - - - %d",state);
    }
}

/// 更新数据
/// @param userId 用户ID
/// @param type 类型
- (void)updateDataWithUserId:(NSString *)userId withType:(nonnull NSString *)type{
    NSString *selectString = [NSString stringWithFormat:@"SELECT * FROM abnormal_data WHERE user_id = '%@';",userId];
    FMResultSet *set =  [_fmdb executeQuery:selectString];
    while ([set next]) {
        NSString *uSql = [NSString stringWithFormat:@"UPDATE abnormal_data SET type = '%@' WHERE user_id='%@';",userId,type];
        BOOL res = [_fmdb executeUpdate:uSql];
        if (!res) {
            NSLog(@"error to UPDATE data");
        } else {
            NSLog(@"succ to UPDATE data");
        }
    }
}

/// 关闭数据库
- (void)closeFmdb{
    [_fmdb close];
}

@end

上面涉及到一个model类，SXFmdbModel

首先是.h

#import <Foundation/Foundation.h>

NS_ASSUME_NONNULL_BEGIN

@interface SXFmdbModel : NSObject
@property (nonatomic, assign) NSString *user_id;
@property (nonatomic, strong) NSString *type;
@property (nonatomic, strong) NSString *creat_time;
@end

NS_ASSUME_NONNULL_END

首先是.m

#import "SXFmdbModel.h"

@implementation SXFmdbModel

@end

上面是将FMDB的数据进行了封装，并章程model，为了方便操作。

到此FMDB的基本使用就完成了。