一 FMDB簡介

什么是 FMDB

• FMDB 是 iOS 平臺的 SQLite 數(shù)據(jù)庫框架
• FMDB 以 OC 的方式封裝了 SQLite 的 C 語言 API

FMDB的優(yōu)點(diǎn)

• 使用起來更加面向?qū)ο笮檠∪チ撕芏嗦闊┪汗觥⑷哂嗟腃語言代碼
• 對比蘋果自帶的Core Data框架奸披，更加輕量級和靈活
• 提供了多線程安全的數(shù)據(jù)庫操作方法污茵，有效地防止數(shù)據(jù)混亂

FMDB 的地址

• FMDB

二 導(dǎo)入 FMDB

cocopads 引入 FMDB 庫

pod 'FMDB'

核心類

FMDB有三個主要的類

• FMDatabase

一個FMDatabase對象就代表一個單獨(dú)的SQLite數(shù)據(jù)庫，用來執(zhí)行SQL語句蝙昙。

• FMResultSe

使用FMDatabase執(zhí)行查詢后的結(jié)果集

• FMDatabaseQueue

用于在多線程中執(zhí)行多個查詢或更新毯炮，它是線程安全的

三 打開數(shù)據(jù)庫

通過指定SQLite數(shù)據(jù)庫文件路徑來創(chuàng)建FMDatabase對象

// 1..創(chuàng)建數(shù)據(jù)庫對象
FMDatabase *db = [FMDatabase databaseWithPath:path];
// 2.打開數(shù)據(jù)庫
if ([db open]) {
    // do something
} else {
    DLog(@"fail to open database");
}

文件路徑有三種情況

• 1.具體文件路徑

如果不存在會自動創(chuàng)建，(使用絕對路徑)

- (void)createDataBase {
    // 獲取數(shù)據(jù)庫文件的路徑
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *path = [docPath stringByAppendingPathComponent:@"student.sqlite"];
    NSLog(@"path = %@",path);
    // 1..創(chuàng)建數(shù)據(jù)庫對象
    FMDatabase *db = [FMDatabase databaseWithPath:path];
    // 2.打開數(shù)據(jù)庫
    if ([db open]) {
        // do something
        NSLog(@"Open database Success");
    } else {
        NSLog(@"fail to open database");
    }
}

image.png

• 2.空字符串 @""

會在臨時目錄創(chuàng)建一個空的數(shù)據(jù)庫耸黑，當(dāng)FMDatabase連接關(guān)閉時，數(shù)據(jù)庫文件也被刪除篮幢。

• 3.nil

會創(chuàng)建一個內(nèi)存中臨時數(shù)據(jù)庫大刊，當(dāng)FMDatabase連接關(guān)閉時，數(shù)據(jù)庫會被銷毀

path 可以是相對路徑三椿，也可以是絕對路徑缺菌。

四 數(shù)據(jù)庫操作

在FMDB中，除查詢以外的所有操作搜锰，都稱為“更新”

• create
• drop
• insert
• update
• delete

4.1 使用executeUpdate:方法執(zhí)行更新

• 建表操作

NSString *createTableSqlString = @"CREATE TABLE IF NOT EXISTS t_student (id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL)";
[db executeUpdate:createTableSqlString];

image.png

• 寫入數(shù)據(jù)

// 寫入數(shù)據(jù) - 不確定的參數(shù)用伴郁？來占位
NSString *sql = @"insert into t_student (name, age) values (?, ?)";
NSString *name = [NSString stringWithFormat:@"韓雪 - %d",arc4random()];
NSNumber *age = [NSNumber numberWithInt:arc4random_uniform(100)];
[db executeUpdate:sql, name, age];

image.png

• 刪除數(shù)據(jù)

// 刪除數(shù)據(jù)
NSString *sql = @"delete from t_student where id = ?";
[db executeUpdate:sql, [NSNumber numberWithInt:1]];

image.png

• 更改數(shù)據(jù)

// 更改數(shù)據(jù)
NSString *sql = @"update t_student set name = '齊天大圣'  where id = ?";
[db executeUpdate:sql, [NSNumber numberWithInt:2]];

image.png

4.2 使用executeUpdateWithFormat:執(zhí)行

// 使用executeUpdateWithFormat: - 不確定的參數(shù)用%@焊傅，%d等來占位
NSString *sql = @"insert into t_student (name,age) values (%@,%i)";
NSString *name = [NSString stringWithFormat:@"孫悟空 - %d",arc4random()];
[db executeUpdateWithFormat:sql, name, arc4random_uniform(100)];

image.png

4.3 使用 executeUpdate:withParameterDictionary:執(zhí)行

// 使用 executeUpdate:withParameterDictionary:
NSString *name = [NSString stringWithFormat:@"玉皇大帝 - %d",arc4random()];
NSNumber *age = [NSNumber numberWithInt:arc4random_uniform(100)];
NSDictionary *studentDict = [NSDictionary dictionaryWithObjectsAndKeys:name, @"name", age, @"age", nil];
[db executeUpdate:@"insert into t_student (name, age) values (:name, :age)" withParameterDictionary:studentDict];

image.png

4.4 執(zhí)行查詢操作

查詢方法

• (FMResultSet )executeQuery:(NSString)sql, ...
• (FMResultSet )executeQueryWithFormat:(NSString)format, ...

示例：

// 4.查詢
NSString *sql = @"select id, name, age FROM t_student";
FMResultSet *rs = [db executeQuery:sql];
while ([rs next]) {
    int id = [rs intForColumnIndex:0];
    NSString *name = [rs stringForColumnIndex:1];
    int age = [rs intForColumnIndex:2];
    Student *student = [[Student alloc] init];
    student.name = name;
    student.age = age;
    [students addObject:student];
}

image.png

4.5 多語句和批處理

FMDatabase 可以通過 -executeStatements:withResultBlock: 方法在一個字符串中執(zhí)行多語句。

- (void)executeMuchSql {
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *path = [docPath stringByAppendingPathComponent:@"student.sqlite"];
    NSLog(@"path = %@",path);
    
    // 1..創(chuàng)建數(shù)據(jù)庫對象
    FMDatabase *db = [FMDatabase databaseWithPath:path];
    // 2.打開數(shù)據(jù)庫
    if ([db open]) {
        NSString *sql = @"CREATE TABLE IF NOT EXISTS bulktest1 (id integer PRIMARY KEY AUTOINCREMENT, x text);"
        "CREATE TABLE IF NOT EXISTS bulktest2 (id integer PRIMARY KEY AUTOINCREMENT, y text);"
        "CREATE table IF NOT EXISTS bulktest3 (id integer primary key autoincrement, z text);"
        "insert into bulktest1 (x) values ('XXX');"
        "insert into bulktest2 (y) values ('YYY');"
        "insert into bulktest3 (z) values ('ZZZ');";
        
        BOOL result = [db executeStatements:sql];
        
        sql = @"select count(*) as count from bulktest1;"
        "select count(*) as count from bulktest2;"
        "select count(*) as count from bulktest3;";
        
        result = [db executeStatements:sql withResultBlock:^int(NSDictionary *resultsDictionary) {
            NSLog(@"dictionary=%@", resultsDictionary);
            return 0;
        }];
    } else {
        NSLog(@"fail to open database");
    }
}

image.png

五 隊列和線程安全

在多線程中同時使用 FMDatabase 單例是極其錯誤的想法鸭栖，會導(dǎo)致每個線程創(chuàng)建一個 FMDatabase 對象握巢。不要跨線程使用單例晕鹊，也不要同時跨多線程，不然會奔潰或者異常暴浦。

因此不要實例化一個 FMDatabase 單例來跨線程使用。

相反飞几，使用 FMDatabaseQueue同规，下面就是它的使用方法：

• 1.創(chuàng)建隊列

FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath];

• 2.示例

[queue inDatabase:^(FMDatabase *db) {
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:1]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:2]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:3]];

    FMResultSet *rs = [db executeQuery:@"select * from foo"];
    while ([rs next]) {
        ...
    }
}];

• 3.把操作放在事務(wù)中也很簡單券勺，示例：

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:1]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:2]];
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:3]];

    if (whoopsSomethingWrongHappened) {
        *rollback = YES;
        return;
    }
    // ...
    [db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:4]];
}];

六 項目實戰(zhàn)

1.gif

6.1 聲明一個實現(xiàn)了歸檔協(xié)議的基類

• CSArchiveBaseModel.h

@interface CSArchiveBaseModel : NSObject <NSCoding , NSCopying>

@end

• CSArchiveBaseModel.m

#import "CSArchiveBaseModel.h"
#import <objc/runtime.h>

@implementation CSArchiveBaseModel

- (void)encodeWithCoder:(NSCoder *)aCoder {
    NSArray *names = [[self class] getPropertyNames];
    for (NSString *name in names) {
        id value = [self valueForKey:name];
        [aCoder encodeObject:value forKey:name];
    }
}

- (nullable instancetype)initWithCoder:(NSCoder *)aDecoder {
    if (self = [super init]) {
        NSArray *names = [[self class] getPropertyNames];
        for (NSString *name in names) {
            id value = [aDecoder decodeObjectForKey:name];
            [self setValue:value forKey:name];
        }
    }
    return self;
}

- (id)copyWithZone:(NSZone *)zone {
    id obj = [[[self class] alloc] init];
    NSArray *names = [[self class] getPropertyNames];
    for (NSString *name in names) {
        id value = [self valueForKey:name];
        [obj setValue:value forKey:name];
    }
    return obj;
}

+ (NSArray *)getPropertyNames {
    // Property count
    unsigned int count;
    // Get property list
    objc_property_t *properties = class_copyPropertyList([self class], &count);
    // Get names
    NSMutableArray *array = [NSMutableArray array];
    for (int i = 0; i < count; i++) {
        // objc_property_t
        objc_property_t property = properties[i];
        const char *cName = property_getName(property);
        NSString *name = [NSString stringWithCString:cName encoding:NSUTF8StringEncoding];
        if (name.length) {
            [array addObject:name];
        }
    }
    free(properties);
    return array;
}

@end

6.2 定義一個數(shù)據(jù)模型

• Student.h

@interface Student : CSArchiveBaseModel

@property (nonatomic) int age;
@property (nonatomic) int height;
@property (nullable, nonatomic, copy) NSString *name;
@property (nonatomic) int number;
@property (nullable, nonatomic, copy) NSString *sex;

/** time */
@property(nonatomic, strong)NSString *startTime;

@end

注意：該類繼承自CSArchiveBaseModel

6.3 定義一個存儲工具類

• CSStorageManager.h

@class Student;

/// 數(shù)據(jù)庫存儲
@interface CSStorageManager : NSObject

+ (instancetype)sharedManager;

#pragma mark - motion Model Actions

/**
 Save a motion model to database.
 */
- (BOOL)saveStudentModel:(Student *)model;

/**
 Get all motion models in database(this time). If nothing, it will return an emtpy array.
 */
- (NSArray <Student *>*)getAllStudentModels;

/**
 According to the models to remove the motion models where happened in dataBase.
 If any one fails, it returns to NO, and any failure will not affect others.
 */
- (BOOL)removeStudentModels:(NSArray <Student *>*)models;

@end

該方法封裝好了插入程腹，查找儒拂，刪除等操作

• CSStorageManager.m 的實現(xiàn)

(1) 因為本工具類頻繁操作，所以聲明一個單例

static CSStorageManager *_instance = nil;

+ (instancetype)sharedManager {
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        _instance = [[CSStorageManager alloc] init];
        [_instance initial];
    });
    return _instance;
}

- (void)initial {
    __unused BOOL result = [self initDatabase];  // 創(chuàng)建數(shù)據(jù)庫
    NSAssert(result, @"Init Database fail");
}

(2) 創(chuàng)建一個操作隊列

// Table SQL
static NSString *const kCreateStudentModelTableSQL = @"CREATE TABLE IF NOT EXISTS StudentModelTable(ObjectData BLOB NOT NULL,CreatehDate TEXT NOT NULL);";

// Table Name
static NSString *const kStudentModelTable = @"StudentModelTable";

// Column Name
static NSString *const kObjectDataColumn = @"ObjectData";
static NSString *const kIdentityColumn = @"Identity";
static NSString *const kCreateDateColumn = @"CreatehDate";

/**
 Init database.
 */
- (BOOL)initDatabase {
    NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    doc = [doc stringByAppendingPathComponent:@"LDebugTool"];
    if (![[NSFileManager defaultManager] fileExistsAtPath:doc]) {
        NSError *error;
        [[NSFileManager  defaultManager] createDirectoryAtPath:doc withIntermediateDirectories:YES attributes:nil error:&error];
        if (error) {
            NSLog(@"CSStorageManager create folder fail, error = %@",error.description);
        }
        NSAssert(!error, error.description);
    }
    NSString *filePath = [doc stringByAppendingPathComponent:@"LDebugTool.db"];
    
    _dbQueue = [FMDatabaseQueue databaseQueueWithPath:filePath];
    
    __block BOOL ret1 = NO;
    [_dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
        // ObjectData use to convert to BGLCrashModel, launchDate use as identity
        ret1 = [db executeUpdate:kCreateStudentModelTableSQL];
        if (!ret1) {
            NSLog(@"LLStorageManager create StudentModelTable fail");
        }
    }];
    return ret1;
}

• 增，刪斩箫，改撵儿，查操作

(3) 插入操作

- (BOOL)saveStudentModel:(Student *)model {
    NSData *data = [NSKeyedArchiver archivedDataWithRootObject:model];
    if (data.length == 0) {
        NSLog(@"CSStorageManager save student model fail, because model's data is null");
        return NO;
    }
    
    __block NSArray *arguments = @[data, model.startTime];
    __block BOOL ret = NO;
    
    [_dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
        NSError *error;
        ret = [db executeUpdate:[NSString stringWithFormat:@"INSERT INTO %@(%@,%@) VALUES (?,?);",kStudentModelTable,kObjectDataColumn,kCreateDateColumn] values:arguments error:&error];
        if (!ret) {
            NSLog(@"CSStorageManager save crash model fail,Error = %@",error.localizedDescription);
        } else {
            NSLog(@"CSStorageManager save crash success!");
        }
    }];
    return ret;
}

(4) 查詢操作

- (NSArray <Student *>*)getAllStudentModels {
    __block NSMutableArray *modelArray = [[NSMutableArray alloc] init];
    
    [_dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
        FMResultSet *set = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@",kStudentModelTable]];
        while ([set next]) {
            NSData *objectData = [set dataForColumn:kObjectDataColumn];
            Student *model = [NSKeyedUnarchiver unarchiveObjectWithData:objectData];
            if (model) {
                [modelArray insertObject:model atIndex:0];
            }
        }
    }];
    
    return modelArray.copy;
}

(5) 刪除操作

- (BOOL)removeStudentModels:(NSArray <Student *>*)models {
    BOOL ret = YES;
    for (Student *model in models) {
        ret = ret && [self _removeMotionModel:model];
    }
    return ret;
}

// 內(nèi)部真正實現(xiàn)刪除的方法操作
- (BOOL)_removeMotionModel:(Student *)model {
    __block BOOL ret = NO;
    [_dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
        NSError *error;
        ret = [db executeUpdate:[NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ = ?",kStudentModelTable,kCreateDateColumn] values:@[model.startTime] error:&error];
        if (!ret) {
            NSLog(@"Delete Student model fail,error = %@",error);
        }
    }];
    return ret;
}

6.4 外部使用存儲工具類

// 插入
Student * student = [[Student alloc] init];
BOOL result = [[CSStorageManager sharedManager] saveStudentModel:student];

// 查找
NSArray *students = [[CSStorageManager sharedManager] getAllStudentModels];

// 刪除
BOOL result = [[CSStorageManager sharedManager] removeStudentModels:delStudents];

image.png

注意事項
1.因為本文方法是將數(shù)據(jù)模型歸檔存儲到數(shù)據(jù)庫中易核，所以數(shù)據(jù)模型必須實現(xiàn)歸檔協(xié)議浪默。
2.為了方便使用缀匕，將數(shù)據(jù)庫操作封裝成一個類井氢，外界可以很方便的使用花竞。

本文參考 ios FMDB 簡單使用，非常感謝該作者

更多同類型文章參考
iOS - SQLite3的使用詳解
iOS-CoreData詳解與使用
iOS-FMDB詳解及使用
iOS SQLite零远、CoreData厌蔽、FMDB數(shù)據(jù)庫詳解

項目連接地址 - FMDB_Demo