FMDB 基本使用

DYDB.h

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

@interface DYDB : NSObject {
  
}

@property(nonatomic, readonly) FMDatabase *database;

+ (DYDB *) sharedDB;

- (FMDatabase *) connect;

- (void) clearDB;

@end

DYDB.m

#import "DYDB.h"
#define kDYDBObvName @"dyobv.sqlite"

@implementation DYDB

static DYDB *_sharedDB;

+ (DYDB *) sharedDB {
  if (!_sharedDB) {
		_sharedDB = [[DYDB alloc] init];
	}
	
	return _sharedDB;
}

- (id) init {
	self = [super init];
	if (self) {
		NSString* docsdir = [NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
		NSString *dbpath = [docsdir stringByAppendingPathComponent:kDYDBObvName];
		DLog(@"database file path: %@", dbpath);
		
		_database = [FMDatabase databaseWithPath:dbpath];
	}
	
	return self;
}

- (FMDatabase *) connect {
		
	if ([_database open]) {
		return _database;
	}
	
	DLog(@"fail to open db...");
	return nil;
}

- (void) clearDB {
	dispatch_async(dispatch_get_main_queue(), ^{
		if([_database close]) {
			_database = nil;
		}
	});
}

@end

DYLogKeeper+DB.h

#import "DYLogKeeper.h"
#import <FMDB/FMDatabase.h>

DYLogKeeper * rs2logkeeper(FMResultSet *rs);

@interface DYLogKeeper (DB)

+ (void) createSqliteTable;

+ (BOOL) insert: (DYLogKeeper *) logkeeper;

+ (BOOL) updateContent: (NSString *) content localId: (NSString *) localId;

+ (BOOL) remove: (DYLogKeeper *) logkeeperId;

+ (DYLogKeeper *) findById: (NSString *) localId;

+ (NSArray *) findOfStartDate: (NSDate *) start toDate:(NSDate *) toDate;

@end

DYLogKeeper+DB.m

#import "DYLogKeeper+DB.h"

#import "DYDB.h"
#import <FMDB/FMDatabase.h>
#import "DYUUID.h"
#import "NSDate+NSDateFormaterCategory.h"

DYLogKeeper * rs2logkeeper(FMResultSet *rs) {
  DYLogKeeper *obj = [[DYLogKeeper alloc] init];
	
	obj.localId = [rs stringForColumn:@"local_id"];
	obj.logkeeperId = [rs stringForColumn:@"logkeeper_id"];
	obj.addtime = [rs dateForColumn:@"add_time"];
	obj.content = [rs stringForColumn:@"content"];

	obj.deviceId = [rs stringForColumn:@"device_id"];
	obj.deviceType = [rs intForColumn:@"device_type"];
	obj.channel = [rs intForColumn:@"channel"];
	
	return obj;
}

@implementation DYLogKeeper (DB)

+ (void) createSqliteTable {
	
	DLog(@"check table is exists?");
	FMDatabase *db = [[DYDB sharedDB] connect];
	NSString *existsSql = [NSString stringWithFormat:@"select count(name) as countNum from sqlite_master where type = 'table' and name = '%@'", @"log_keepers" ];
	DLog(@"%@", existsSql);
	
	FMResultSet *rs = [db executeQuery:existsSql];
	
	if ([rs next]) {
		NSInteger count = [rs intForColumn:@"countNum"];
		DLog(@"The table count: %d", count);
		if (count == 1) {
			DLog(@"log_keepers table is existed.");
			return;
		}
		
		DLog(@"log_keepers is not existed.");
	}
	[rs close];
	
	
	DLog(@"create table ....");
	NSString *filePath = [[NSBundle mainBundle] pathForResource:@"log_keepers_table" ofType:@"sql"];
	DLog(@"logkeeper sql file: %@", filePath);
		
	NSError *error;
	NSString *sql = [NSString stringWithContentsOfFile:filePath encoding:NSUTF8StringEncoding error:&error];
	
	if (error != nil) {
		DLog(@"fail to read sql file: %@", [error description]);
		return;
	}
	DLog(@"--sql: \n %@", sql);
	DLog(@"execute sql ....");
	if([db executeUpdate:sql]) {
		DLog(@"create table succes....");
	} else {
		DLog(@"fail to execute update sql..");
	}
	
	
	[db close];
}


+ (BOOL) insert: (DYLogKeeper *) logkeeper {
	DLog(@"insert logkeeper");
	DLog(@"convert int value to NSNumber ...");
	logkeeper.localId = [DYUUID uuidString];
	
	NSNumber *channelNum = [NSNumber numberWithInt:logkeeper.channel];
	DLog(@"-- channelNum: %@", channelNum);
	NSNumber *typeNumber = [NSNumber numberWithInt:logkeeper.deviceType];
	DLog(@"-- deviceType: %@", typeNumber);
	
	NSString *sql = @"insert into log_keepers(local_id, logkeeper_id, add_time, content, device_id, device_type, channel) values(?, ?, ?, ?, ?, ?, ?)";
	
	FMDatabase *db = [[DYDB sharedDB] connect];
	if (db == nil) {
		DLog(@"fail to create db..");
	}
	BOOL ret = [db executeUpdate:sql, logkeeper.localId, logkeeper.logkeeperId,
				logkeeper.addtime, logkeeper.content,
				logkeeper.deviceId, typeNumber, channelNum];
	
	[db close];

	return ret;
}

+ (BOOL) updateContent:(NSString *)content
			   localId: (NSString *)localId {
	
	DLog(@"update logkeeper content");
	NSString *sql = @"update log_keepers set content = ? where local_id = ?";
	
	FMDatabase *db = [[DYDB sharedDB] connect];
	BOOL ret = [db executeUpdate:sql, content, localId];
	
	[db close];
	return ret;
}

- (BOOL) remove: (DYLogKeeper *) logkeeper {
	DLog(@"remove logkeeper: %@", logkeeper.localId);
	NSString *sql = @"delete from log_keepers where local_id = ?";
		
	FMDatabase *db = [[DYDB sharedDB] connect];
	BOOL ret = [db executeUpdate:sql, logkeeper.localId];
	
	[db close];
	
	return ret;
}

- (DYLogKeeper *) findById:(NSString *)localId {
	DLog(@"find logkeeper by id: %@", localId);
	
	FMDatabase *db = [[DYDB sharedDB] connect];
	FMResultSet *rs = [db executeQuery:@"select * from log_keepers where local_id = ?", localId];

	DYLogKeeper *ret;
	
	if ([rs next]) {
		ret = rs2logkeeper(rs);
	}

	[db close];
	return ret;
}

+ (NSArray *) findOfStartDate: (NSDate *) start toDate:(NSDate *) toDate {
	DLog(@"find logkeeper between date ....");
	NSString *sql = @"select * from log_keepers where add_time between ? and ?";
	
	FMDatabase *db = [[DYDB sharedDB] connect];
	FMResultSet *rs = [db executeQuery:sql, start, toDate];
	
	NSMutableArray *array = [NSMutableArray arrayWithCapacity:32];
	while ([rs next]) {
		DYLogKeeper *logkeeper = rs2logkeeper(rs);
		[array addObject:logkeeper];
	}

	[rs close];
	[db close];
	
	return array;
}

@end

DYLogKeeper.h

#import <Foundation/Foundation.h>

@interface DYLogKeeper : NSObject

@property(strong, nonatomic) NSString *localId;

@property(strong, nonatomic) NSDate *addtime;

@property(strong, nonatomic) NSString *deviceId;

@property(strong, nonatomic) NSString *content;

@property(strong, nonatomic) NSString *logkeeperId;

@property(nonatomic) int deviceType;

@property(nonatomic) int channel;


@end