FMDB的简单封装

@interface Manager : NSObject

@property (nonatomic, strong) FMDatabaseQueue *dataQueue;

+(Manager*)sharedManager;

-(void)initQueue;

-(BOOL)insertToDB:(NSString *)table dic:(NSDictionary *)where;

-(void)insertToDB:(NSString *)table dic:(NSDictionary *)where callback:(void(^)(BOOL b))block;

-(BOOL)deleteToDB:(NSString *)table dic:(NSDictionary *)where;

-(void)deleteToDB:(NSString *)table dic:(NSDictionary *)where callback:(void(^)(BOOL b))block;

-(BOOL)updateToDB:(NSString *)table dic:(NSDictionary *)dic where:(NSDictionary *)where;

-(void)updateToDB:(NSString *)table dic:(NSDictionary *)dic where:(NSDictionary *)where

         callback:(void(^)(BOOL b))block;

-(NSDictionary *)searchToDB:(NSString *)sql;

-(NSArray *)searchToDBs:(NSString *)sql;

-(NSDictionary *)searchToDB:(NSString *)table dic:(NSDictionary *)where;

-(void)searchToDB:(NSString *)table dic:(NSDictionary *)where callback:(void(^)(NSDictionary *dic))result;

@end

@implementation Manager

static Manager *shareDataBase = nil;

static FMDatabaseQueue *queue = nil;

+(Manager *)sharedManager{

    static dispatch_once_t onceToken;

    dispatch_once(&onceToken, ^{

        shareDataBase = [[self alloc] init];

        [[self alloc] initQueue];

    });

    return shareDataBase;

}

-(void)initQueue{

    queue = [[FMDatabaseQueue alloc] initWithPath:数据库的路径字符串];

}

-(FMDatabaseQueue *)dataQueue{

    if (_dataQueue != queue) {

        _dataQueue = queue;

    }

    return _dataQueue;

}

-(BOOL)insertToDB:(NSString *)table dic:(NSDictionary *)where{

    __block BOOL result = YES;

    [self insertToDB:table dic:where callback:^(BOOL b) {

        result = b;

    }];

    return result;

}

-(void)insertToDB:(NSString *)table dic:(NSDictionary *)where callback:(void(^)(BOOL b))block{

    [queue inDatabase:^(FMDatabase *db) {

        NSMutableString* insertKeysString = [NSMutableString string];

        NSMutableString* insertValuesString = [NSMutableString string];

        NSMutableArray* values = [NSMutableArray arrayWithCapacity:0];

        

        NSArray* keys = where.allKeys;

        for (int i=0; i< keys.count;i++){

        

            NSString* key = [keys objectAtIndex:i];

            [insertKeysString appendFormat:@"%@,",key];

            

            [values addObject:[where objectForKey:key]];

            [insertValuesString appendString:@"?,"];

        }

        [insertKeysString deleteCharactersInRange:NSMakeRange(insertKeysString.length - 1,1)];

        [insertValuesString deleteCharactersInRange:NSMakeRange(insertValuesString.length - 1,1)];

        NSString* insertSQL = [NSString stringWithFormat:@"INSERT INTO %@ (%@) values(%@)",

                               table,insertKeysString,insertValuesString];

        BOOL result = [db executeUpdate:insertSQL withArgumentsInArray:values];

        

        if(block != nil)

            block(result);

    }];

}

-(BOOL)deleteToDB:(NSString *)table dic:(NSDictionary *)where{

    __block BOOL result = YES;

    [self deleteToDB:table dic:where callback:^(BOOL b) {

        result = b;

    }];

    return result;

}

-(void)deleteToDB:(NSString *)table dic:(NSDictionary *)where callback:(void(^)(BOOL b))block{

    [queue inDatabase:^(FMDatabase *db) {

        NSMutableArray* values = [NSMutableArray array];

        NSString* wherekey = [self dictionaryToSqlWhere:where andValues:values isWhere:YES];

        NSString* delete = [NSString stringWithFormat:@"DELETE FROM %@ where %@ ",table,wherekey];

        BOOL result = [db executeUpdate:delete withArgumentsInArray:values];

        if(block != nil)

            block(result);

    }];

}

-(BOOL)updateToDB:(NSString *)table dic:(NSDictionary *)dic where:(NSDictionary *)where{

    __block BOOL result = YES;

    [self updateToDB:table dic:dic where:where callback:^(BOOL b) {

        result = b;

    }];

    return result;

}

-(void)updateToDB:(NSString *)table dic:(NSDictionary *)dic where:(NSDictionary *)where

         callback:(void(^)(BOOL b))block{

    [queue inDatabase:^(FMDatabase *db) {

        NSMutableString* query = [NSMutableString stringWithFormat:@"UPDATE %@ SET ",table];

        NSMutableArray* values = [NSMutableArray arrayWithCapacity:0];

        if(dic !=nil&& dic.count>0){

            NSString* wherekey = [self dictionaryToSqlWhere:dic andValues:values isWhere:NO];

            [query appendFormat:@" %@",wherekey];

            wherekey = [self dictionaryToSqlWhere:where andValues:values isWhere:NO];

            [query appendFormat:@" WHERE %@ ",wherekey];

        }

        

        BOOL result = [db executeUpdate:query withArgumentsInArray:values];

        

        if(block != nil)

            block(result);

    }];

    

}

-(NSArray *)searchToDBs:(NSString *)sql{

   __block NSMutableArray *array = [NSMutableArray array];

    [queue inDatabase:^(FMDatabase *db) {

        FMResultSet* rs =[db executeQuery:sql];

        int num_cols = [rs columnCount];

        while ([rs next]){

            NSMutableDictionary *dict = [NSMutableDictionary dictionaryWithCapacity:num_cols];

            for (int i = 0; i < num_cols; i++) {

                NSString * columnName = [rs columnNameForIndex:i];

                id columnValue = [rs objectForColumnIndex:i];

                [dict setObject:columnValue forKey:columnName];

            }

            [array addObject:dict];

            

        }

    }];

    return array;

}

-(NSDictionary *)searchToDB:(NSString *)sql{

    __block NSDictionary *result = nil;

    

    [queue inDatabase:^(FMDatabase *db) {

        FMResultSet* rs =[db executeQuery:sql];

        int num_cols = [rs columnCount];

        NSMutableDictionary *dict = [NSMutableDictionary dictionaryWithCapacity:num_cols];

        while ([rs next]){

            for (int i = 0; i < num_cols; i++) {

                NSString * columnName = [rs columnNameForIndex:i];

                id columnValue = [rs objectForColumnIndex:i];

                [dict setObject:columnValue forKey:columnName];

            }

        }

        result = dict;

    }];

    return result;

}

-(NSDictionary *)searchToDB:(NSString *)table dic:(NSDictionary *)where{

    __block NSDictionary *result = nil;

    

    [self searchToDB:table dic:where callback:^(NSDictionary *dic) {

        if (dic.allKeys.count != 0) {

            result = dic;

        }

    }];

    return result;

}

-(void)searchToDB:(NSString *)table dic:(NSDictionary *)where callback:(void(^)(NSDictionary *dic))result{

    [queue inDatabase:^(FMDatabase *db) {

        NSMutableString* query = [NSMutableString stringWithFormat:@"SELECT * from %@ ",table];

        NSMutableArray* values = [NSMutableArray arrayWithCapacity:0];

        if(where !=nil&& where.count>0){

            NSString* wherekey = [self dictionaryToSqlWhere:where andValues:values isWhere:YES];

            [query appendFormat:@" where %@",wherekey];

        }

        [self sqlString:query AddOder:nil offset:0 count:1];

        FMResultSet* rs =[db executeQuery:query withArgumentsInArray:values];

        int num_cols = [rs columnCount];

        NSMutableDictionary *dict = [NSMutableDictionary dictionaryWithCapacity:num_cols];

        while ([rs next]){

            for (int i = 0; i < num_cols; i++) {

                NSString * columnName = [rs columnNameForIndex:i];

                id columnValue = [rs objectForColumnIndex:i];

                [dict setObject:columnValue forKey:columnName];

            }

        }

        result(dict);

    }];

}

-(NSString*)dictionaryToSqlWhere:(NSDictionary*)dic andValues:(NSMutableArray*)values isWhere:(BOOL)b{

    NSMutableString* wherekey = [NSMutableString stringWithCapacity:0];

    if(dic != nil && dic.count >0 ){

        NSArray* keys = dic.allKeys;

        for (int i=0; i< keys.count;i++) {

            NSString* key = [keys objectAtIndex:i];

            id va = [dic objectForKey:key];

            

            if(wherekey.length > 0){

                [wherekey appendFormat:b ? @" and %@ = ? " : @" , %@ = ? ",key];

            }

            else{

                [wherekey appendFormat:@" %@ = ? ",key];

            }

            if (values != nil) {

                [values addObject:va];

            }

        }

    }

    return wherekey;

}

-(void)sqlString:(NSMutableString*)sql AddOder:(NSString*)orderby offset:(int)offset count:(int)count{

    if(orderby != nil && ![orderby isEmptyWithTrim]){

        [sql appendFormat:@" order by %@ ",orderby];

    }

    if (count > 0) {

        [sql appendFormat:@" limit %d offset %d ",count,offset];

    }

}

- (BOOL)isEmptyWithTrim{

    return [[self stringWithTrim] isEqualToString:@""];

}

- (NSString *)stringWithTrim{

    return [self stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];

}

@end