iOS - FMDB 封装(数据库的存入||读取||删除||查询

前言:

在项目中会经常遇到数据写入本地,读取等等操作,这样的操作会经常用到,所以就封装了一个类方法

前提:

是要用cocoapods下载FMDB框架,我用到是模型来存储数据的,大家也可以用字典来存储,一样的道理,封装类方法如下:

• .h文件如下:

#import <Foundation/Foundation.h>
#import "FMDB.h"

@class TestModel;
@class DetectModel;
@interface FmdbTooler : NSObject

+(FmdbTooler*)sharedInstance;
- (void)openDatabaseWithUserName:(NSString*)userName;

// 往数据库写入数据ut_detect  add by bob
-(BOOL)addUt_detectLogsave:(DetectModel *)model;
// 获取ut_detect表下面的数据 add by bob
- (NSMutableArray *)getUt_detectDataDetial;
// 根据detectingdate删除某个咨询对话 add by bob
-(BOOL)deleteUt_detectOfVisitid:(NSString *)detectingdate;
//更新detectingdate对应的咨询对话信息UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
- (void)updateUt_detectOfVisitid:(NSString *)detectingdate logsave:(DetectModel *)model;
// 根据detectingdate判断数据是否存在
-(BOOL)isExitUt_detectOfDetectingdate:(NSString *)detectingdate;

// ut_testing表
// 往数据库写入数据  add by bob
-(BOOL)addUt_testingLogsave:(TestModel *)model;
// 获取ut_testing表下面的数据 add by bob
- (NSMutableArray *)getUt_testingDataDetial;
// 根据testingid删除某个咨询对话 add by bob
-(BOOL)deleteUt_testingOfVisitid:(NSInteger)testingid;

@end

• .m实现方法:

#import "FmdbTooler.h"
#import <CommonCrypto/CommonDigest.h>
#import "DetectModel.h"
#import "TestModel.h"

@interface FmdbTooler()

@property (nonatomic, strong) FMDatabase *dataBase;

@end

@implementation FmdbTooler

+(FmdbTooler*)sharedInstance{
    static FmdbTooler* dbmanager;
    static dispatch_once_t dbmanageronce;
    dispatch_once(&dbmanageronce, ^{
        dbmanager = [[FmdbTooler alloc] init];
    });
    return dbmanager;
}

- (void)openDatabaseWithUserName:(NSString*)userName {
    if (userName.length==0) {
        return;
    }
    
    //Documents:
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    
    //username md5
    const char *cStr = [userName UTF8String];
    unsigned char result[16];
    CC_MD5(cStr, (CC_LONG)strlen(cStr), result);
    NSString* MD5 =  [NSString stringWithFormat:@"%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x",result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7],result[8], result[9], result[10], result[11],result[12], result[13], result[14], result[15]];
    
    //数据库文件夹
    NSString * documentsDirectory = [[paths objectAtIndex:0] stringByAppendingPathComponent:MD5];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    BOOL isDir = FALSE;
    BOOL isDirExist = [fileManager fileExistsAtPath:documentsDirectory isDirectory:&isDir];
    if(!(isDirExist && isDir)) {
        BOOL bCreateDir = [fileManager createDirectoryAtPath:documentsDirectory withIntermediateDirectories:YES attributes:nil error:nil];
        if(!bCreateDir) {
            NSLog(@"Create Database Directory Failed.");
        }
        NSLog(@"%@", documentsDirectory);
    }
    
    NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:@"ut_database.db"];
    if (self.dataBase) {
        [self.dataBase close];
        self.dataBase = nil;
    }
    
    self.dataBase = [FMDatabase databaseWithPath:dbPath];
    [self.dataBase open];
    
    
    // 新建ut_detect数据表
    [self ut_detectTableCreate];
    
    // 新建ut_testing数据表
    [self ut_testingTableCreate];
}

// 判断指定表是否存在
- (BOOL)checkTableExist:(NSString *)tableName {
    BOOL result = NO;
    NSString* lowtableName = [tableName lowercaseString];
    
    FMResultSet *rs = [self.dataBase executeQuery:@"SELECT [sql] FROM sqlite_master WHERE [type] = 'table' AND lower(name) = ?", lowtableName];
    result = [rs next];
    [rs close];
    
    return result;
}

// 创建表
- (void) createTable:(NSString*)tableName sql:(NSString *)createSql {
    
    BOOL isExist = [self.dataBase tableExists:tableName];
    if (!isExist) {
        [self.dataBase executeUpdate:createSql];
    }
}
- (BOOL)runSql:(NSString*)sql {
    return [self.dataBase executeUpdate:sql];
}
- (int)getCountWithSql:(NSString*)sql {
    
    int count = 0;
    FMResultSet *rs = [self.dataBase executeQuery:sql];
    if ([rs next]) {
        count = [rs intForColumnIndex:0];
    }
    [rs close];
    return count;
}
// 创建 ut_detect 表
- (void)ut_detectTableCreate{
    [self createTable:@"ut_detect" sql:@"CREATE table ut_detect (detectingdate varchar(64) PRIMARY KEY, deviceid varchar(64), indexuom varchar(64),pvalue varchar(64),frequency varchar(64),minvalue varchar(64),maxvalue varchar(64))"];
}
// 往数据库写入数据  add by bob
-(BOOL)addUt_detectLogsave:(DetectModel *)model {
    //增加一条记录
  return [self.dataBase executeUpdate:@"INSERT INTO ut_detect(detectingdate, deviceid, indexuom, pvalue, frequency, minvalue, maxvalue) VALUES (?,?,?,?,?,?,?)", model.detectingdate, model.detectid, model.indexuom, model.pvalue, model.frequency, model.minvalue, model.maxvalue];
}
// 获取ut_detect表下面的数据 add by bob
- (NSMutableArray *)getUt_detectDataDetial{
    
    NSMutableArray *detectArray = [NSMutableArray array];
    FMResultSet *rs = [self.dataBase executeQuery:[NSString stringWithFormat:@"SELECT * FROM ut_detect"]];
    while ([rs next]){ // 这些属性名务必按创建表时候的顺序来写
        DetectModel *model = [[DetectModel alloc] init];
        int columnIndex = 0;
        model.detectingdate  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.deviceid = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.indexuom  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.pvalue  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.frequency  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.minvalue  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.maxvalue  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        [detectArray addObject:model];
    }
    
    [rs close];
    return detectArray;
    
}
// 根据detectingdate删除某个咨询对话 add by bob
-(BOOL)deleteUt_detectOfVisitid:(NSString *)detectingdate {
    return [self runSql:[NSString stringWithFormat:@"DELETE FROM ut_detect WHERE detectingdate = %@",detectingdate]];
}
//更新detectingdate对应的咨询对话信息UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
- (void)updateUt_detectOfVisitid:(NSString *)detectingdate logsave:(DetectModel *)model {
    [self runSql:[NSString stringWithFormat:@"UPDATE ut_detect SET indexuom = '%@', pvalue = '%@', frequency = '%@', minvalue = '%@', maxvalue = '%@' WHERE detectingdate = '%@'",model.indexuom, model.pvalue, model.frequency, model.minvalue, model.maxvalue,  detectingdate]];
}
// 根据detectingdate判断数据是否存在
-(BOOL)isExitUt_detectOfDetectingdate:(NSString *)detectingdate {
    int count = [self getCountWithSql:[NSString stringWithFormat:@"SELECT count(*) FROM ut_detect WHERE detectingdate = '%@'", detectingdate]];
    if (count > 0) {
        return YES;
    }else{
        return NO;
    }
}

// 创建 ut_testing 表
- (void)ut_testingTableCreate{
    [self createTable:@"ut_testing" sql:@"CREATE table ut_testing (testingid INTEGER PRIMARY KEY AUTOINCREMENT, deviceid varchar(64), testtype varchar(64), testtime varchar(64),indexuom varchar(64),pvalue varchar(64),frequency varchar(64),minvalue varchar(64),maxvalue varchar(64),strength varchar(64),fatigue varchar(64), dectecttime varvhar(5000))"];
}
// 往数据库写入数据  add by bob
-(BOOL)addUt_testingLogsave:(TestModel *)model {
    //增加一条记录
   return [self.dataBase executeUpdate:@"INSERT INTO ut_testing(deviceid, testtype, testtime, indexuom, pvalue, frequency, minvalue, maxvalue, strength, fatigue, dectecttime) VALUES (?,?,?,?,?,?,?,?,?,?,?)", model.deviceid, model.testtype, model.testtime, model.indexuom, model.pvalue, model.frequency, model.minvalue, model.maxvalue, model.strength, model.fatigue, model.dectecttime];
    
}
// 获取ut_detect表下面的数据 add by bob
- (NSMutableArray *)getUt_testingDataDetial{
    
    NSMutableArray *detectArray = [NSMutableArray array];
    FMResultSet *rs = [self.dataBase executeQuery:[NSString stringWithFormat:@"SELECT * FROM ut_testing"]];
    while ([rs next]){
        TestModel *model = [[TestModel alloc] init];
        int columnIndex = 0;   // 这些属性名务必按创建表时候的顺序来写
        model.testingid = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.deviceid  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.testtype = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.testtime  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.indexuom  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.pvalue  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.frequency  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.minvalue  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.maxvalue  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.strength  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.fatigue  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        model.dectecttime  = [rs stringForColumnIndex:columnIndex]; columnIndex++;
        [detectArray addObject:model];
    }
    
    [rs close];
    return detectArray;
    
}
// 根据detectingdate删除某个咨询对话 add by bob
-(BOOL)deleteUt_testingOfVisitid:(NSInteger)testingid {
    return [self runSql:[NSString stringWithFormat:@"DELETE FROM ut_testing WHERE testingid = %ld",(long)testingid]];
}

具体用法如下:

首先是导入头文件

NSString *uid = [WSUserUtil getUser]._id;
 // 打开数据库
 [[FmdbTooler sharedInstance] openDatabaseWithUserName:uid];
// 检测数据是否存在 currentDate 查询的条件
[[FmdbTooler sharedInstance] isExitUt_detectOfDetectingdate:currentDate]
//添加
[[FmdbTooler sharedInstance] addUt_detectLogsave:model];
// 读取表内的所有数据 , 从数据库中读取
  NSArray *detectData = [[FmdbTooler sharedInstance] getUt_detectDataDetial];
// 根据某个属性删除某个数据
[[FmdbTooler sharedInstance] deleteUt_detectOfVisitid:currentDate]

应部分人的要求,把上文中所有用到的 model 开放出来;

• DetectModel

#import <Foundation/Foundation.h>

@interface DetectModel : NSObject
// 检测数据标识
@property (copy, nonatomic) NSString *detectid;
// 设备标识
@property (copy, nonatomic) NSString *deviceid;
// 采样日期
@property (copy, nonatomic) NSString *detectingdate;
// 单位
@property (copy, nonatomic) NSString *indexuom;
// 校准值
@property (copy, nonatomic) NSString *pvalue;
// 采样周期
@property (copy, nonatomic) NSString *frequency;
// 最大值
@property (copy, nonatomic) NSString *minvalue;
// 最小值
@property (copy, nonatomic) NSString *maxvalue;
@end

• TestModel

#import <Foundation/Foundation.h>

@interface TestModel : NSObject
// 测试采样标识
@property (copy, nonatomic) NSString *testingid;
// 检测类型
@property (copy, nonatomic) NSString *deviceid;
// 检测类型
@property (copy, nonatomic) NSString *testtype;
// 测试日期
@property (copy, nonatomic) NSString *testtime;
// 单位
@property (copy, nonatomic) NSString *indexuom;
// 校准值
@property (copy, nonatomic) NSString *pvalue;
// 采样周期
@property (copy, nonatomic) NSString *frequency;
// 最大值
@property (copy, nonatomic) NSString *minvalue;
// 最小值
@property (copy, nonatomic) NSString *maxvalue;
// 肌力
@property (copy, nonatomic) NSString *strength;
// 疲劳度
@property (copy, nonatomic) NSString *fatigue;
// 采样时间点
@property (copy, nonatomic) NSString *dectecttime;

@end