//
// WJDataBaseManager.h
// wenjin
//
// Created by Kevin on 2017/4/17.
// Copyright © 2017年 Hangzhou Chuanwoqi Internet Technology Co., Ltd. All rights reserved.
//
#import <Foundation/Foundation.h>
/**
数据库管理
- (void)createTables 中使用model创建表
*/
@interface WJDataBaseManager : NSObject
/**
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
在app启动时初始化。例如：[[WJDataBaseManager shareManager] registerTables:@[@"WJTestModel1",@"WJTestModel2"]];
*/
+ (instancetype)shareManager;
/**
创建表（使用model名创建对应表）,model名 -> table名
注意事项:一个model对应一张表，因此一张表只会被创建一次，model往后的修改不会被重新创建，可使用新model名创建新的table.
表的设计需慎重，考虑app版本兼容性，建议不要修改model，而使用新的model。
@param tables 存放model名称的数组。
*/
- (void)registerTables:(NSArray <NSString*>*)tables;
/**
* 插入数据
*
* param model 模型
* param finishBlock 插入完成回调
*/
-(void)insert:(id)model FinishBlock:(void (^)(bool issuccess))finishBlock;
/**
* 更新数据库
*
* param model 模型
* param where 条件
* param finishBlock 更新完成回调
*
* return 是否成功
*/
-(void)update:(id)model Where:(NSString*)where FinishBlock:(void (^)(bool issuccess))finishBlock;
/**
* 查询数据库
*
* param model 模型
* param where 条件
* param order 排序
* param limit 限制行数
*
* return 模型数组
*/
-(NSArray*)select:(id)model Where:(NSString*)where Order:(NSString*)order Limit:(NSString*)limit;
/**
* 删除
*
* param model 模型
* param where 条件
*
* return 删除是否成功
*/
-(BOOL)delete:(id)model Where:(NSString*)where;
/**
* 查询表总记录数
*
* param model 模型
*
* return 总数
*/
-(NSInteger)getCount:(id)model;
-(NSInteger)getCount:(id)model where:(NSString*)where;
-(NSInteger)getSum:(id)model Filed:(NSString*)filed where:(NSString*)where;
// 表是否存在
-(BOOL)isExit:(id)model;
// 删除表数据
-(void)clearTable:(id)model;
@end
//
// WJDataBaseManager.m
// wenjin
//
// Created by Kevin on 2017/4/17.
// Copyright © 2017年 Hangzhou Chuanwoqi Internet Technology Co., Ltd. All rights reserved.
//
#import "WJDataBaseManager.h"
#import <MJExtension/MJExtension.h>
#define WJ_DATA_BASE_NAME @"WJ_DATA_BASE_NAME.db"
@interface WJDataBaseManager ()
@property (nonatomic,strong) FMDatabase *db;
@property (nonatomic,strong) FMDatabaseQueue *queue;
@end
@implementation WJDataBaseManager
+(instancetype)shareManager{
static WJDataBaseManager *instance = nil;
static dispatch_once_t oneToken;
dispatch_once(&oneToken, ^{
instance = [[WJDataBaseManager alloc] init];
});
return instance;
}
- (instancetype)init{
self = [super init];
if (self) {
[self createDB];
}
return self;
}
#pragma mark -
#pragma mark 创建数据库
- (void)createDB{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentDirectory = [paths objectAtIndex:0];
NSString *dbPath = [documentDirectory stringByAppendingPathComponent:WJ_DATA_BASE_NAME];
_queue = [FMDatabaseQueue databaseQueueWithPath:dbPath];
_db = [FMDatabase databaseWithPath:dbPath] ;
if (![_db open]) {
NSLog(@"Could not open db.");
return ;
}
NSLog(@"database path : %@",dbPath);
// 创建表
[self createTables];
}
- (void)registerTables:(NSArray *)tables{
for (NSString *table in tables) {
if(NSClassFromString(table)){
[self createTableWithModel:NSClassFromString(table)];
}
}
}
- (void)createTables{
//根据model创建表
// [self createTableWithModel:[WJMyStocksModel class]];
}
-(void)removeDatabase{
[_db close];
// 直接删除文件
NSFileManager *fileManager = [NSFileManager defaultManager];
NSString *dbPath = _db.databasePath;
if ([fileManager fileExistsAtPath:dbPath]) {
[fileManager removeItemAtPath:dbPath error:nil];
}
}
-(void)dropTable:(id)model{
NSString *table = NSStringFromClass([model class]);
NSString *sql = [NSString stringWithFormat:@"DROP TABLE IF EXISTS %@",table];
[_db executeUpdate:sql];
}
-(void)clearTable:(id)model{
NSString *table = NSStringFromClass([model class]);
NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@",table];
[_db executeUpdate:sql];
}
#pragma mark -
#pragma mark 数据库操作
// 插入数据
-(void)insert:(id)model FinishBlock:(void (^)(bool issuccess))finishBlock{
NSString *fileds = @"";
NSArray *keys = [self propertyKeysWithClass:[model class]];
NSString *values = @"";
for (NSString *key in keys) {
NSString* value = [NSString stringWithFormat:@"%@",[model valueForKey:key]];
if ([value isEqualToString:@"(null)"] || !value) {
value = @"";
}
if ([fileds isEqualToString:@""]) {
fileds = [fileds stringByAppendingFormat:@"%@",key];
values = [values stringByAppendingFormat:@"'%@'",value];
}else{
fileds = [fileds stringByAppendingFormat:@",%@",key];
values = [values stringByAppendingFormat:@",'%@'",value];
}
}
NSString *table = NSStringFromClass([model class]);
NSString *sql = [[NSString alloc]
initWithFormat:@"INSERT INTO %@ (%@) Values(%@)",table,fileds,values];
// 加入队列
[_queue inDatabase:^(FMDatabase *dbs){
bool success = [_db executeUpdate:sql];
if (finishBlock) {
finishBlock(success);
}
}];
}
// 更新数据
-(void)update:(id)model Where:(NSString*)where FinishBlock:(void (^)(bool issuccess))finishBlock{
NSString *fileds = @"";
NSArray *keys = [self propertyKeysWithClass:[model class]];
for (NSString *key in keys) {
NSString* value = [NSString stringWithFormat:@"%@",[model valueForKey:key]];
if (![value isEqualToString:@"(null)"] && value) {
if ([fileds isEqualToString:@""]) {
fileds = [fileds stringByAppendingFormat:@"%@='%@'",key,value];
}else{
fileds = [fileds stringByAppendingFormat:@",%@='%@'",key,value];
}
}
}
NSString *table = NSStringFromClass([model class]);
NSString *sql = [[NSString alloc]
initWithFormat:@"UPDATE %@ SET %@",table,fileds];
if (where) {
sql = [sql stringByAppendingFormat:@" WHERE %@",where];
}
// 加入队列
[_queue inDatabase:^(FMDatabase *dbs){
bool success = [_db executeUpdate:sql];
if (finishBlock) {
finishBlock(success);
}
}];
}
// 查询
-(NSArray*)select:(id)model Where:(NSString*)where Order:(NSString*)order Limit:(NSString*)limit{
NSString *table = NSStringFromClass([model class]);
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@",table];
if (where) {
sql = [sql stringByAppendingFormat:@" WHERE %@",where];
}
if (order) {
sql = [sql stringByAppendingFormat:@" ORDER BY %@",order];
}
if (limit) {
sql = [sql stringByAppendingFormat:@" LIMIT %@",limit];
}
NSMutableArray *array = [NSMutableArray new];
FMResultSet *rs = [_db executeQuery:sql];
while ([rs next]) {
NSDictionary *dic = [rs resultDictionary];
id m = [[model class] mj_objectWithKeyValues:dic];
[array addObject:m];
dic = nil;
m = nil;
}
return array;
}
// 删除
-(BOOL)delete:(id)model Where:(NSString*)where{
NSString *table = NSStringFromClass([model class]);
NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@",table];
if (where) {
sql = [sql stringByAppendingFormat:@" WHERE %@",where];
}
BOOL success = [_db executeUpdate:sql];
return success;
}
// 获取表记录总数
-(NSInteger)getCount:(id)model{
NSString *table = NSStringFromClass([model class]);
NSInteger count = 0;
NSString *sqlStr = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@",table];
count = [_db intForQuery:sqlStr];
return count;
}
-(NSInteger)getCount:(id)model where:(NSString*)where{
NSString *table = NSStringFromClass([model class]);
NSInteger count = 0;
NSString *sql = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@",table];
if (where) {
sql = [sql stringByAppendingFormat:@" WHERE %@",where];
}
count = [_db intForQuery:sql];
return count;
}
-(NSInteger)getSum:(id)model Filed:(NSString*)filed where:(NSString*)where{
NSString *table = NSStringFromClass([model class]);
NSInteger count = 0;
NSString *sql = [NSString stringWithFormat:@"SELECT SUM(%@) FROM %@",filed,table];
if (where) {
sql = [sql stringByAppendingFormat:@" WHERE %@",where];
}
count = [_db intForQuery:sql];
return count;
}
// 通过模型创建表
-(void)createTableWithModel:(id)model{
if (![self isExit:model]) {
NSString *s = [self createSqlWithTableModel:[model class]];
[_db executeUpdate:s];
}
}
// 通过模型生成创建表语句
-(NSString*)createSqlWithTableModel:(id)model{
NSArray *propertys = [self propertyKeysWithClass:[model class]];
NSString *keys = @"";
NSString *className = NSStringFromClass([model class]);
NSString *sql = [NSString stringWithFormat:@"CREATE TABLE %@ (",className];
for (NSString *key in propertys) {
// 字符串
if ([[key class] isSubclassOfClass:[NSString class]]) {
keys = [keys stringByAppendingString:[NSString stringWithFormat:@"%@ text,",key]];
}
// 数字
if ([[key class] isSubclassOfClass:[NSNumber class]]) {
keys = [keys stringByAppendingString:[NSString stringWithFormat:@"%@ integer,",key]];
}
}
if ([[keys substringFromIndex:keys.length-1] isEqualToString:@","]) {
keys = [keys substringToIndex:keys.length-1];
}
sql = [sql stringByAppendingString:[NSString stringWithFormat:@"%@)",keys]];
return sql;
}
// 表是否存在
-(BOOL)isExit:(id)model{
NSString *table = NSStringFromClass([model class]);
BOOL exit = NO;
exit = [_db intForQuery:@"select count(*) as 'count' from sqlite_master where type ='table' and name = ?",table];
return exit;
}
// 反射对象所有属性
- (NSArray*)propertyKeysWithClass:(Class)classs
{
unsigned int outCount, i;
objc_property_t *properties = class_copyPropertyList(classs, &outCount);
NSMutableArray *keys = [[NSMutableArray alloc] initWithCapacity:outCount];
for (i = 0; i < outCount; i++) {
objc_property_t property = properties[i];
NSString *propertyName = [[NSString alloc] initWithCString:property_getName(property) encoding:NSUTF8StringEncoding];
[keys addObject:propertyName];
}
free(properties);
return keys;
}
@end
