对FMDB进行的二次封装

对FMDB进行的二次封装类 

//

//  YCFmdbOperate.h

//  Ycfmdb

//

//  Created by LoveYC on 2017/4/12.

//  Copyright © 2017年 snjj. All rights reserved.

//

/*

 对FMDB进行的二次封装

 */

#import <Foundation/Foundation.h>

@interface YCFmdbOperate : NSObject

//单例

+ (YCFmdbOperate *)shareOperate;

//操作表，除了查询

- (void)operateTable:(NSString *)sql sth:(id)sth finished:(void(^)(BOOL isok))finished;

//查询

- (void)operateSelect:(NSString *)sql finished:(void(^)(NSArray *array))finished;

//方便打印数据库路径

- (void)check;

//是否存在这张表

- (BOOL)operateIsExistsTable:(NSString *)tableName;

//表里面是否有记录

- (void)operateIsNullTable:(NSString *)tableName finished:(void(^)(BOOL isNullTable))finished;

@end

/*************************************************************************/

//

//  YCFmdbOperate.m

//  Ycfmdb

//

//  Created by LoveYC on 2017/4/12.

//  Copyright © 2017年 snjj. All rights reserved.

//

#import "YCFmdbOperate.h"

#import <sqlite3.h>

#import "FMDB.h"

@interface YCFmdbOperate ()

@property (nonatomic,strong)NSString *dbName;

@property (nonatomic,strong)NSString *dbPath;

@property (nonatomic,strong)FMDatabaseQueue *dbQueue;

@property (nonatomic,strong)FMDatabase *db;

@end

@implementation YCFmdbOperate

static YCFmdbOperate *yc =nil;

+ (YCFmdbOperate *)shareOperate{

    staticdispatch_once_t onceToken;

    dispatch_once(&onceToken, ^{

        yc = [[YCFmdbOperatealloc]init];

        yc.dbName =@"YCFMDB.sqlite";

        

    });

    returnyc;

}

- (FMDatabase *)db{

    if (!_db) {

        

        NSString *filePath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) lastObject]stringByAppendingPathComponent:@"TJYMeInfo"];

        NSFileManager *f = [NSFileManagerdefaultManager];

        if (![ffileExistsAtPath:filePath]) {

            [f createDirectoryAtPath:filePathwithIntermediateDirectories:YESattributes:nilerror:nil];

        }

        NSString *path = [filePathstringByAppendingPathComponent:[NSStringstringWithFormat:@"/%@",_dbName]];

        _dbPath = path;

        _db = [FMDatabasedatabaseWithPath:path];

        

    }

    if ([_dbopen]) {

        return_db;

    }

    return_db;

}

#pragma mark - 用来找印

- (void)check{

    NSLog(@"db = %@,dbpath = %@",self.db,self.dbPath);

    if (![self.dbopen]) {

        NSLog(@"database can not open !");

        return ;

    };

    

}

#pragma mark - 表是否存在 (只能用于查看本表是否存在)

- (BOOL)operateIsExistsTable:(NSString *)tableName{

    if ([self.dbtableExists:tableName]) {

        returnYES;

    }else {

        returnNO;

    }

}

#pragma mark - 检查表里面是否有记录（即能查看本表是否存在，也能用于表里面是否有记录和上面方法一起结合使用最佳）

- (void)operateIsNullTable:(NSString *)tableName finished:(void(^)(BOOL isNullTable))finished{

    

    NSString *sql = [NSStringstringWithFormat:@"select * from %@",tableName];

    [[YCFmdbOperateshareOperate] operateSelect:sqlfinished:^(NSArray *array) {

        

        if (array.count < 1) {

            if (finished) {

                finished(YES);

            }

        }else {

            if (finished) {

                finished(NO);

            }

        }

    }];

}

#pragma mark - 操作表 (增删改)

- (void)operateTable:(NSString *)sql sth:(id)sth finished:(void(^)(BOOL isok))finished{

    if (!sth) {

        [selfoperateTable:sql finished:finished];

        return;

    }

    if ([sthisKindOfClass:[NSArrayclass]]) {

        [selfoperateTable:sql array:sth finished:finished];

        return;

    }

    if ([sthisKindOfClass:[NSDictionaryclass]]) {

        [selfoperateTable:sql dictionary:sth finished:finished];

        return;

    }

}

#pragma mark - 查询语句

- (void)operateSelect:(NSString *)sql finished:(void(^)(NSArray *array))finished{

    

    FMResultSet *s = [self.dbexecuteQuery:sql];

    NSMutableArray *array = [NSMutableArrayarray];

    while ([snext]) {

        NSDictionary *dic = [sresultDictionary];

        [array addObject:dic];

    }

    if (finished) {

        finished(array);

    }

}

/* ------------------------------------------ */

#pragma mark - 私有方法

- (void)operateTable:(NSString *)sql finished:(void(^)(BOOL ))finished{

    

    if ([self.dbexecuteUpdate:sql]) {

        if (finished) {

            finished(YES);

        }

    }else{

        if (finished) {

            finished(NO);

        }

    }

}

- (void)operateTable:(NSString *)sql array:(NSArray *)array finished:(void(^)(BOOL ))finished{

    

    if ([self.dbexecuteUpdate:sql withArgumentsInArray:array]) {

        if (finished) {

            finished(YES);

        }

    }else{

        if (finished) {

            finished(NO);

        }

    }

    

}

- (void)operateTable:(NSString *)sql dictionary:(NSDictionary *)dic finished:(void(^)(BOOL ))finished{

    if ([self.dbexecuteUpdate:sql withParameterDictionary:dic]) {

        if (finished) {

            finished(YES);

        }

    }else{

        if (finished) {

            finished(NO);

        }

    }

}

@end

/**************************************************************************/

//

//  YCSqliteManager.h

//  TJY

//

//  Created by MacAir on 2017/4/12.

//  Copyright © 2017年 snjj. All rights reserved.

//

/*

    这个类是用来集中操作数库存取 

    代码中的数据库操作代码集中写在这儿

 */

#import <Foundation/Foundation.h>

#import "YCChildModel.h"

@interface YCSqliteManager : NSObject

#pragma mark - 过期表操作

+(NSString *)futureTimeDay:(CGFloat)day;

+ (BOOL)isOutTime:(NSString *)tableName outTime:(NSString *)outTimeStr finish:(void(^)(BOOL isOut))finish;

#pragma mark - 分类类型表操作

+ (void)saveTemplateClass:(id)sth;

+ (void)getTemplateClss:(void(^)(NSArray *array))finish;

+ (void)updateTemplateClass:(NSString *)chageStr finished:(void(^)(BOOL ok))finish;

#pragma mark - //游记更换模板表大类

+ (void)saveNoteTemplateTable:(id)sth;

+ (void)getNoteTemplateTable:(void(^)(NSArray *array))finish;

+ (void)saveNoteTemplateDetailTable:(id)sth;

#pragma mark - 孩子信息表

+ (void)saveChildTable:(id)sth;

+ (void)getChildTable:(void(^)(NSArray *array))finish;

+ (void)updateChildTable:(YCChildModel *)model finish:(void(^)(BOOL isok))finish;

+ (void)deleteChildTable:(NSInteger)childId finish:(void(^)(BOOL isok))finish;

@end

//

//  YCSqliteManager.m

//  TJY

//

//  Created by MacAir on 2017/4/12.

//  Copyright © 2017年 snjj. All rights reserved.

//

#import "YCSqliteManager.h"

#define TJYTimeOutTable @"TJYTimeOutTable"

@implementation YCSqliteManager

+ (NSString *)futureTimeDay:(CGFloat)day{

    

    NSDateFormatter *f = [[NSDateFormatteralloc]init];

    f.dateFormat =@"yyyy-MM-dd hh:mm:ss";

    NSTimeInterval secondsPerDay = 1.0 * day * 24 * 60 * 60;

    NSDate *tomorrowDate = [[NSDatealloc] initWithTimeIntervalSinceNow:secondsPerDay];

    NSString *tomorrowDateStr = [fstringFromDate:tomorrowDate];

    return tomorrowDateStr;

}

#pragma mark - 过期表操作

+ (BOOL)isOutTime:(NSString *)tableName outTime:(NSString *)outTimeStr finish:(void(^)(BOOL isOut))finish{

    

    YCFmdbOperate *ycfmdb = [YCFmdbOperateshareOperate];

    if (![ycfmdboperateIsExistsTable:TJYTimeOutTable]) {

        [ycfmdb operateTable:[NSStringstringWithFormat:@"create table if not exists %@ (id integer primary key autoincrement, tableName text,outTime text)",TJYTimeOutTable]sth:nilfinished:^(BOOL isok) {

            if (isok) {

                //NSLog(@"时间过期表创表成功");

            }

        }];

    }

    [ycfmdb check];

    [ycfmdb operateSelect:[NSStringstringWithFormat:@"select * from %@ where tableName = '%@'",TJYTimeOutTable,tableName]finished:^(NSArray *array) {

        if (array.count > 0) {

            

            BOOL isExists =NO;

            NSDictionary *myDic =nil;

            for (NSDictionary *dicin array) {

                //NSLog(@"dic = %@",dic);

                

                NSString *tName = dic[@"tableName"];

                if ([tNameisEqualToString:tableName]) {

                    isExists = YES;

                    myDic = dic;

                    break;

                }

            }

            

            NSDateFormatter *f = [[NSDateFormatteralloc]init];

            f.dateFormat =@"yyyy-MM-dd hh:mm:ss";

            

            //当前时间

            NSDate *nowDate = [NSDatedate];

            if (isExists) {

                

                NSString *tableInOutTimeStr = myDic[@"outTime"];

                

                NSDate *tableInOutTimeDate = [fdateFromString:tableInOutTimeStr];

                

                //比较是否过期

                NSComparisonResult result = [nowDatecompare:tableInOutTimeDate];

                if (result ==NSOrderedSame || result ==NSOrderedDescending) {

                    //NSLog(@"过期");

                    

                    [ycfmdb operateTable:[NSStringstringWithFormat:@"update %@ set outTime = '%@' where tableName = '%@'",TJYTimeOutTable,outTimeStr,tableName]sth:nilfinished:^(BOOL isok) {

                        if (isok) {

                            

                            //删除表中过期数据

                            [ycfmdb operateTable:[NSStringstringWithFormat:@"delete from %@",tableName]sth:nilfinished:^(BOOL isok) {

                                if (isok) {

                                    

                                    if (finish) {

                                        finish(YES);

                                    }

                                }

                            }];

                            

                        }

                    }];

                    

                }elseif (result == NSOrderedAscending) {

                    //NSLog(@"未过期");

                    if (finish) {

                        finish(NO);

                    }

                    

                }

            }else {

                

                //插入期望时间

                [ycfmdb operateTable:[NSStringstringWithFormat:@"insert into %@ (tableName,outTime) values('%@','%@')",TJYTimeOutTable,tableName,outTimeStr]sth:nilfinished:^(BOOL isok) {

                    if (isok) {

                        if (finish) {

                            finish(NO);

                        }

                    }

                }];

            

            }

            

        }else {

            

            //插入期望时间

            [ycfmdb operateTable:[NSStringstringWithFormat:@"insert into %@ (tableName,outTime) values('%@','%@')",TJYTimeOutTable,tableName,outTimeStr]sth:nilfinished:^(BOOL isok) {

                if (isok) {

                    if (finish) {

                        finish(NO);

                    }

                }

            }];

            

        }

    }];

    

    returnNO;

}

#pragma mark - 分类类型表操作

//保存分类数据

+ (void)saveTemplateClass:(id)sth{

    

    YCFmdbOperate *ycfmdb = [YCFmdbOperateshareOperate];

    if (![ycfmdboperateIsExistsTable:TemplateClassTable]) {

        

        [ycfmdb operateTable:[NSStringstringWithFormat:@"create table if not exists %@ (id integer primary key autoincrement, title text, isSelect int)",TemplateClassTable]sth:nilfinished:^(BOOL isok) {

            if (isok) {

                [ycfmdb check];

            }

        }];

    }

    

    NSString *sql = [NSStringstringWithFormat:@"insert into %@ (title,isSelect) values('%@',%d);",TemplateClassTable,sth,0];

    [ycfmdb operateTable:sqlsth:nilfinished:^(BOOL isok) {

        if (isok) {

            //NSLog(@"插入成功");

        }

    }];

    

}

//获取分类数据

+ (void)getTemplateClss:(void(^)(NSArray *array))finish{

    YCFmdbOperate *ycfmdb = [YCFmdbOperateshareOperate];

    [ycfmdb operateIsNullTable:TemplateClassTablefinished:^(BOOL isNullTable) {

        if (!isNullTable) {

            //NSLog(@"不是空表");

            [ycfmdb operateSelect:[NSStringstringWithFormat:@"select * from %@",TemplateClassTable]finished:^(NSArray *array) {

                if (array.count > 0) {

                    if (finish) {

                        finish(array);

                    }

                }

            }];

            

        }

    }];

    

}

+ (void)updateTemplateClass:(NSString *)chageStr finished:(void(^)(BOOL ok))finish{

    

    [[YCFmdbOperateshareOperate] operateTable:[NSStringstringWithFormat:@"update %@ set isSelect = %d",TemplateClassTable,0]sth:nilfinished:^(BOOL isok) {

        if (isok) {

            

            [[YCFmdbOperateshareOperate] operateTable:[NSStringstringWithFormat:@"update %@ set isSelect = %d where title = '%@'",TemplateClassTable,1,chageStr]sth:nilfinished:^(BOOL isok) {

                if (isok) {

                    if (finish) {

                        finish(YES);

                    }

                }else {

                    if (finish) {

                        finish(NO);

                    }

                }

            }];

        }

    }];

}

#pragma mark - //游记更换模板表大类

+ (void)saveNoteTemplateTable:(id)sth{

    YCFmdbOperate *ycfmdb = [YCFmdbOperateshareOperate];

    if (![ycfmdboperateIsExistsTable:TJYNoteTemplateTable]) {

        [ycfmdb operateTable:[NSStringstringWithFormat:@"create table if not exists %@ (id integer primary key autoincrement, sortId text, sortName text, isSelect int)",TJYNoteTemplateTable]sth:nilfinished:^(BOOL isok) {

            if (isok) {

                NSLog(@"创建表成功");

            }

        }];

    }

    NSDictionary *dic = sth;

    [ycfmdb operateTable:[NSStringstringWithFormat:@"insert into %@ (sortId,sortName,isSelect) values('%@','%@',%d)",TJYNoteTemplateTable,dic[@"sortId"],dic[@"sortName"],0]sth:nilfinished:^(BOOL isok) {

        if (isok) {

            NSLog(@"插入成功");

        }

    }];

    

}

+ (void)getNoteTemplateTable:(void(^)(NSArray *array))finish{

    YCFmdbOperate *ycfmdb = [YCFmdbOperateshareOperate];

    [ycfmdb operateIsNullTable:TJYNoteTemplateTablefinished:^(BOOL isNullTable) {

        if (!isNullTable) {

            //NSLog(@"不是空表");

            [ycfmdb operateSelect:[NSStringstringWithFormat:@"select * from %@",TJYNoteTemplateTable]finished:^(NSArray *array) {

                if (array.count > 0) {

                    if (finish) {

                        finish(array);

                    }

                }

            }];

            

        }

    }];

}

#pragma mark - 游记更换模板详细数据表

+ (void)saveNoteTemplateDetailTable:(id)sth{

    YCFmdbOperate *ycfmdb = [YCFmdbOperateshareOperate];

    

    if (![ycfmdboperateIsExistsTable:TJYTemplateDetailTable]) {

        [ycfmdb operateTable:[NSStringstringWithFormat:@"create table if not exists %@ (id integer primary key autoincrement, addTime text,coverImg text, modelID int, isSoldOut int, name text, sortId int)",TJYTemplateDetailTable]sth:nilfinished:^(BOOL isok) {

            if (isok) {

                NSLog(@"创建表成功");

            }

        }];

    }

    NSDictionary *dic = sth;

    [ycfmdb operateTable:[NSStringstringWithFormat:@"insert into %@ (addTime,coverImg,modelID,isSoldOut,name,sortId) values('%@','%@',%zd,%zd,'%@',%zd)",TJYTemplateDetailTable,dic[@"addTime"],dic[@"coverImg"],[dic[@"id"]integerValue],[dic[@"isSoldOut"]integerValue],dic[@"name"],[dic[@"sortId"]integerValue] ]sth:nilfinished:^(BOOL isok) {

        if (isok) {

            NSLog(@"插入成功");

        }

    }];

}

#pragma mark - 孩子信息表

+ (void)saveChildTable:(id)sth{

    YCFmdbOperate *ycfmdb = [YCFmdbOperateshareOperate];

    if (![ycfmdboperateIsExistsTable:TJYChildTable]) {

        [ycfmdb operateTable:[NSStringstringWithFormat:@"create table if not exists %@ (id integer primary key autoincrement,birthday text, myid int, name text, sex int)",TJYChildTable]sth:nilfinished:^(BOOL isok) {

            if (isok) {

                NSLog(@"创建表成功");

            }

        }];

    }

    NSDictionary *dic = sth;

    [ycfmdb operateTable:[NSStringstringWithFormat:@"insert into %@ (birthday,myid,name,sex) values ('%@',%ld,'%@',%ld)",TJYChildTable,dic[@"birthday"],[dic[@"id"]integerValue],dic[@"name"],[dic[@"sex"]integerValue]]sth:nilfinished:^(BOOL isok) {

        if (isok) {

            NSLog(@"插入成功");

        }

    }];

}

+ (void)getChildTable:(void(^)(NSArray *array))finish{

    YCFmdbOperate *ycfmdb = [YCFmdbOperateshareOperate];

    [ycfmdb operateIsNullTable:TJYChildTablefinished:^(BOOL isNullTable) {

        if (!isNullTable) {

            //NSLog(@"不是空表");

            [ycfmdb operateSelect:[NSStringstringWithFormat:@"select * from %@",TJYChildTable]finished:^(NSArray *array) {

                if (array.count > 0) {

                    if (finish) {

                        finish(array);

                    }

                }

            }];

            

        }

    }];

}

+ (void)updateChildTable:(YCChildModel *)model finish:(void(^)(BOOL isok))finish{

    YCFmdbOperate *ycfmdb = [YCFmdbOperateshareOperate];

    [ycfmdb operateIsNullTable:TJYChildTablefinished:^(BOOL isNullTable) {

        if (!isNullTable) {

            [ycfmdb operateTable:[NSStringstringWithFormat:@"update %@ set birthday = '%@', myid = %ld, name = '%@', sex = %d where myid = %ld",TJYChildTable,model.birthday,model.myid,model.name,model.sex == YES ? 1: 0, model.myid]sth:nilfinished:^(BOOL isok) {

                if (isok) {

                    NSLog(@"更新成功");

                    if (finish) {

                        finish(YES);

                    }

                }

            }];

            

        }

    }];

}

+ (void)deleteChildTable:(NSInteger)childId finish:(void(^)(BOOL isok))finish{

    YCFmdbOperate *ycfmdb = [YCFmdbOperateshareOperate];

    [ycfmdb operateTable:[NSStringstringWithFormat:@"delete from %@ where myid = %ld",TJYChildTable,childId]sth:nilfinished:^(BOOL isok) {

        if (isok) {

            if (finish) {

                finish(YES);

            }

        }else {

            if (finish) {

                finish(NO);

            }

        }

    }];

}

@end