FMDB 简单封装

作者: ivylee_mr | 来源:发表于2018-12-28 11:29 被阅读8次

目前对本地数据最好的处理方式就是利用数据库系统来进行数据的处理。
而且数据库对数据的增删改查要比其他方式更方便

思路

目前我们利用数据库保存数据，为了方便起见，我们这边的思路是在数据库创建两到三个主要的key，

id : 主键（方便对数据的增删改查）处理，保证数据唯一性

json: 数据统一用model 转json 字符串保存在字段json中
（这里需要model转json 和json转model，我们主要用的YYKit 中的YYModel框架处理）

createtime: 数据的修改的最后时间

数据库创建后的截图如下：

数据库截图

SQL基本语法

1、创建表

在SQLite3中可以不指定字段的数据类型，SQLite3会自动推断类型。常用的格式有：

文本： Text

整形： Integer

二进制数据: Blob

浮点型： Real Float、Double

布尔型： Boolean

时间型： Time

日期型： Date

时间戳： TimeStamp

我们主要用的就采用Text即可
查询代码如下

typedef NS_ENUM(NSUInteger, SQLStringType) {
    SQLStringTypeCreate,            //-> 创建
    SQLStringTypeInsert,            //-> 插入
    SQLStringTypeUpdate,            //-> 更新
    SQLStringTypeQuery,             //-> 查询
    SQLStringTypeDelete,            //-> 删除
    SQLStringTypeGetAllData,        //-> 获取全部
    SQLStringTypeReInsertKey,       //-> 给数据添加新的key
    SQLStringTypeSortDesc,          //-> 降序
    SQLStringTypeSortAsc,           //-> 升序
};

static NSString *const kDBPrimary   = @"db_id";
static NSString *const kDBCreatTime = @"create_time";
static NSString *const kDBJson      = @"json";

- (NSString *)excuteSQLStringWithType:(SQLStringType)type withModel:(id)model withDataCount:(NSUInteger)count withNewKey:(NSString *)newKey{
    NSString *statementString = @"";
    switch (type) {
        case SQLStringTypeCreate:{
            statementString = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (%@ TEXT NOT NULL PRIMARY KEY,%@ TEXT,%@ INTEGER)",self.dbTableName,kDBPrimary,kDBJson,kDBCreatTime];
        }
            break;
        case SQLStringTypeInsert:{
            NSString *jsonString = [self getJsonStringFromModel:model];
            statementString = [NSString stringWithFormat:@"INSERT INTO %@ (%@,%@,%@) VALUES ('%@','%@','%ld')",self.dbTableName,kDBPrimary,kDBJson,kDBCreatTime,self.primaryValue,jsonString,(long)[LeeFMDBManagerUtils getNowTimeTimestamp]];
        }
            break;
            
        case SQLStringTypeQuery:{
            statementString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = '%@' LIMIT 1",self.dbTableName,kDBPrimary,self.primaryValue];
        }
            break;

        case SQLStringTypeUpdate:{
            NSString *jsonString = [self getJsonStringFromModel:model];
            statementString = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@ (%@,%@,%@) VALUES ('%@','%@','%ld')",self.dbTableName,kDBPrimary,kDBJson,kDBCreatTime,self.primaryValue,jsonString,(long)[LeeFMDBManagerUtils getNowTimeTimestamp]];
        }
            break;
            
        case SQLStringTypeDelete:{
            statementString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = %@",self.dbTableName,kDBPrimary,self.primaryValue];
        }
            break;
            
        case SQLStringTypeGetAllData: {
            statementString = [NSString stringWithFormat:@"SELECT * FROM %@",self.dbTableName];
        }
            break;
       
        case SQLStringTypeReInsertKey:{
            statementString = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ TEXT",self.dbTableName,newKey];
        }
            break;
            
        default:
            break;
    }
    return statementString;
}

- (NSString *)excuteSQLStringWithType:(SQLStringType)type withModel:(id)model withDataCount:(NSUInteger)count pageNo:(NSInteger)pageNo withNewKey:(NSString *)newKey{
    NSString *statementString = @"";
    switch (type) {
        case SQLStringTypeSortAsc:{
            if (count == 0) {
                statementString = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY %@ ASC",self.dbTableName,kDBCreatTime];
            }else{
                statementString = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY %@ ASC LIMIT %lu,%lu",self.dbTableName,kDBCreatTime,pageNo*count,(unsigned long)count];
            }
        }
            break;
        case SQLStringTypeSortDesc:{
            if (count == 0) {
                statementString = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY %@ DESC",self.dbTableName,kDBCreatTime];
            }else{
                statementString = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY %@ DESC LIMIT %lu,%lu",self.dbTableName,kDBCreatTime,pageNo*count,(unsigned long)count];
            }
        }
            break;
        default:
            break;
    }
    return statementString;
}

直接贴代码

//
//  LeeFMDBManager.h
//  PeanutDiary
//
//  Created by LeeMiao on 2018/12/12.
//  Copyright © 2018 PeanutDiary. All rights reserved.
//

#import <Foundation/Foundation.h>

#define kLeeFMDBManager  [LeeFMDBManager sharedManager]

// 按照时间顺序排序
typedef NS_ENUM(NSUInteger, LeeFMDatabaseCreateTimeSortType) {
    LeeFMDatabaseCreateTimeSortTypeDESC, //-> 降序
    LeeFMDatabaseCreateTimeSortTypeASC,  //-> 升序
};


// 数据库 类型 针对业务
typedef NS_ENUM(NSUInteger, LeeFMDatabaseType) {
    LeeFMDatabaseTypeHSTopIsAcess,      //-> 花生头条
    LeeFMDatabaseTypeGoodsCollection,   //-> 花生日记产品收藏
};


@interface LeeFMDBManager : NSObject


/**
 单利

 @return 创建单利对象
 */
+ (instancetype)sharedManager;


/**
 创建表或插入一条数据
 
 * @param  model    所需存储的Model数据
 * @param  primaryValue    主键值，必须唯一 且为model中的一个对象值
 * @param  type     根据数据类型确定表名
 *
 * @return 是否创建或插入成功
 */
- (BOOL)insertDataWithModel:(id)model primaryValue:(NSString *)primaryValue databaseType:(LeeFMDatabaseType)type;


/**
 更新某一条数据
 
 * @param  model    所需存储的Model数据
 * @param  primaryValue    主键值，必须唯一 且为model中的一个对象值
 * @param  type     根据数据类型确定表名
 *
 * @return 是否创建或插入成功
 */
- (BOOL)updateDataWithModel:(id)model primaryValue:(NSString *)primaryValue databaseType:(LeeFMDatabaseType)type;



/**
 删除某一条数据
 
 * @param  primaryValue    主键值，必须唯一 且为model中的一个对象值
 * @param  type     根据数据类型确定表名
 *
 * @return 是否创建或插入成功
 */
- (BOOL)deleteDataWithPrimaryValue:(NSString *)primaryValue databaseType:(LeeFMDatabaseType)type;



/**
 根据primaryValue 查询是否存在这个数据

 @param kclass 实体
 @param primaryValue 主键值，必须唯一 且为model中的一个对象值
 @param type 根据数据类型确定表名
 @param completionHander 查询到结果后的回掉
 @return 是否查询到结果
 */
- (BOOL)queryDataWithClass:(Class)kclass primaryValue:(NSString *)primaryValue databaseType:(LeeFMDatabaseType)type completionHander:(void(^)(id dbModel,BOOL result))completionHander;


/* 获取表中全部数据总数
 *
 * @param  kclass   Model类
 * @param  type     根据数据类型确定表名
 *
 * @return 所存Model类数组
 */
- (NSInteger)getAllDataCountWithDatabaseType:(LeeFMDatabaseType)type;


/* 获取表中全部数据
 *
 * @param  kclass   Model类
 * @param  type     根据数据类型确定表名
 *
 * @return 所存Model类数组
 */
- (NSArray *)getAllDataWithModelClass:(Class)kclass databaseType:(LeeFMDatabaseType)type;



/**
 获取表中的数据 排序  分页
 
 @param kclass Model类
 @param type 根据数据类型确定表名
 @param sortType 按照时间 排序类型 升降序
 @return Model类数组
 */
- (NSArray *)getAllDataWithModelClass:(Class)kclass databaseType:(LeeFMDatabaseType)type sortType:(LeeFMDatabaseCreateTimeSortType)sortType;


/**
 获取表中的数据 排序  分页

 @param kclass Model类
 @param type 根据数据类型确定表名
 @param sortType 按照时间 排序类型 升降序
 @param pageSize 分页的大小
 @param pageNumber 页码
 @return Model类数组
 */
- (NSArray *)getDataWithModelClass:(Class)kclass databaseType:(LeeFMDatabaseType)type sortType:(LeeFMDatabaseCreateTimeSortType)sortType pageSize:(NSInteger)pageSize pageNumber:(NSInteger)pageNumber;


/**
 给表添加新的字段

 @param keyString 字段名称
 @param type 根据数据类型确定表名
 @return 是否新增成功
 */
- (BOOL)insertKeyString:(NSString *)keyString databaseType:(LeeFMDatabaseType)type;


@end

//
//  LeeFMDBManager.m
//  PeanutDiary
//
//  Created by LeeMiao on 2018/12/12.
//  Copyright © 2018 PeanutDiary. All rights reserved.
//

#import "LeeFMDBManager.h"
#import <FMDB.h>
//#import <objc/runtime.h>
#import "LeeFMDBManagerUtils.h"
#import <NSObject+YYModel.h>

typedef NS_ENUM(NSUInteger, SQLStringType) {
    SQLStringTypeCreate,            //-> 创建
    SQLStringTypeInsert,            //-> 插入
    SQLStringTypeUpdate,            //-> 更新
    SQLStringTypeQuery,             //-> 查询
    SQLStringTypeDelete,            //-> 删除
    SQLStringTypeGetAllData,        //-> 获取全部
    SQLStringTypeReInsertKey,       //-> 给数据添加新的key
    SQLStringTypeSortDesc,          //-> 降序
    SQLStringTypeSortAsc,           //-> 升序
};

static NSString *const kDBPrimary   = @"db_id";
static NSString *const kDBCreatTime = @"create_time";
static NSString *const kDBJson      = @"json";

@interface LeeFMDBManager ()

@property (nonatomic,strong) FMDatabaseQueue *baseQueue; //-> 数据库操作线程
@property (nonatomic,strong) NSString *dbFilePath;       //-> 数据库路径
@property (nonatomic,strong) NSString *dbTableName;      //-> 数据库文件名
@property (nonatomic,assign) LeeFMDatabaseType dbType;   //-> 数据库类型 确定 dbFilePath 和 dbTableName
@property (nonatomic,strong) NSString *primaryValue;     //-> 主键值

@end


//全局变量
static id _instance = nil;

@implementation LeeFMDBManager
#pragma mark - 单利
//单例方法
+(instancetype)sharedManager{
    return [[self alloc] init];
}

+(instancetype)allocWithZone:(struct _NSZone *)zone{
    //只进行一次
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        _instance = [super allocWithZone:zone];
    });
    return _instance;
}

- (instancetype)init{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        _instance = [super init];
    });
    return _instance;
}


- (id)copyWithZone:(NSZone *)zone{
    return  _instance;
}

- (id)mutableCopyWithZone:(NSZone *)zone{
    return _instance;
}

#pragma mark - 公开方法
- (BOOL)insertDataWithModel:(id)model primaryValue:(NSString *)primaryValue databaseType:(LeeFMDatabaseType)type{
    
    NSAssert(!kStringIsEmpty(primaryValue),@"primaryValue 不能为空");
    
    self.primaryValue = primaryValue;
    self.dbType = type;
    __block BOOL result;
    kDefineWeakSelf;
    [self.baseQueue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db open]) {
            // 为数据库设置缓存，提高查询效率
            [db setShouldCacheStatements:YES];
            // 判断表存在不存在
            // 如果存在 执行更新功能
            if ([db tableExists:weakSelf.dbTableName]) {
                // 创建成功
                if ([db executeUpdate:[weakSelf excuteSQLStringWithType:SQLStringTypeInsert withModel:model withDataCount:0 withNewKey:nil]]) {
                    result = YES;
                    NSLog(@"数据插入成功");
                } else {
                    result = NO;
                    NSLog(@"数据插入失败");
                }
            }else{ // 如果不存在 执行创建功能
                if ([db executeUpdate:[weakSelf excuteSQLStringWithType:SQLStringTypeCreate withModel:model withDataCount:0 withNewKey:nil]]) {
                    
                    if ([db executeUpdate:[weakSelf excuteSQLStringWithType:SQLStringTypeInsert withModel:model withDataCount:0 withNewKey:nil]]) {
                        result = YES;
                        NSLog(@"表创建成功 数据插入成功");
                    } else {
                        result = NO;
                        NSLog(@"表创建成功 数据插入失败");
                    }
                }else{
                    result = NO;
                    NSLog(@"表创建失败 数据插入失败");
                }
            }
        }else{
            [db close];
        }
        
    }];
    return result;
}


- (BOOL)insertKeyString:(NSString *)keyString databaseType:(LeeFMDatabaseType)type{
    NSAssert(!kStringIsEmpty(keyString),@"keyString 不能为空");
    self.dbType = type;
    __block BOOL result;
    kDefineWeakSelf;
    [self.baseQueue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db open]) {
            // 为数据库设置缓存，提高查询效率
            [db setShouldCacheStatements:YES];
            
            //判断是表中是否存在这个key
            if ([db columnExists:keyString inTableWithName:self.dbTableName]) {
                result = NO;
                NSLog(@"数据库存在这个字段，无需额外添加");
            }else{
                if ([db executeUpdate:[weakSelf excuteSQLStringWithType:SQLStringTypeReInsertKey withModel:nil withDataCount:0 withNewKey:keyString]]) {
                    result = YES;
                    NSLog(@"数据添加字段成功");
                }else{
                    result = NO;
                    NSLog(@"数据添加字段失败");
                }
            }
            
        }else{
            [db close];
        }
    }];
    return result;
}

- (BOOL)updateDataWithModel:(id)model primaryValue:(NSString *)primaryValue databaseType:(LeeFMDatabaseType)type{
    NSAssert(!kStringIsEmpty(primaryValue),@"primaryValue 不能为空");
    self.primaryValue = primaryValue;
    self.dbType = type;
    __block BOOL result;
    kDefineWeakSelf;
    [self.baseQueue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            if(![db tableExists:weakSelf.dbTableName]) {
                if ([db executeUpdate:[weakSelf excuteSQLStringWithType:SQLStringTypeCreate withModel:model withDataCount:0 withNewKey:nil]]) {
                    NSString *sqlStr = [weakSelf excuteSQLStringWithType:SQLStringTypeUpdate withModel:model withDataCount:0 withNewKey:primaryValue];
                    if ([db executeUpdate:sqlStr]) {
                        result = YES;
                        NSLog(@"表创建成功 数据插入成功");
                    } else {
                        result = NO;
                        NSLog(@"表创建成功 数据插入失败");
                    }
                }else{
                    result = NO;
                    NSLog(@"表创建失败 数据插入失败");
                }
            }else{
                NSString *sqlStr = [weakSelf excuteSQLStringWithType:SQLStringTypeUpdate withModel:model withDataCount:0 withNewKey:primaryValue];
                BOOL res = [db executeUpdate:sqlStr];
                if (!res) {
                    NSLog(@"数据修改失败");
                    result = NO;
                } else {
                    NSLog(@"数据修改成功");
                    result = YES;
                }
            }
           
        }
        [db close];
    }];
    return result;
}



- (BOOL)deleteDataWithPrimaryValue:(NSString *)primaryValue databaseType:(LeeFMDatabaseType)type{
    NSAssert(!kStringIsEmpty(primaryValue),@"primaryValue 不能为空");
    self.primaryValue = primaryValue;
    self.dbType = type;
    __block BOOL result;
    kDefineWeakSelf;
    [self.baseQueue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            if(![db tableExists:weakSelf.dbTableName]) {
                NSLog(@"表格不存在");
                result = NO;
            }
            NSString *sqlStr = [weakSelf excuteSQLStringWithType:SQLStringTypeDelete withModel:nil withDataCount:0 withNewKey:primaryValue];
            BOOL res = [db executeUpdate:sqlStr];
            if (!res) {
                NSLog(@"数据删除失败");
                result = NO;
            } else {
                NSLog(@"数据删除成功");
                result = YES;
            }
        }
        [db close];
    }];
    return result;
}


- (BOOL)queryDataWithClass:(Class)kclass primaryValue:(NSString *)primaryValue databaseType:(LeeFMDatabaseType)type completionHander:(void (^)(id,BOOL))completionHander{
    NSAssert(!kStringIsEmpty(primaryValue),@"primaryValue 不能为空");
    self.primaryValue = primaryValue;
    self.dbType = type;
    __block BOOL result;
    kDefineWeakSelf;
    [self.baseQueue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            
            if(![db tableExists:weakSelf.dbTableName]) {
                NSLog(@"表格不存在");
                result = NO;
                completionHander ? completionHander(nil,result): nil;
            }else{
                // 表格存在
                FMResultSet *res = [db executeQuery:[weakSelf excuteSQLStringWithType:SQLStringTypeQuery withModel:nil withDataCount:0 withNewKey:nil]];
                if (![res next]) {
                    NSLog(@"未查询到相关数据");
                    result = NO;
                    completionHander ? completionHander(nil,result): nil;
                }else{
                    id objc = [[[kclass class] alloc]init];
                    NSString __block *jsonString = @"";
                    [[res resultDictionary] enumerateKeysAndObjectsUsingBlock:^(id  _Nonnull key, id  _Nonnull obj, BOOL * _Nonnull stop) {
                        if ([key isEqualToString:kDBJson]) {
                            jsonString = [NSString stringWithFormat:@"%@",obj];
                        }
                    }];
//                    objc = [weakSelf getModel:kclass withDataDic:[LeeFMDBManagerUtils dictionaryWithJsonString:jsonString]];
                    objc = [kclass modelWithJSON:jsonString];
//                    NSLog(@"%@",objc);
                    result = YES;
                    completionHander ? completionHander(objc,result): nil;
                }
            }
           
        }
        [db close];
    }];
    return result;
}

- (NSArray *)getAllDataWithModelClass:(Class)kclass databaseType:(LeeFMDatabaseType)type sortType:(LeeFMDatabaseCreateTimeSortType)sortType{
    return [self getDataWithModelClass:kclass databaseType:type sortType:sortType pageSize:0 pageNumber:0];
}


- (NSArray *)getDataWithModelClass:(Class)kclass databaseType:(LeeFMDatabaseType)type sortType:(LeeFMDatabaseCreateTimeSortType)sortType pageSize:(NSInteger)pageSize pageNumber:(NSInteger)pageNumber{
    self.dbType = type;
    NSMutableArray *result = [[NSMutableArray alloc]init];
    kDefineWeakSelf;
    
    [self.baseQueue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            
            if(![db tableExists:weakSelf.dbTableName]) {
                NSLog(@"表格不存在");
                return;
            }
            
            SQLStringType sqlType = (sortType == LeeFMDatabaseCreateTimeSortTypeASC)? SQLStringTypeSortAsc : SQLStringTypeSortDesc;
            NSString *sqlStr = [self excuteSQLStringWithType:sqlType withModel:nil withDataCount:pageSize pageNo:pageNumber withNewKey:nil];
            FMResultSet *res = [db executeQuery:sqlStr];
            while ([res next]) {
                NSString __block *jsonString = @"";
                [[res resultDictionary] enumerateKeysAndObjectsUsingBlock:^(id  _Nonnull key, id  _Nonnull obj, BOOL * _Nonnull stop) {
                    if ([key isEqualToString:kDBJson]) {
                        jsonString = [NSString stringWithFormat:@"%@",obj];
                    }
                }];
                
                id objc = [[[kclass class] alloc]init];
//                objc = [weakSelf getModel:kclass withDataDic:[LeeFMDBManagerUtils dictionaryWithJsonString:jsonString]];
                objc = [kclass modelWithJSON:jsonString];

                [result addObject:objc];
            }
        }
        [db close];
    }];
    return [result copy];
}



- (NSArray *)getAllDataWithModelClass:(Class)kclass databaseType:(LeeFMDatabaseType)type{
    self.dbType = type;
    NSMutableArray *result = [[NSMutableArray alloc]init];
    kDefineWeakSelf;
    [self.baseQueue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            
            if(![db tableExists:weakSelf.dbTableName]) {
                NSLog(@"表格不存在");
                return;
            }
            FMResultSet *res = [db executeQuery:[self excuteSQLStringWithType:SQLStringTypeGetAllData withModel:nil withDataCount:0 withNewKey:nil]];
            while ([res next]) {
                NSString __block *jsonString = @"";
                [[res resultDictionary] enumerateKeysAndObjectsUsingBlock:^(id  _Nonnull key, id  _Nonnull obj, BOOL * _Nonnull stop) {
                    if ([key isEqualToString:kDBJson]) {
                        jsonString = [NSString stringWithFormat:@"%@",obj];
                    }
                }];
                
                id objc = [[[kclass class] alloc]init];
//                objc = [weakSelf getModel:kclass withDataDic:[LeeFMDBManagerUtils dictionaryWithJsonString:jsonString]];
                objc = [kclass modelWithJSON:jsonString];

                [result addObject:objc];
            }
        }
        [db close];
    }];
    return [result copy];
}


- (NSInteger)getAllDataCountWithDatabaseType:(LeeFMDatabaseType)type{
    self.dbType = type;
    __block NSInteger result;
    kDefineWeakSelf;
    [self.baseQueue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            if(![db tableExists:weakSelf.dbTableName]) {
                NSLog(@"表格不存在");
                result = 0;
            }else{
                NSString *sqlStr = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@",weakSelf.dbTableName];
                NSInteger res = [db intForQuery:sqlStr];
                result = res;
            }
        }
        [db close];
    }];
    return result;
}

#pragma mark - db method


- (NSString *)excuteSQLStringWithType:(SQLStringType)type withModel:(id)model withDataCount:(NSUInteger)count withNewKey:(NSString *)newKey{
    NSString *statementString = @"";
    switch (type) {
        case SQLStringTypeCreate:{
            statementString = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (%@ TEXT NOT NULL PRIMARY KEY,%@ TEXT,%@ INTEGER)",self.dbTableName,kDBPrimary,kDBJson,kDBCreatTime];
        }
            break;
        case SQLStringTypeInsert:{
            NSString *jsonString = [self getJsonStringFromModel:model];
            statementString = [NSString stringWithFormat:@"INSERT INTO %@ (%@,%@,%@) VALUES ('%@','%@','%ld')",self.dbTableName,kDBPrimary,kDBJson,kDBCreatTime,self.primaryValue,jsonString,(long)[LeeFMDBManagerUtils getNowTimeTimestamp]];
        }
            break;
            
        case SQLStringTypeQuery:{
            statementString = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = '%@' LIMIT 1",self.dbTableName,kDBPrimary,self.primaryValue];
        }
            break;

        case SQLStringTypeUpdate:{
            NSString *jsonString = [self getJsonStringFromModel:model];
            statementString = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@ (%@,%@,%@) VALUES ('%@','%@','%ld')",self.dbTableName,kDBPrimary,kDBJson,kDBCreatTime,self.primaryValue,jsonString,(long)[LeeFMDBManagerUtils getNowTimeTimestamp]];
        }
            break;
            
        case SQLStringTypeDelete:{
            statementString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = %@",self.dbTableName,kDBPrimary,self.primaryValue];
        }
            break;
            
        case SQLStringTypeGetAllData: {
            statementString = [NSString stringWithFormat:@"SELECT * FROM %@",self.dbTableName];
        }
            break;
       
        case SQLStringTypeReInsertKey:{
            statementString = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ TEXT",self.dbTableName,newKey];
        }
            break;
            
        default:
            break;
    }
    return statementString;
}


- (NSString *)excuteSQLStringWithType:(SQLStringType)type withModel:(id)model withDataCount:(NSUInteger)count pageNo:(NSInteger)pageNo withNewKey:(NSString *)newKey{
    NSString *statementString = @"";
    switch (type) {
        case SQLStringTypeSortAsc:{
            if (count == 0) {
                statementString = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY %@ ASC",self.dbTableName,kDBCreatTime];
            }else{
                statementString = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY %@ ASC LIMIT %u,%lu",self.dbTableName,kDBCreatTime,pageNo*count,(unsigned long)count];
            }
        }
            break;
        case SQLStringTypeSortDesc:{
            if (count == 0) {
                statementString = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY %@ DESC",self.dbTableName,kDBCreatTime];
            }else{
                statementString = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY %@ DESC LIMIT %u,%lu",self.dbTableName,kDBCreatTime,pageNo*count,(unsigned long)count];
            }
        }
            break;
        default:
            break;
    }
    return statementString;
}


// 实体转json字符串
- (NSString *)getJsonStringFromModel:(id)model{
//    NSLog(@"%@",[model modelToJSONString]);
    return [model modelToJSONString];
}


#pragma mark - 私有方法
- (FMDatabaseQueue *)baseQueue{
    if (!_baseQueue) {
        _baseQueue = [FMDatabaseQueue databaseQueueWithPath:self.dbFilePath];
    }
    return _baseQueue;
}

- (NSString *)dbTableName{
    switch (self.dbType) {
        case LeeFMDatabaseTypeHSTopIsAcess:
            return @"FMDB_HotTopStatus";
        case LeeFMDatabaseTypeGoodsCollection:
            return @"FMDB_UserCollectionGoodsList";
        default:
            break;
    }
}

- (NSString *)dbFilePath{
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) firstObject];
    NSLog(@"数据库存储位置：%@",documentPath);
    return  [documentPath stringByAppendingPathComponent:[NSString stringWithFormat:@"%@.db",self.dbTableName]];
}



@end

FMDB 简单封装

思路

SQL基本语法

1、创建表

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读