【小记】FMDB使用

#import "FMDatabase.h"
#import "FMDatabaseQueue.h"

@interface
@property (nonatomic, copy) NSString *dbPath;

@property (nonatomic, strong) FMDatabaseQueue *queue;


@implementation
@synthesize dbPath;

static id instance_;

+ (instancetype)sharedInstanceMethod {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance_ = [[self alloc] init];
    });
    
    return instance_;
}

+ (instancetype)allocWithZone:(struct _NSZone *)zone {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance_ = [super allocWithZone:zone];
    });
    
    return instance_;
}

+ (id)copyWithZone:(struct _NSZone *)zone {
    return instance_;
}

- (instancetype)init {
    self = [super init];
    if (self) {
        NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
        NSString *fileName = [doc stringByAppendingPathComponent:@"数据库名.sqlite"];
        
        self.dbPath = fileName;
        self.queue = [FMDatabaseQueue databaseQueueWithPath:self.dbPath];
        [instance_ creatSqlite];
    }
    
    return self;
}



// 创建、打开数据库
- (void)creatSqlite {
    [self.queue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            NSString *sql_allMessage = @"CREATE TABLE IF NOT EXISTS 表名1 (字段1 integer NOT NULL, 字段2 text NOT NULL, 字段3 text NOT NULL, 字段4 text NOT NULL, 字段5 text, 字段6 text, 字段7 integer NOT NULL, 字段8 text NOT NULL, 字段9 text NOT NULL, 字段10 text NOT NULL, 字段11 text NOT NULL);";
            
            NSString *sql_category = @"CREATE TABLE IF NOT EXISTS 表名2 (字段1 integer NOT NULL, 字段2 text NOT NULL, 字段3 text NOT NULL, 字段4 text NOT NULL, 字段5 text, 字段6 text, 字段7 integer NOT NULL, 字段8 text NOT NULL, 字段9 text NOT NULL, 字段10 integer NOT NULL);";
            
            NSString *sql_account = @"CREATE TABLE IF NOT EXISTS 表名3 (字段1 text NOT NULL);";
            
            BOOL res_allMessage = [db executeUpdate:sql_allMessage];
            BOOL res_category = [db executeUpdate:sql_category];
            BOOL res_account = [db executeUpdate:sql_account];
            if (!res_allMessage || !res_category || !res_account) {
                NSLog(@"创表失败");
            } else {
                NSLog(@"创表成功");
            }
        } else {
            NSLog(@"打开数据库失败");
        }
    }];
}

// 向表格中增加数据
- (BOOL)addData {
    __block BOOL result = NO;
    
    [self.queue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            NSString *sql = @"INSERT OR REPLACE INTO 表名(字段1, 字段2, 字段3, 字段4, 字段5, 字段6, 字段7, 字段8, 字段9, 字段10, 字段11)""VALUES(?,?,?,?,?,?,?,?,?,?,?);";
            
            BOOL res = [db executeUpdate:sql,
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"", 
                        @"",
                        @""];
            
            if (!res) {
                NSLog(@"向表格中增加数据失败");
                result = NO;
            } else {
                NSLog(@"向表格中增加数据成功");
                result = YES;
            }
        } else {
            NSLog(@"打开数据库失败");
            result = NO;
        }
    }];
    
    return result;
}

// 更新表格中的数据
- (BOOL)updateData {
    __block BOOL result = NO;
    
    [self.queue inDatabase:^(FMDatabase *db) {
        if ([db open]) {
            NSString *sql = @"UPDATE 表名 SET 字段1 = ?, 字段2 = ?, 字段3 = ?, 字段4 = ?, 字段5 = ?, 字段6 = ? WHERE 字段7 = ? AND 字段8 = ? AND 字段9 = ?";
            
            BOOL res = [db executeUpdate:sql,
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @"",
                        @""];
            
            if (!res) {
                NSLog(@"更新表格中的数据失败");
                result = NO;
            } else {
                NSLog(@"更新表格中的数据成功");
                result = YES;
            }
        } else {
            NSLog(@"打开数据库失败");
            result = NO;
        }
    }];
    
    return result;
}




// 搜索(由于搜索内容可能是需要在界面上展示，为保证效率，我这里一次只搜索20条，可配合下拉或上啦搜索加载更多，只需要传入开始搜索的位置，例如刚开始查询，startIndex传入值为0，以此类推)
- (NSMutableArray *)searchDBWithKeyWord:(NSString *)keyWord andSatrtDate:(UInt64)startDate andEndDate:(UInt64)endDate withStartIndex:(UInt64)startIndex {
    __block NSMutableArray *timeRegionArray = [[NSMutableArray alloc] init];
    
    [self.queue inDatabase:^(FMDatabase *db) {
        NSString *keyWordSql = @"";
        NSString *startSql = @"";
        NSString *endSql = @"";
        
        if ([db open]) {
            if (![HLStringUtil isNullOrEmpty:keyWord]) {
                keyWordSql = [NSString stringWithFormat:@"%@%@%@", @" AND 字段1 LIKE '%", keyWord, @"%'"];
            }
            if (startDate) {
                startSql = [NSString stringWithFormat:@"%@%lld", @" AND 字段2 >= ",startDate];
            }
            if (endDate) {
                endSql = [NSString stringWithFormat:@"%@%lld", @" AND 字段3 <= ", endDate];
            }
            
            NSString *sql = [NSString stringWithFormat:@"%@%@%@%@%@%@%@%@%@%@%lld%@",
                             @"SELECT 字段4 FROM 表名 WHERE 字段5 ='",
                             @"",
                             @"' AND 字段6 ='",
                             @"",
                             @"' AND 字段7 = 'NO'",
                             keyWordSql,
                             startSql,
                             endSql,
                             @" ORDER BY id DESC",
                             @" LIMIT ",
                             startIndex,
                             @", 20"];
            
            FMResultSet *rs = [db executeQuery:sql];
            while ([rs next]) {
                NSString *jsonStr = [rs stringForColumn:@"字段4"];
                NSDictionary *jsDic = [HLDataTransformer jsonDataString2ArrayOrDictionary:jsonStr];
                Message *message = [[Message alloc] init];
                [message dicTranslatetoMessage:jsDic];
                
                [timeRegionArray addObject:message];
            }
        }
    }];
    
    return timeRegionArray;
}