数据存储之第三方FMDB优化

    最近项目要用到数据库,采用的是第三方FMDB, 之前做C#时用过sqlHelper,自己就按着sqlHelper的思路封装了一下,封装的也比较简单，看到网上有一些根据FMDB封装的ORM框架，但基本都是单表的.

按着ADO.Net的思路

1.连接数据库

2.打开数据库

3.操作数据库

4.关闭数据库

在使用查询数据时FMResultSet 用next遍历时不能关闭数据库

#import <Foundation/Foundation.h>
#import "FMDB.h"

@interface FMDBManager : NSObject

-(BOOL)updateDataBase:(NSString *)sql withArgumentsInArray:(NSArray *)arguments;

-(void)openDatabase;

-(void)closeDatabase;

-(FMResultSet *)QueryDataBase:(NSString *)sql withArgumentsInArray:(NSArray *)arguments;

-(BOOL)updateDataBaseInTransaction:(NSArray *)sqlArray;

@end

//
//  FMDBManager.m
//  XQBCommunityApp
//
//  Created by City--Online on 16/1/14.
//  Copyright  2016年 CityOnline_1. All rights reserved.
//

#import "FMDBManager.h"
#import "FMDB.h"
#import <sqlite3.h>
/**
 *  数据库名字
 */
#define XQB_DB_NAME       @"XqbDB.sqlite"
#define XQB_DB_PATH       @"XqbDbDir"

@interface FMDBManager ()
@property (nonatomic,strong) NSString *tablePath;
@property (nonatomic,strong) FMDatabase *database;
@end
@implementation FMDBManager

- (void)connectDataBase
{
    NSFileManager *fileManager = [[NSFileManager alloc] init];
    NSString *pathDocuments = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString *databasePath = [NSString stringWithFormat:@"%@/%@", pathDocuments,XQB_DB_PATH];

    // 判断文件夹是否存在，如果不存在，则创建
    if (![[NSFileManager defaultManager] fileExistsAtPath:databasePath]) {
        [fileManager createDirectoryAtPath:databasePath withIntermediateDirectories:YES attributes:nil error:nil];
    } else {
        NSLog(@"FileDir is exists.");
    }
    //获取数据库 的路径
    _tablePath = [NSString stringWithFormat:@"%@/%@",databasePath,XQB_DB_NAME];
    _database = [FMDatabase databaseWithPath:_tablePath];
    NSLog(@"%@",_tablePath);
}

-(BOOL)updateDataBase:(NSString *)sql withArgumentsInArray:(NSArray *)arguments
{
    return [_database executeUpdate:sql withArgumentsInArray:arguments];
}

-(FMResultSet *)QueryDataBase:(NSString *)sql withArgumentsInArray:(NSArray *)arguments
{
    FMResultSet *set=nil;
    set=[_database executeQuery:sql withArgumentsInArray:arguments];
    return set;
}

-(void)openDatabase
{
    //创建数据库
    [self connectDataBase];
    [_database open];
}
-(void)closeDatabase
{
    [_database close];
}

-(BOOL)updateDataBaseInTransaction:(NSArray *)sqlArray
{
    BOOL success=YES;
    [self connectDataBase];
     sqlite3 *db=nil;
    @try {
        char *errmsg=NULL;
        int result=sqlite3_open(_tablePath.UTF8String, &db);
        if (result==SQLITE_OK) {
            if (sqlite3_exec(db, "begin", NULL, NULL, &errmsg)==SQLITE_OK) {
                sqlite3_free(errmsg);
                sqlite3_stmt *stmt=NULL;
                //执行事务
                for (NSString *str in sqlArray) {
                    if (sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, NULL)==SQLITE_OK) {
                        if (sqlite3_step(stmt)!=SQLITE_DONE) {
                            sqlite3_finalize(stmt);
                        }
                    }
                }
                //提交事务
                if (sqlite3_exec(db, "commit", NULL, NULL, &errmsg)==SQLITE_OK) {
                    sqlite3_free(errmsg);
                }
            }
        }
        sqlite3_close(db);
    }
    @catch (NSException *exception) {
        char *errmsg=NULL;
        if (sqlite3_exec(db, "rollback", NULL, NULL, &errmsg)==SQLITE_OK) {
            success=NO;
        }
    }
    @finally {

    }
    return success;
}

@end