FMDBのいくつかの基本的な操作のまとめ
自分で記録して使うだけで、
hファイル
mファイル
hファイル
#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
mファイル
#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