//
// 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>
/**
數(shù)據(jù)庫管理
- (void)createTables 中使用model創(chuàng)建表
*/
@interface WJDataBaseManager : NSObject
/**
- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
在app啟動時初始化供屉。例如：[[WJDataBaseManager shareManager] registerTables:@[@"WJTestModel1",@"WJTestModel2"]];
*/
+ (instancetype)shareManager;
/**
創(chuàng)建表（使用model名創(chuàng)建對應(yīng)表）,model名 -> table名
注意事項:一個model對應(yīng)一張表羡铲，因此一張表只會被創(chuàng)建一次忿族，model往后的修改不會被重新創(chuàng)建误堡，可使用新model名創(chuàng)建新的table.
表的設(shè)計需慎重速挑，考慮app版本兼容性回铛，建議不要修改model派昧，而使用新的model脚祟。
@param tables 存放model名稱的數(shù)組。
*/
- (void)registerTables:(NSArray <NSString*>*)tables;
/**
* 插入數(shù)據(jù)
*
* param model 模型
* param finishBlock 插入完成回調(diào)
*/
-(void)insert:(id)model FinishBlock:(void (^)(bool issuccess))finishBlock;
/**
* 更新數(shù)據(jù)庫
*
* param model 模型
* param where 條件
* param finishBlock 更新完成回調(diào)
*
* return 是否成功
*/
-(void)update:(id)model Where:(NSString*)where FinishBlock:(void (^)(bool issuccess))finishBlock;
/**
* 查詢數(shù)據(jù)庫
*
* param model 模型
* param where 條件
* param order 排序
* param limit 限制行數(shù)
*
* return 模型數(shù)組
*/
-(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;
/**
* 查詢表總記錄數(shù)
*
* param model 模型
*
* return 總數(shù)
*/
-(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;
// 刪除表數(shù)據(jù)
-(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 創(chuàng)建數(shù)據(jù)庫
- (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);
// 創(chuàng)建表
[self createTables];
}
- (void)registerTables:(NSArray *)tables{
for (NSString *table in tables) {
if(NSClassFromString(table)){
[self createTableWithModel:NSClassFromString(table)];
}
}
}
- (void)createTables{
//根據(jù)model創(chuàng)建表
// [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 數(shù)據(jù)庫操作
// 插入數(shù)據(jù)
-(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);
}
}];
}
// 更新數(shù)據(jù)
-(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;
}
// 獲取表記錄總數(shù)
-(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;
}
// 通過模型創(chuàng)建表
-(void)createTableWithModel:(id)model{
if (![self isExit:model]) {
NSString *s = [self createSqlWithTableModel:[model class]];
[_db executeUpdate:s];
}
}
// 通過模型生成創(chuàng)建表語句
-(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]];
}
// 數(shù)字
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
