一 FMDB简介
什么是 FMDB
- FMDB 是 iOS 平台的 SQLite 数据库框架
- FMDB 以 OC 的方式封装了 SQLite 的 C 语言 API
FMDB的优点
- 使用起来更加面向对象，省去了很多麻烦、冗余的C语言代码
- 对比苹果自带的Core Data框架，更加轻量级和灵活
- 提供了多线程安全的数据库操作方法，有效地防止数据混乱
FMDB 的地址
二 导入 FMDB
cocopads 引入 FMDB 库
pod 'FMDB'
核心类
FMDB有三个主要的类
FMDatabase
一个FMDatabase对象就代表一个单独的SQLite数据库，用来执行SQL语句。
FMResultSe
使用FMDatabase执行查询后的结果集
FMDatabaseQueue
用于在多线程中执行多个查询或更新，它是线程安全的
三 打开数据库
通过指定SQLite数据库文件路径来创建FMDatabase对象
// 1..创建数据库对象
FMDatabase *db = [FMDatabase databaseWithPath:path];
// 2.打开数据库
if ([db open]) {
// do something
} else {
DLog(@"fail to open database");
}
文件路径有三种情况
- 1.具体文件路径
如果不存在会自动创建，(使用绝对路径)
- (void)createDataBase {
// 获取数据库文件的路径
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *path = [docPath stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"path = %@",path);
// 1..创建数据库对象
FMDatabase *db = [FMDatabase databaseWithPath:path];
// 2.打开数据库
if ([db open]) {
// do something
NSLog(@"Open database Success");
} else {
NSLog(@"fail to open database");
}
}
image.png
- 2.空字符串
@""
会在临时目录创建一个空的数据库，当FMDatabase连接关闭时，数据库文件也被删除。
- 3.
nil
会创建一个内存中临时数据库，当FMDatabase连接关闭时，数据库会被销毁
path 可以是相对路径，也可以是绝对路径。
四 数据库操作
在FMDB中，除查询以外的所有操作，都称为“更新”
- create
- drop
- insert
- update
- delete
4.1 使用executeUpdate:方法执行更新
- 建表操作
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
- 写入数据
// 写入数据 - 不确定的参数用？来占位
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
- 删除数据
// 删除数据
NSString *sql = @"delete from t_student where id = ?";
[db executeUpdate:sql, [NSNumber numberWithInt:1]];
image.png
- 更改数据
// 更改数据
NSString *sql = @"update t_student set name = '齐天大圣' where id = ?";
[db executeUpdate:sql, [NSNumber numberWithInt:2]];
image.png
4.2 使用executeUpdateWithFormat:执行
// 使用executeUpdateWithFormat: - 不确定的参数用%@，%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:执行
// 使用 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 执行查询操作
查询方法
(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: 方法在一个字符串中执行多语句。
- (void)executeMuchSql {
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *path = [docPath stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"path = %@",path);
// 1..创建数据库对象
FMDatabase *db = [FMDatabase databaseWithPath:path];
// 2.打开数据库
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 单例是极其错误的想法，会导致每个线程创建一个 FMDatabase 对象。不要跨线程使用单例，也不要同时跨多线程，不然会奔溃或者异常。
因此不要实例化一个 FMDatabase 单例来跨线程使用。
相反，使用 FMDatabaseQueue，下面就是它的使用方法：
- 1.创建队列
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.把操作放在事务中也很简单，示例：
[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]];
}];
六 项目实战
1.gif
6.1 声明一个实现了归档协议的基类
- 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 定义一个数据模型
- 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;
/// 数据库存储
@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 的实现
(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]; // 创建数据库
NSAssert(result, @"Init Database fail");
}
(2) 创建一个操作队列
// 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;
}
// 内部真正实现删除的方法操作
- (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.因为本文方法是将数据模型归档存储到数据库中，所以数据模型必须实现归档协议。
2.为了方便使用，将数据库操作封装成一个类，外界可以很方便的使用。
本文参考 ios FMDB 简单使用，非常感谢该作者
更多同类型文章参考
iOS - SQLite3的使用详解
iOS-CoreData详解与使用
iOS-FMDB详解及使用
iOS SQLite、CoreData、FMDB数据库详解
