使用說明

 增刪改查造作：

FMDatabase

#import <Foundation/Foundation.h>
#import "FMDatabaseAdditions.h"

@class FMDatabase;

/**
 * @brief 對數據鏈接進行管理，包括鏈接，關閉連接
 * 可以建立長連接 長連接
 */
@interface SDBManager : NSObject {
    NSString * _name;
}
/// 數據庫操作對象，當數據庫被建立時，會存在次至
@property (nonatomic, readonly) FMDatabase * dataBase;  // 數據庫操作對象
/// 單例模式
+(SDBManager *) defaultDBManager;

// 關閉數據庫
- (void) close;

@end

#--------------------------------------------------------------------------------------------------------

#import "SDBManager.h"
#import "FMDatabase.h"

#define kDefaultDBName @"voice.sqlite"

@interface SDBManager ()

@end

@implementation SDBManager

static SDBManager * _sharedDBManager;

+ (SDBManager *) defaultDBManager {
    if (!_sharedDBManager) {
        _sharedDBManager = [[SDBManager alloc] init];
    }
    return _sharedDBManager;
}

- (void) dealloc {
    [self close];
}

- (id) init {
    self = [super init];
    if (self) {
        int state = [self initializeDBWithName:kDefaultDBName];
        if (state == -1) {
            NSLog(@"數據庫初始化失敗");
        } else {
            NSLog(@"數據庫初始化成功");
        }
    }
    return self;
}

/**
 * @brief 初始化數據庫操作
 * @param name 數據庫名稱
 * @return 返回數據庫初始化狀態， 0 為 已經存在，1 為創建成功，-1 為創建失敗
 */
- (int) initializeDBWithName : (NSString *) name {
    if (!name) {
        return -1;  // 返回數據庫創建失敗
    }
    // 沙盒Docu目錄
    NSString * docp = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) objectAtIndex:0];
    _name = [docp stringByAppendingString:[NSString stringWithFormat:@"/%@",name]];
    NSFileManager * fileManager = [NSFileManager defaultManager];
    BOOL exist = [fileManager fileExistsAtPath:_name];
    [self connect];
    if (!exist) {
        return 0;
    } else {
        return 1;          // 返回 數據庫已經存在
       
    }
   
}

/// 連接數據庫
- (void) connect {
    if (!_dataBase) {
        _dataBase = [[FMDatabase alloc] initWithPath:_name];
    }
    if (![_dataBase open]) {
        NSLog(@"不能打開數據庫");
    }
}
/// 關閉連接
- (void) close {
    [_dataBase close];
    _sharedDBManager = nil;
}

@end

 

#--------------------------------------------------------------------------------------------------------

 

 

#import "SDBManager.h"
#import "SUser.h"

@interface SUserDB : NSObject {
    FMDatabase * _db;
}

/**
 * @brief 創建數據庫
 */
- (void) createDataBase;
/**
 * @brief 保存一條用戶記錄
 *
 * @param user 需要保存的用戶數據
 */
- (void) saveUser:(SUser *) user;

/**
 * @brief 刪除一條用戶數據
 *
 * @param uid 需要刪除的用戶的id
 */
- (void) deleteUserWithId:(NSString *) uid;

/**
 * @brief 修改用戶的信息
 *
 * @param user 需要修改的用戶信息
 */
- (void) mergeWithUser:(SUser *) user;

/**
 * @brief 模擬分頁查找數據。取uid大於某個值以後的limit個數據
 *
 * @param uid
 * @param limit 每頁取多少個
 */
- (NSArray *) findWithUid:(NSString *) uid limit:(int) limit;

@end

#--------------------------------------------------------------------------------------------------------

 

#import "SUserDB.h"

#define kUserTableName @"SUser"

@implementation SUserDB

- (id) init {
    self = [super init];
    if (self) {
        //========== 首先查看有沒有建立message的數據庫，如果未建立，則建立數據庫=========
        _db = [SDBManager defaultDBManager].dataBase;
       
    }
    return self;
}

/**
 * @brief 創建數據庫
 */
- (void) createDataBase {
    FMResultSet * set = [_db executeQuery:[NSString stringWithFormat:@"select count(*) from sqlite_master where type ='table' and name = '%@'",kUserTableName]];
   
    [set next];
   
    NSInteger count = [set intForColumnIndex:0];
   
    BOOL existTable = !!count;
   
    if (existTable) {
        // TODO:是否更新數據庫
        [AppDelegate showStatusWithText:@"數據庫已經存在" duration:2];
    } else {
        // TODO: 插入新的數據庫
        NSString * sql = @"CREATE TABLE SUser (uid INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL, name VARCHAR(50), description VARCHAR(100))";
        BOOL res = [_db executeUpdate:sql];
        if (!res) {
            [AppDelegate showStatusWithText:@"數據庫創建失敗" duration:2];
        } else {
            [AppDelegate showStatusWithText:@"數據庫創建成功" duration:2];
        }
    }
}

/**
 * @brief 保存一條用戶記錄
 *
 * @param user 需要保存的用戶數據
 */
- (void) saveUser:(SUser *) user {
    NSMutableString * query = [NSMutableString stringWithFormat:@"INSERT INTO SUser"];
    NSMutableString * keys = [NSMutableString stringWithFormat:@" ("];
    NSMutableString * values = [NSMutableString stringWithFormat:@" ( "];
    NSMutableArray * arguments = [NSMutableArray arrayWithCapacity:5];
    if (user.name) {
        [keys appendString:@"name,"];
        [values appendString:@"?,"];
        [arguments addObject:user.name];
    }
    if (user.description) {
        [keys appendString:@"description,"];
        [values appendString:@"?,"];
        [arguments addObject:user.description];
    }
    [keys appendString:@")"];
    [values appendString:@")"];
    [query appendFormat:@" %@ VALUES%@",
     [keys stringByReplacingOccurrencesOfString:@",)" withString:@")"],
     [values stringByReplacingOccurrencesOfString:@",)" withString:@")"]];
    NSLog(@"%@",query);
    [AppDelegate showStatusWithText:@"插入一條數據" duration:2.0];
    [_db executeUpdate:query withArgumentsInArray:arguments];
}

/**
 * @brief 刪除一條用戶數據
 *
 * @param uid 需要刪除的用戶的id
 */
- (void) deleteUserWithId:(NSString *) uid {
    NSString * query = [NSString stringWithFormat:@"DELETE FROM SUser WHERE uid = '%@'",uid];
    [AppDelegate showStatusWithText:@"刪除一條數據" duration:2.0];
    [_db executeUpdate:query];
}

/**
 * @brief 修改用戶的信息
 *
 * @param user 需要修改的用戶信息
 */
- (void) mergeWithUser:(SUser *) user {
    if (!user.uid) {
        return;
    }
    NSString * query = @"UPDATE SUser SET";
    NSMutableString * temp = [NSMutableString stringWithCapacity:20];
    // xxx = xxx;
    if (user.name) {
        [temp appendFormat:@" name = '%@',",user.name];
    }
    if (user.description) {
        [temp appendFormat:@" description = '%@',",user.description];
    }
    [temp appendString:@")"];
    query = [query stringByAppendingFormat:@"%@ WHERE uid = '%@'",[temp stringByReplacingOccurrencesOfString:@",)" withString:@""],user.uid];
    NSLog(@"%@",query);
   
    [AppDelegate showStatusWithText:@"修改一條數據" duration:2.0];
    [_db executeUpdate:query];
}

/**
 * @brief 模擬分頁查找數據。取uid大於某個值以後的limit個數據
 *
 * @param uid
 * @param limit 每頁取多少個
 */
- (NSArray *) findWithUid:(NSString *) uid limit:(int) limit {
    NSString * query = @"SELECT uid,name,description FROM SUser";
    if (!uid) {
        query = [query stringByAppendingFormat:@" ORDER BY uid DESC limit %d",limit];
    } else {
        query = [query stringByAppendingFormat:@" WHERE uid > %@ ORDER BY uid DESC limit %d",uid,limit];
    }

    FMResultSet * rs = [_db executeQuery:query];
    NSMutableArray * array = [NSMutableArray arrayWithCapacity:[rs columnCount]];
    while ([rs next]) {
        SUser * user = [SUser new];
        user.uid = [rs stringForColumn:@"uid"];
        user.name = [rs stringForColumn:@"name"];
        user.description = [rs stringForColumn:@"description"];
        [array addObject:user];
    }
    [rs close];
    return array;
}

@end

 

 

調用：

- (void)initFMDB{
    BlogDB * db = [[[BlogDB alloc] init] autorelease];
    [db createDataBase];
}

 

        BlogDB *blogDB = [[BlogDB alloc] init];
        [blogDB saveBlog:blog];
        [blogDB findWithBlogid:nil limit:20];
        NSArray *arr = [blogDB findWithBlogid:nil limit:20];       

        NSLog(@"數據數量： %d",[arr count] );
        [blogDB release];