简介:

SQLite (http://www.sqlite.org/docs.html) 是一个轻量级的关系数据库。iOS SDK 很早就支持了 SQLite，在使用时，只需要加入 libsqlite3.dylib 依赖以及引入 sqlite3.h 头文件即可。但是，原生的 SQLite API 在使用上相当不友好，在使用时，非常不便。于是，开源社区中就出现了一系列将 SQLite API 进行封装的库，而 FMDB (https://github.com/ccgus/fmdb) 则是开源社区中的优秀者。

使用: (BESTAccountsManager 为账号数据管理分类, BESTAccountItem 为账号模型)

static FMDatabase *_db;
static BESTAccountsManager *sharedManager = nil;
// 单例
+ (instancetype)sharedManager {
    @synchronized (self) {
        if (sharedManager == nil) {
            sharedManager = [[BESTAccountsManager alloc] init];
        }
    }
    return sharedManager;
}

// 打开数据库

- (void)openAccountManager {
    // 判断 caches 文件夹是否存在.不存在则创建
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject];
    NSFileManager *manager = [NSFileManager defaultManager];
    BOOL tag = [manager fileExistsAtPath:path isDirectory:NULL];
    if (!tag) {
        [manager createDirectoryAtPath:path withIntermediateDirectories:YES attributes:nil error:NULL];
    }
    
    NSString *pathString = [path stringByAppendingPathComponent:@"AccountManager.db"];
    NSLog(@"account_path - %@", pathString);
    _db = [FMDatabase databaseWithPath:pathString];
   
    if ([_db open]) {
        BOOL res = [[BESTAccountsManager sharedManager] createTable];
        
        if (!res) {
            NSLog(@"create table Accounts failed!");
        }
    }
}

// 创建数据库
- (BOOL)createTable {
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS 'Account'('id' PRIMARY KEY AUTOINCREMENT NOT NULL, 'UserName' TEXT NOT NULL, 'Password' TEXT NOT NULL, 'Host' TEXT NOT NULL)"];
    
    return [_db executeUpdate:sql];
}

// 增
- (void)insertAccountItem:(BESTAccountItem *)item {
    if ([_db open]) {
        NSString *sql = [NSString stringWithFormat:@"INSERT INTO 'Account' ('UserName', 'Password', 'Host') VALUES ('%@', '%@', '%@')", item.userName, item.password, item.host];
        BOOL res = [_db executeUpdate:sql];
        if (res) {
            NSLog(@"insert account item succeed!");
        } else {
            NSLog(@"insert account item failed!");
        }
    }
    [_db close];
}

// 删
- (void)deleteAccountItem:(BESTAccountItem *)item {
    if ([_db open]) {
        NSString *sql = [NSString stringWithFormat:@"DELETE FROM 'Account' WHERE 'UserName' = '%@' AND 'Host' = '%@'", item.userName, item.host];
        BOOL res = [_db executeUpdate:sql];
        if (res) {
            NSLog(@"delete account item succeed!");
        } else {
            NSLog(@"delete account item failed!");
        }
    }
    [_db close];
}

// 改
- (void)updataAccountItem:(BESTAccountItem *)item {
    if ([_db open]) {
        NSString *sql = [NSString stringWithFormat:@"UPDATE 'Account' SET 'Password' = '%@' WHERE 'UserName' = '%@' AND 'Host' = '%@'", item.password, item.userName, item.host];
        BOOL res = [_db executeUpdate:sql];
        if (res) {
            NSLog(@"update account item succeed!");
        } else {
            NSLog(@"update account item failed!");
        }
    }
    [_db close];
}

// 查
- (NSArray *)queryAccountItems {
    NSMutableArray *accounts = [NSMutableArray array];
    if ([_db open]) {
        NSString *sql = [NSString stringWithFormat:@"SELECT * FROM 'Account'"];
        FMResultSet *set = [_db executeQuery:sql];
        while ([set next]) {
            BESTAccountItem *item = [[BESTAccountItem alloc] init];
            item.userName = [set stringForColumn:@"UserName"];
            item.password = [set stringForColumn:@"Password"];
            item.host = [set stringForColumn:@"Host"];
            [accounts addObject:item];
        }
    }
    [_db close];
    
    return accounts;
}