FMDB

#import "DBManager.h"
#import "FMDatabase.h"
#import "User.h"

@implementation DBManager {
    
    FMDatabase *_database;
    
}
- (instancetype)init {
    
    self = [super init];
    if (self) {
        // 只初始化数据库对象，而先不打开数据库 ，等到用的时候再打开。
        _database = [FMDatabase databaseWithPath:[self dbPath]];
        
    }
    return self;
}

+ (instancetype)sharedManager {
    
    static DBManager *singleton = nil;
    static  dispatch_once_t onceToken;
    
    dispatch_once(&onceToken, ^{
        
        singleton = [[DBManager alloc] init];
    });
    return singleton;
}

#pragma mark - 建表

- (BOOL)createTableWithTableName:(NSString *)tableName {
    
    if (![_database open]) {
        return NO;
    }
    NSString *sql = [NSString stringWithFormat:@"create table if not exists %@ (id integer primary key autoincrement,name text,age integer);",tableName];
    
    return [self updateAndCloseWithSql:sql];
    
}

#pragma mark - 插入

//insert into 表名 (字段名,字段名,字段名) values ('','','');
//insert into Book (name,url,des) values ('霍金全集','www.baidu.com','描述');

// 1. 插入数据之前，先看看表是否存在，如果不存在则创建 （1.打开数据库，关闭数据库）
// 2. 如果表存在，那么打开数据库开始操作。

- (BOOL)insertIntoTableWithTableName:(NSString *)tableName obj:(id)obj {
    
    // 如果表不存在，则创建表
    if (![self isExistTableInDatabaseWithTableName:tableName]) {
        if (![self createTableWithTableName:tableName]) {
            return NO;
        }
    }
    
    if (![_database open]) {
        return NO;
    }
    
    NSString *sql = nil;
    
    if ([obj isKindOfClass:[User class]]) {
        User *user = obj;
        
        sql = [NSString stringWithFormat:@"insert into %@(name,age) values ('%@','%@');",tableName,user.name,@(user.age)];
        
        return [self updateAndCloseWithSql:sql];
    }
    return NO;
    
}

- (BOOL)isExistTableInDatabaseWithTableName:(NSString *)tablename
{
    
    if (![_database open]) {
        return NO;
    }
    //sqlite_master是系统表
    NSString *sql = @"select name from sqlite_master where type='table' and name=?";
    //查询
    FMResultSet *results = [_database executeQuery:sql,tablename];
    
    [_database close];
    
    return results.next;
    
}

#pragma mark - 查询

- (NSArray *)quertAllUserObjsFromDB {
    
    return [self quertAllUserObjsFromDBWithParams:nil];
}

- (NSArray *)quertAllUserObjsFromDBWithParams:(NSDictionary *)params {
    
    if (![_database open]) {
        return nil;
    }
    NSMutableString *sql = [NSMutableString stringWithString: @"select * from User"];
    
    sql = [self combineString:sql withParams:params];
    
    NSMutableArray *users = [NSMutableArray array];
    // 得到结果集 executeQuery:
    FMResultSet *results = [_database executeQuery:sql];
    // 根据字段取值
    while (results.next) {
        NSString *name = [results stringForColumn:@"name"];
        //        NSString *name = [results stringForColumnIndex:1];
        
        int age = [results intForColumn:@"age"];
        //        int age = [results intForColumnIndex:2];
        
        // 封装数据模型
        User *user = [[User alloc] init];
        user.name = name;
        user.age = age;
        
        [users addObject:user];
    }
    [_database close];
    return users;
}

#pragma mark - 删除

- (BOOL)deleteUserObjsFromDB {
    
    return [self deleteUserObjsFromDBWithParams:nil];
}

- (BOOL)deleteUserObjsFromDBWithParams:(NSDictionary *)params {
    if (![_database open]) {
        return NO;
    }
    NSMutableString *sql = [NSMutableString stringWithFormat:@"delete * from User"];
    sql = [self combineString:sql withParams:params];
    
    return [self updateAndCloseWithSql:sql];
}

#pragma mark - 修改

- (BOOL)updateUserObjsFromDB {
    
    return [self updateUserObjsFromDBWithParams:nil];
}

- (BOOL)updateUserObjsFromDBWithParams:(NSDictionary *)params {
    
    if (![_database open]) {
        return NO;
    }
    NSMutableString *sql = [NSMutableString stringWithFormat:@"update User set name = 'Charles'"];
    
    sql = [self combineString:sql withParams:params];
    
    return [self updateAndCloseWithSql:sql];
}

/**
 *  拼接sql语句的查询条件参数
 */
- (NSMutableString *)combineString:(NSMutableString *)sql withParams:(NSDictionary *)params {
    
    // 如果有参数 则拼接参数
    if (params) {
        [sql appendFormat:@" where "];
        NSArray *allkeys = params.allKeys;
        for (int i = 0; i < [params count]; i++) {
            NSString *key = allkeys[i];
            // 拼接sql语句
            if (i == [params count] - 1) {
                [sql appendFormat:@"%@='%@';",key,params[key]];
            } else {
                [sql appendFormat:@"%@='%@' and",key,params[key]];
            }
        }
    }
    
    return sql;
    
}

/**
 *  执行sql语句 并 关闭数据库
 */
- (BOOL)updateAndCloseWithSql:(NSString *)sql {
    
    BOOL isFinish = [_database executeUpdate:sql];
    
    //关闭数据
    [_database close];
    
    return isFinish;
    
}

/**
 *  获取数据库路径
 */
- (NSString *)dbPath {
    
    NSString *docPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    return [docPath stringByAppendingPathComponent:@"user.db"];
}