fmdb数据库操作

注意点参考文件

#import <Foundation/Foundation.h>

#import "FMDatabase.h"

#import "FMDatabaseQueue.h"

@interface JFWDataBaseManager : NSObject

+(id)sharedInstance;

- (BOOL)initDataBaseDataName:(NSString *)dbName;

- (BOOL)isExistTable:(NSString *)dbName;

- (BOOL)createDataTableSql:(NSString *)sql tableName:(NSString *)tableName;

//删除数据库

- (BOOL)deleteDataBaseDataName:(NSString *)dbName;

//删除表

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

//增加、删除、修改

- (BOOL)upDataTableSql:(NSString *)sql tableName:(NSString *)tableName;

//查询表信息

- (NSArray *)queryDataTableSql:(NSString *)sql tableName:(NSString *)tableName  indexKeys:(NSArray *)allKeys;

//创建表sql

/*sqlDic

 key:字段

 value:数据类型

 *tableName:表名

 */

- (NSString *)combineCreateSqlTableName:(NSString *)tableName sqlData:(NSDictionary *)sqlDic;

//插入数据sql

/*sqlDic

 key:字段

 value:数据值

 *tableName:表名

 */

- (NSString *)combineInsertSqlTableName:(NSString *)tableName sqlData:(NSDictionary *)sqlDic;

//更新数据sql

/*sqlDic

 key:字段

 value:数据值

 *tableName:表名

 *condStr:修改条件

 */

- (NSString *)combineUpdataSqlTableName:(NSString *)tableName sqlData:(NSDictionary *)sqlDic modifyConditions:(NSString *)condStr;

//查询数据sql

/*tableName:表名

 *condStr:查询条件

 */

- (NSString *)combineQuerySqlTableName:(NSString *)tableName queryConditions:(NSString *)condStr;

//

//  JFWDataBaseManager.m

//  JuFengWang

//

//  Created by lxf on 16/7/12.

//

//

#import "JFWDataBaseManager.h"

#import "Global.h"

@implementation JFWDataBaseManager{

    

    FMDatabase *_db;

}

+(id)sharedInstance {

    

    static JFWDataBaseManager *sharedInstace = nil;

    static dispatch_once_t onceToken;

    dispatch_once(&onceToken,^{

        

        sharedInstace = [[self alloc]init];

    });

    return sharedInstace;

}

- (BOOL)initDataBaseDataName:(NSString *)dbName {

    

    BOOL ret = NO;

    if (!dbName || 0 == dbName.length) {

        

        return ret;

    }

    

    NSString *dbPath = [Global documentDirectory];

    dbPath = [dbPath stringByAppendingPathComponent:dbName];

    _db = [FMDatabase databaseWithPath:dbPath];

    if (!_db) {

        

        ret = NO;

    }

    else {

        

        ret = YES;

        

    }

    return ret;

}

- (BOOL)isExistTable:(NSString *)dbName {

    

    BOOL ret = NO;

    NSString *sql = [NSString stringWithFormat:

           @"select * from sqlite_master where type='table' and name = '%@'",

           dbName];

    FMResultSet *rs = [_db executeQuery:sql];

    if ([rs next]) {

        

        NSLog(@"columnNameToIndexMap = %@",rs.columnNameToIndexMap);

        ret = YES;

    }

    return ret;

}

- (BOOL)createDataTableSql:(NSString *)sql tableName:(NSString *)tableName {

    

    BOOL ret = NO;

    if ([_db open]) {

        

        if ([self isExistTable:tableName]) {

            

            ret = YES;

        }

        else {

            

            ret = [_db executeUpdate:sql];

        }

    }

    else {

        

        ret = NO;

        NSLog(@"error when open db");

    }

    [_db close];

    return ret;

}

- (BOOL)deleteDataBaseDataName:(NSString *)dbName {

    

    BOOL ret = NO;

    NSString *dbPath = [Global documentDirectory];

    dbPath = [dbPath stringByAppendingPathComponent:dbName];

    NSFileManager *fileMgr = [NSFileManager defaultManager];

    if ([fileMgr fileExistsAtPath:dbPath]) {

        

        ret = [fileMgr removeItemAtPath:dbPath error:nil];

    }

    return ret;

}

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

    

    BOOL ret = NO;

    NSString *sql = [NSString stringWithFormat:@"delete from %@",tableName];

    if ([self isExistTable:tableName]) {

        

        ret = [_db executeUpdate:sql];

    }

    else {

        

        ret = NO;

    }

    return ret;

}

- (BOOL)upDataTableSql:(NSString *)sql tableName:(NSString *)tableName {

    

    BOOL ret = NO;

    if ([_db open]) {

        

        if ([self isExistTable:tableName]) {

        

            ret = [_db executeUpdate:sql];

        }

        else {

            

            ret = NO;

        }

    }

    else {

        

        ret = NO;

        NSLog(@"error when open db");

    }

    [_db close];

    return ret;

}

- (NSArray *)queryDataTableSql:(NSString *)sql tableName:(NSString *)tableName  indexKeys:(NSArray *)allKeys {

    

    NSMutableArray *dataArr = [[NSMutableArray alloc]init];

    if (tableName.length > 0 && allKeys.count > 0) {

        

        if ([_db open]) {

            

            if ([self isExistTable:tableName]) {

            

                if (!sql || sql.length == 0) {

                    

                    sql = [NSString stringWithFormat:@"select * from %@",tableName];//todo 为空查全部、后续分页

                }

                FMResultSet * rs = [_db executeQuery:sql];

                while (rs.next) {

                    

                    NSMutableDictionary *resultDic = [[NSMutableDictionary alloc]init];

                    for (NSString *key in allKeys) {

                        

                        NSString *value = [rs stringForColumn:key];

                        [resultDic setObject:[Global dealWithNilString:[value copy]] forKey:[Global dealWithNilString:[key copy]]];

                    }

                    if (resultDic.count > 0) {

                        

                        [dataArr addObject:resultDic];

                    }

                }

            }

        }

        else {

            

            NSLog(@"error when open db");

        }

        [_db close];

    }

    else {

        

        NSLog(@"pass param error!");

    }

    

    return dataArr;

}

- (NSString *)combineCreateSqlTableName:(NSString *)tableName sqlData:(NSDictionary *)sqlDic {

    

    NSString *sql = [NSString new];

    if (tableName.length > 0 && sqlDic.count > 0) {

        

        NSArray *allKeys = [sqlDic allKeys];

        NSString *keyValueStr = [NSString new];

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

            

            NSString *key = [self dealWithNilString:allKeys[i]];

            NSString *value = [self dealWithNilString:sqlDic[key]];

            keyValueStr = [keyValueStr stringByAppendingFormat:@", '%@' '%@'",key,value];

        }

        sql = [NSString stringWithFormat:@"CREATE TABLE '%@' ('id' INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL %@)",tableName,keyValueStr];

        NSLog(@"createSql keyValueStr = %@,sql = %@",keyValueStr,sql);

    }

    return sql;

}

- (NSString *)combineInsertSqlTableName:(NSString *)tableName sqlData:(NSDictionary *)sqlDic {

    

    NSString *sql = [NSString new];

    if (tableName.length > 0 && sqlDic.count > 0) {

        

        NSArray *allKeys = [sqlDic allKeys];

        NSString *keyStr = [NSString new];

        NSString *valueStr = [NSString new];

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

            

            NSString *key = [self dealWithNilString:allKeys[i]];

            NSString *value = [self dealWithNilString:sqlDic[key]];

            if (0 == i) {

                

                keyStr = [keyStr stringByAppendingFormat:@"'%@'",key];

                valueStr = [valueStr stringByAppendingFormat:@"'%@'",value];

            }

            else {

                

                keyStr = [keyStr stringByAppendingFormat:@",'%@'",key];

                valueStr = [valueStr stringByAppendingFormat:@",'%@'",value];

            }

        }

        sql = [NSString stringWithFormat:@"INSERT INTO '%@' (%@) VALUES(%@)",tableName,keyStr,valueStr];

        NSLog(@"createSql keyStr = %@,valueStr = %@,sql = %@",keyStr,valueStr,sql);

    }

    return sql;

}

- (NSString *)combineUpdataSqlTableName:(NSString *)tableName sqlData:(NSDictionary *)sqlDic modifyConditions:(NSString *)condStr{

    

    NSString *sql = [NSString new];

    if (tableName.length > 0 && sqlDic.count > 0 && condStr.length > 0) {

        

        NSArray *allKeys = [sqlDic allKeys];

        NSString *keyValueStr = [NSString new];

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

            

            NSString *key = [self dealWithNilString:allKeys[i]];

            NSString *value = [self dealWithNilString:sqlDic[key]];

            if (0 == i) {

                

                keyValueStr = [keyValueStr stringByAppendingFormat:@"'%@' = '%@'",key,value];

            }

            else {

                

                keyValueStr = [keyValueStr stringByAppendingFormat:@", '%@' = '%@'",key,value];

            }

            

        }

        sql = [NSString stringWithFormat:@"UPDATE '%@' SET %@ where %@",tableName,keyValueStr,[self dealWithNilString:condStr]];

        NSLog(@"createSql keyValueStr = %@,sql = %@",keyValueStr,sql);

    }

    return sql;

}

- (NSString *)combineQuerySqlTableName:(NSString *)tableName queryConditions:(NSString *)condStr {

    

    NSString *sql = [NSString new];

    if (tableName.length > 0) {

        

        if (!condStr || condStr.length == 0) {

            

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

        }

        else {

            

            sql = [NSString stringWithFormat:@"select * from '%@' where %@",tableName,condStr];

        }

    }

    return sql;

}

- (NSString *)dealWithNilString:(NSString *)str{

        

    if (!str) {

        

        return @"";

    }

    else{

        

        return str;

    }

}

@end