Created
July 30, 2013 14:46
-
-
Save lvjian700/6113564 to your computer and use it in GitHub Desktop.
FMDB 基本使用
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#import <Foundation/Foundation.h> | |
#import <FMDB/FMDatabase.h> | |
@interface DYDB : NSObject { | |
} | |
@property(nonatomic, readonly) FMDatabase *database; | |
+ (DYDB *) sharedDB; | |
- (FMDatabase *) connect; | |
- (void) clearDB; | |
@end |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment