ShengQianProject/YouHuiProject/YouHui/Tools/YDFMDB/YDFMDB.m

//
//  YDFMDB.m
//  YUEDU
//
//  Created by jcymac on 2017/12/6.
//  Copyright © 2017年 kuxuan. All rights reserved.
//

#import "YDFMDB.h"
#import <FMDB.h>
@interface YDFMDB()
@property(nonatomic,strong) FMDatabase *jcyDB;
@end

@implementation YDFMDB
+(instancetype)sharedInstance{
    
    static YDFMDB *YDDB = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        YDDB = [[YDFMDB alloc]init];
    });
    return YDDB;
}
-(FMDatabase *)jcyDB{
    if (!_jcyDB) {
        NSString *filePath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"jcydb.sqlite"];
        NSLog(@"数据库路径:\n          %@",filePath);
        _jcyDB= [FMDatabase databaseWithPath:filePath];
    }
    return _jcyDB;
}

-(BOOL)executeUpdateByString:(NSString *)sql{
    BOOL isSucc=NO;
    [self.jcyDB open];
    isSucc=[self.jcyDB executeUpdate:sql];
    [self.jcyDB close];
    return isSucc;
}



- (BOOL)createTableName:(NSString *)tableName fieldNamesDictionaryArray:(NSArray*)fieldNamesDictionaryArray{
    if ( nil==tableName|| [@"" isEqualToString:tableName]) {
        NSLog(@"表名错误");
        return NO;
    }
    //创建表 添加id  创建时间  token字段
    [self.jcyDB open];
    BOOL isSucc = [self.jcyDB executeUpdate:[NSString stringWithFormat:@"create table if not exists %@(myId integer primary key autoincrement,createdTime TEXT NOT NULL);",tableName]];
    [self.jcyDB close];
    if (isSucc) {
        if (!(nil==fieldNamesDictionaryArray||0==fieldNamesDictionaryArray.count)) {
            isSucc=[self addFieldNamesDictionaryArray:fieldNamesDictionaryArray withTableName:tableName];
        }
    }else{
        NSLog(@"创建表失败");
    }
    return isSucc;
}

-(BOOL)dropTableByTableName:(NSString *)tableName{
    BOOL isSucc=NO;
    if ( nil==tableName|| [@"" isEqualToString:tableName]) {
        NSLog(@"表名错误");
        return isSucc;
    }
    [self.jcyDB open];
    isSucc=[self.jcyDB executeUpdate:[NSString stringWithFormat:@"DROP TABLE %@",tableName]];
    if (!isSucc) {
        NSLog(@"删除表失败!请检查:\n(1)表名是否错误(已经被删除的表，或者拼写错误);\n(2)该表被其他表关联(外键);\n(3)其余错误");
    }
    [self.jcyDB close];
    return isSucc;
}

//添加字段
- (BOOL)addFieldNamesDictionaryArray:(NSArray*)fieldNamesDictionaryArray withTableName:(NSString *)tableName{
    BOOL isSucc=NO;
    if (nil==fieldNamesDictionaryArray||fieldNamesDictionaryArray.count==0 || nil==tableName || [tableName isEqualToString:@""]) {
        NSLog(@"缺失字段或者表名");
        return isSucc;
    }
    [self.jcyDB open];
    for (NSDictionary *fieldName in fieldNamesDictionaryArray) {
        NSString *name=fieldName[@"name"];
        NSString *type=fieldName[@"type"];
        if (nil==name||nil==type) {
            NSLog(@"传入字典数组错误，两个key分别为:name与type");
            return isSucc;
        }
        isSucc= [self.jcyDB columnExists:name inTableWithName:tableName];
        if (!isSucc) {
            NSString *str = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@  %@;",tableName,name,type];
            NSLog(@"添加字段 sql:%@",str);
            isSucc= [self.jcyDB executeUpdate:str];
        }else{
            NSLog(@"有重复字段");
            break;
        }
        if (!isSucc) {
            break;
        }
    }
    [self.jcyDB close];
    return isSucc;
}
//删除字段
- (BOOL)deleteFieldNamesStringArray:(NSArray*)fieldNamesStringArray withTableName:(NSString *)tableName;{
    BOOL isSucc=NO;
    if (nil==fieldNamesStringArray||fieldNamesStringArray.count==0 || nil==tableName || [tableName isEqualToString:@""]) {
        NSLog(@"缺失字段或者表名");
        return isSucc;
    }
    [self.jcyDB open];
    for (NSString *name in fieldNamesStringArray) {
        NSString *str = [NSString stringWithFormat:@"ALTER TABLE %@ DROP COLUMN %@;",tableName,name];
        isSucc= [self.jcyDB executeUpdate:str];
        if (!isSucc) {
            NSLog(@"添加字段失败");
            break;
        }
    }
    [self.jcyDB close];
    return isSucc;
}


//增加数据  传递NSArray(key value)
- (BOOL)addSingleDataStringDictionary:(NSDictionary *)dataStringDictionary withTableName:(NSString *)tableName{
    BOOL isSucc=NO;
    if (nil==dataStringDictionary||dataStringDictionary.count==0 || nil==tableName || [tableName isEqualToString:@""]) {
        NSLog(@"缺失字段或者表名");
        return isSucc;
    }
    NSString *keyStr=[self linkStringBySymbol:@"," withNSArray:[dataStringDictionary allKeys] onBothSides:@""];
    NSString *valueStr=[self linkStringBySymbol:@"," withNSArray:[dataStringDictionary allValues] onBothSides:@"'"];
    /*************/
    if (keyStr && valueStr) {
        [self.jcyDB open];
        NSString *str=[NSString stringWithFormat:@"insert into %@(%@,%@) values(%@,'%@');",tableName,keyStr,@"createdTime",valueStr,[NSString stringWithFormat:@"%ld", (long)[[NSDate date] timeIntervalSince1970]]];
        isSucc=[self.jcyDB executeUpdate:str];
        NSLog(@"添加sql:%@ 状态:%@",str,@(isSucc));
        [self.jcyDB close];
    }else{
        NSLog(@"添加失败");
    }
    
    return isSucc;
}
//删除数据  key value
- (BOOL)deleteDataByEqualConditionStringDictionary:(NSDictionary *)conditionStringDictionary withTableName:(NSString *)tableName{
    BOOL isSucc=NO;
    if (nil==tableName || [tableName isEqualToString:@""]) {
        NSLog(@"缺失表名");
        return isSucc;
    }
    //连接条件
    NSString *conditionString;
    NSMutableArray *conditionArray=[[NSMutableArray alloc]init];
    for (NSString *key in conditionStringDictionary) {
        [conditionArray addObject:[self linkStringByEqualSymbolWithKey:key withValue:conditionStringDictionary[key]]];
    }
    conditionString=[self linkStringBySymbol:@" and " withNSArray:conditionArray onBothSides:@""];//获取条件
    //删除
    isSucc=[self deleteDataByConditionString:conditionString withTableName:tableName];
    return isSucc;
}

- (BOOL)deleteDataByConditionString:(NSString *)conditionString withTableName:(NSString *)tableName{
    BOOL isSucc=NO;
    if (nil==tableName || [tableName isEqualToString:@""]) {
        NSLog(@"缺失表名");
        return isSucc;
    }
    [self.jcyDB open];
    if (conditionString&&![@"" isEqualToString:conditionString]) {
        NSString *str=[NSString stringWithFormat:@"delete from %@  where %@;",tableName,conditionString];
        isSucc=[self.jcyDB executeUpdate:str];
        NSLog(@"有条件删除sql:%@ 状态:%@",str,@(isSucc));
    }else{
        NSLog(@"无条件删除");
        NSString *str=[NSString stringWithFormat:@"delete from %@;",tableName];
        NSLog(@"无条件删除sql:%@ 状态:%@",str,@(isSucc));
        isSucc=[self.jcyDB executeUpdate:str];
    }
    [self.jcyDB close];
    return isSucc;
}





//更改数据
- (BOOL)changeDataByEqualConditionStringDictionary:(NSDictionary *)conditionStringDictionary withNewStringDictionary:(NSDictionary *)newStringDictionary withTableName:(NSString *)tableName{
    BOOL isSucc=NO;
    if (nil==newStringDictionary||newStringDictionary.count==0 || nil==tableName || [tableName isEqualToString:@""]) {
        NSLog(@"缺失字段或者表名");
        return isSucc;
    }
    //连接新数据
    NSString *newDataString;
    NSMutableArray *newDataArray=[[NSMutableArray alloc]init];
    for (NSString *key in newStringDictionary) {
        [newDataArray addObject:[self linkStringByEqualSymbolWithKey:key withValue:newStringDictionary[key]]];
    }
    newDataString=[self linkStringBySymbol:@"," withNSArray:newDataArray onBothSides:@""];//获取新数据字符串
    //连接条件
    NSString *conditionString;
    NSMutableArray *conditionArray=[[NSMutableArray alloc]init];
    for (NSString *key in conditionStringDictionary) {
        [conditionArray addObject:[self linkStringByEqualSymbolWithKey:key withValue:conditionStringDictionary[key]]];
    }
    conditionString=[self linkStringBySymbol:@" and " withNSArray:conditionArray onBothSides:@""];//获取条件
    isSucc=[self changeDataByConditionString:conditionString withNewString:newDataString withTableName:tableName];
    return isSucc;
}


- (BOOL)changeDataByConditionString:(NSString *)conditionString withNewString:(NSString *)newString withTableName:(NSString *)tableName{
    BOOL isSucc=NO;
    if (nil==newString||[@"" isEqualToString:newString] || nil==tableName || [tableName isEqualToString:@""]) {
        NSLog(@"缺失字段或者表名");
        return isSucc;
    }
    [self.jcyDB open];
    if (conditionString&&![@"" isEqualToString:conditionString]) {
        
        NSString *str=[NSString stringWithFormat:@"UPDATE  %@ SET %@ where %@;",tableName,newString,conditionString];
        isSucc=[self.jcyDB executeUpdate:str];
        NSLog(@"有条件修改SQL:%@ 状态:%@",str,@(isSucc));
    }else{
        
        NSString *str=[NSString stringWithFormat:@"UPDATE  %@ SET %@;",tableName,newString];
        isSucc=[self.jcyDB executeUpdate:str];
        NSLog(@"无条件修改SQL:%@ 状态:%@",str,@(isSucc));
    }
    
    [self.jcyDB close];
    return isSucc;
}



//查询数据
- (NSArray *)selectDataByEqualConditionStringDictionary:(NSDictionary *)conditionStringDictionary  withSelectStringArray:(NSArray *)selectStringArray withTableName:(NSString *)tableName limit:(NSInteger)limit descID:(BOOL)descID{
    NSArray *arr=[[NSArray alloc]init];
    if (nil==tableName || [tableName isEqualToString:@""]) {
        NSLog(@"缺失表名");
        return nil;
    }
    NSString *conditionString;
    NSMutableArray *conditionArray=[[NSMutableArray alloc]init];
    for (NSString *key in conditionStringDictionary) {
        [conditionArray addObject:[self linkStringByEqualSymbolWithKey:key withValue:conditionStringDictionary[key]]];
    }
    conditionString=[self linkStringBySymbol:@" and " withNSArray:conditionArray onBothSides:@""];//获取条件
    arr=[self selectDataByConditionString:conditionString withSelectStringArray:selectStringArray withTableName:tableName limit:limit descID:descID];
    return [arr copy];
}



- (NSArray *)selectDataByConditionString:(NSString *)conditionString  withSelectStringArray:(NSArray *)selectStringArray withTableName:(NSString *)tableName limit:(NSInteger)limit descID:(BOOL)descID{
    NSMutableArray *arr=[[NSMutableArray alloc]init];
    FMResultSet *set;
    [self.jcyDB open];
    
    NSString *strDest=@"";
    NSString *strLimit=@"";
    if (descID) {
        strDest=@"order by myId desc";
    }
    if (limit>0) {
        strLimit=[NSString stringWithFormat:@" limit %@",[@(limit) stringValue]];
    }
    if (conditionString&&![@"" isEqualToString:conditionString]) {
        
        NSString *str=[NSString stringWithFormat:@"select *  from %@ where %@ %@ %@;",tableName,conditionString,strDest,strLimit];
        set=[self.jcyDB executeQuery:str];
        NSLog(@"有条件查询SQL%@ ",str);
    }else{
        NSString *str=[NSString stringWithFormat:@"select *  from %@ %@ %@;",tableName,strDest,strLimit];
        set=[self.jcyDB executeQuery:str];
        NSLog(@"无条件查询SQL%@ ",str);
    }
    
    if (set) {//查询到数据
        while ([set next]) {
            NSMutableDictionary *dic=[[NSMutableDictionary alloc]init];
            for (NSString *key in selectStringArray) {
                [dic setValue:[set stringForColumn:key] forKey:key];
            }
            [arr addObject:dic];
        }
    }else{
        NSLog(@"没有查询到数据");
    }
    [self.jcyDB close];
    return [arr copy];
}



#pragma mark -私有方法
-(NSArray *)addSingleQuotationMarksWithArray:(NSArray *)array{
    NSMutableArray *newArray=[[NSMutableArray alloc]init];
    for (NSString *str in array) {
        NSString *newStr=[NSString stringWithFormat:@"'%@'",str];
        [newArray addObject:newStr];
    }
    return newArray;
}
                      
//利用逗号连接
-(NSString *)linkStringBySymbol:(NSString *)symbol withNSArray:(NSArray *)array onBothSides:(NSString *)bothSymbol{//这里可以默认写入token time等基本信息
    NSMutableString *str=[[NSMutableString alloc]initWithString:@""];
    for (NSUInteger i=0; i<array.count; i++) {
        if (i==0) {
            [str appendFormat:@"%@%@%@",bothSymbol,array[i],bothSymbol];
        }else{
            [str appendFormat:@" %@ %@%@%@",symbol,bothSymbol,array[i],bothSymbol];
        }
    }
    return [str copy];
}

//利用等号连接
-(NSString *)linkStringByEqualSymbolWithKey:(NSString *)key withValue:(NSString *)value{
    NSMutableString *str=[[NSMutableString alloc]initWithString:@""];
    [str appendFormat:@" %@",key];
    [str appendFormat:@"='%@'",value];
    return [str copy];
}


@end