FMDB的一些基本操作小结

仅供自己记录使用，

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