FMDatabase的一些基本操作小结
#import <Foundation/Foundation.h> |
#import "FMDatabase.h" |
#import "FMDatabaseAdditions.h" |
@interface wiDBRoot : NSObject |
@property (retain, nonatomic) FMDatabase *DB; |
@property (retain, nonatomic) NSString *DBName; |
//+ (id)modelWithDBName:(NSString *)dbName; |
- (id)initWithDBName:(NSString *)dbName; |
// 删除数据库 |
- (void)deleteDatabse; |
// 数据库存储路径 |
//- (NSString *)getPath:(NSString *)dbName; |
// 打开数据库 |
- (void)readyDatabse; |
// 判断是否存在表 |
- (BOOL) isTableOK:(NSString *)tableName; |
// 获得表的数据条数 |
- (BOOL) getTableItemCount:(NSString *)tableName; |
// 创建表 |
- (BOOL) createTable:(NSString *)tableName withArguments:(NSString *)arguments; |
// 删除表-彻底删除表 |
- (BOOL) deleteTable:(NSString *)tableName; |
// 清除表-清数据 |
- (BOOL) eraseTable:(NSString *)tableName; |
// 插入数据 |
- (BOOL)insertTable:(NSString*)sql, ...; |
// 修改数据 |
- (BOOL)updateTable:(NSString*)sql, ...; |
// 整型 |
- (NSInteger)getDb_Integerdata:(NSString *)tableName withFieldName:(NSString *)fieldName; |
// 布尔型 |
- (BOOL)getDb_Booldata:(NSString *)tableName withFieldName:(NSString *)fieldName; |
// 字符串型 |
- (NSString *)getDb_Stringdata:(NSString *)tableName withFieldName:(NSString *)fieldName; |
// 二进制数据型 |
- (NSData *)getDb_Bolbdata:(NSString *)tableName withFieldName:(NSString *)fieldName; |
@end |
#import "wiDBRoot.h" |
@interface wiDBRoot () |
- (NSString *)getPath:(NSString *)dbName; |
@end |
@implementation wiDBRoot |
@synthesize DB; |
@synthesize DBName; |
/* |
+ (id)modelWithDBName:(NSString *)dbName |
{ |
[[[self alloc] initWithDBName:dbName] autorelease]; |
return self; |
} |
*/ |
- (id)initWithDBName:(NSString *)dbName |
{ |
self = [super init]; |
if(nil != self) |
{ |
DBName = [self getPath:dbName]; |
WILog(@"DBName: %@", DBName); |
} |
return self; |
} |
- (void)dealloc { |
[DB close]; |
[DB release]; |
[DBName release]; |
[super dealloc]; |
} |
// 数据库存储路径(内部使用) |
- (NSString *)getPath:(NSString *)dbName |
{ |
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); |
NSString *documentsDirectory = [paths objectAtIndex:0]; |
return [documentsDirectory stringByAppendingPathComponent:dbName]; |
} |
// 打开数据库 |
- (void)readyDatabse |
{ |
//BOOL success; |
//NSError *error; |
//NSFileManager *fileManager = [NSFileManager defaultManager]; |
//success = [fileManager fileExistsAtPath:self.DBName]; |
if ([DB databaseExists]) |
return; |
//DB = [FMDatabase databaseWithPath:DBName]; |
DB = [[FMDatabase alloc] initWithPath:DBName]; |
if (![DB open]) |
{ |
[DB close]; |
NSAssert1(0, @"Failed to open database file with message '%@'.", [DB lastErrorMessage]); |
} |
// kind of experimentalish. |
[DB setShouldCacheStatements:YES]; |
} |
#pragma mark 删除数据库 |
// 删除数据库 |
- (void)deleteDatabse |
{ |
BOOL success; |
NSError *error; |
NSFileManager *fileManager = [NSFileManager defaultManager]; |
// delete the old db. |
if ([fileManager fileExistsAtPath:DBName]) |
{ |
[DB close]; |
success = [fileManager removeItemAtPath:DBName error:&error]; |
if (!success) { |
NSAssert1(0, @"Failed to delete old database file with message '%@'.", [error localizedDescription]); |
} |
} |
} |
// 判断是否存在表 |
- (BOOL) isTableOK:(NSString *)tableName |
{ |
FMResultSet *rs = [DB executeQuery:@"SELECT count(*) as 'count' FROM sqlite_master WHERE type ='table' and name = ?", tableName]; |
while ([rs next]) |
{ |
// just print out what we've got in a number of formats. |
NSInteger count = [rs intForColumn:@"count"]; |
WILog(@"isTableOK %d", count); |
if (0 == count) |
{ |
return NO; |
} |
else |
{ |
return YES; |
} |
} |
return NO; |
} |
// 获得表的数据条数 |
- (BOOL) getTableItemCount:(NSString *)tableName |
{ |
NSString *sqlstr = [NSString stringWithFormat:@"SELECT count(*) as 'count' FROM %@", tableName]; |
FMResultSet *rs = [DB executeQuery:sqlstr]; |
while ([rs next]) |
{ |
// just print out what we've got in a number of formats. |
NSInteger count = [rs intForColumn:@"count"]; |
WILog(@"TableItemCount %d", count); |
return count; |
} |
return 0; |
} |
// 创建表 |
- (BOOL) createTable:(NSString *)tableName withArguments:(NSString *)arguments |
{ |
NSString *sqlstr = [NSString stringWithFormat:@"CREATE TABLE %@ (%@)", tableName, arguments]; |
if (![DB executeUpdate:sqlstr]) |
//if ([DB executeUpdate:@"create table user (name text, pass text)"] == nil) |
{ |
WILog(@"Create db error!"); |
return NO; |
} |
return YES; |
} |
// 删除表 |
- (BOOL) deleteTable:(NSString *)tableName |
{ |
NSString *sqlstr = [NSString stringWithFormat:@"DROP TABLE %@", tableName]; |
if (![DB executeUpdate:sqlstr]) |
{ |
WILog(@"Delete table error!"); |
return NO; |
} |
return YES; |
} |
// 清除表 |
- (BOOL) eraseTable:(NSString *)tableName |
{ |
NSString *sqlstr = [NSString stringWithFormat:@"DELETE FROM %@", tableName]; |
if (![DB executeUpdate:sqlstr]) |
{ |
WILog(@"Erase table error!"); |
return NO; |
} |
return YES; |
} |
// 插入数据 |
- (BOOL)insertTable:(NSString*)sql, ... |
{ |
va_list args; |
va_start(args, sql); |
BOOL result = [DB executeUpdate:sql error:nil withArgumentsInArray:nil orVAList:args]; |
va_end(args); |
return result; |
} |
// 修改数据 |
- (BOOL)updateTable:(NSString*)sql, ... |
{ |
va_list args; |
va_start(args, sql); |
BOOL result = [DB executeUpdate:sql error:nil withArgumentsInArray:nil orVAList:args]; |
va_end(args); |
return result; |
} |
// 暂时无用 |
#pragma mark 获得单一数据 |
// 整型 |
- (NSInteger)getDb_Integerdata:(NSString *)tableName withFieldName:(NSString *)fieldName |
{ |
NSInteger result = NO; |
NSString *sql = [NSString stringWithFormat:@"SELECT %@ FROM %@", fieldName, tableName]; |
FMResultSet *rs = [DB executeQuery:sql]; |
if ([rs next]) |
result = [rs intForColumnIndex:0]; |
[rs close]; |
return result; |
} |
// 布尔型 |
- (BOOL)getDb_Booldata:(NSString *)tableName withFieldName:(NSString *)fieldName |
{ |
BOOL result; |
result = [self getDb_Integerdata:tableName withFieldName:fieldName]; |
return result; |
} |
// 字符串型 |
- (NSString *)getDb_Stringdata:(NSString *)tableName withFieldName:(NSString *)fieldName |
{ |
NSString *result = NO; |
NSString *sql = [NSString stringWithFormat:@"SELECT %@ FROM %@", fieldName, tableName]; |
FMResultSet *rs = [DB executeQuery:sql]; |
if ([rs next]) |
result = [rs stringForColumnIndex:0]; |
[rs close]; |
return result; |
} |
// 二进制数据型 |
- (NSData *)getDb_Bolbdata:(NSString *)tableName withFieldName:(NSString *)fieldName |
{ |
NSData *result = NO; |
NSString *sql = [NSString stringWithFormat:@"SELECT %@ FROM %@", fieldName, tableName]; |
FMResultSet *rs = [DB executeQuery:sql]; |
if ([rs next]) |
result = [rs dataForColumnIndex:0]; |
[rs close]; |
return result; |
} |
@end |