本文示例代碼查看
0.0 FMDB 結(jié)構(gòu)圖
1.FMDB 使用方法
1.1 類
1.1.1 三個(gè)基本類
- FMDatabase: 代表一個(gè) SQLite 數(shù)據(jù)庫(kù),用于執(zhí)行 SQLite 語(yǔ)句;
- FMResultSet:表示FMDatabase執(zhí)行查詢后結(jié)果集
- FMDatabaseQueue:如果你想在多線程中執(zhí)行多個(gè)查詢或更新，你應(yīng)該使用該類。這是線程安全的稽鞭。
1.1.2 其他類
- FMDatabasePool:使用任務(wù)池的形式,對(duì)多線程的操作提供支持凹髓。(官方不推薦使用)
- FMDatabaseAdditions: 擴(kuò)展`FMDatabase類,新增對(duì)查詢結(jié)果只返回單個(gè)值的方法進(jìn)行簡(jiǎn)化,對(duì)表、列是否存在,版本號(hào),校驗(yàn)SQL等等功能。
1.2 數(shù)據(jù)庫(kù)操作
創(chuàng)建Student模型
Student.h
#import <Foundation/Foundation.h>
@interface Student : NSObject
@property (nonatomic,copy)NSString *name;
@property (nonatomic,copy)NSString *sex;
@property (nonatomic,assign) int age;
@property (nonatomic,assign) int studentID;
@end
1.2.1 創(chuàng)建數(shù)據(jù)庫(kù)
創(chuàng)建方式如下圖：
- 以上初始化方法本質(zhì)上只是給了數(shù)據(jù)庫(kù)一個(gè)名字，并沒(méi)有真實(shí)創(chuàng)建或者獲取數(shù)據(jù)庫(kù)，
open函數(shù)才是真正獲取到數(shù)據(jù)庫(kù)线欲，其本質(zhì)上也就是調(diào)用SQLite的C/C++接口– sqlite3_open()
NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *file = [doc stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"%@",file);
//2.獲得數(shù)據(jù)庫(kù)
FMDatabase *db = [FMDatabase databaseWithPath:file];
//3.使用如下語(yǔ)句，如果打開失敗汽摹，可能是權(quán)限不足或者資源不足李丰。通常打開完操作操作后，需要調(diào)用`close`方法來(lái)關(guān)閉數(shù)據(jù)庫(kù)逼泣。在和數(shù)據(jù)庫(kù)交互之前嫌套，數(shù)據(jù)庫(kù)必須是打開的。如果資源或權(quán)限不足無(wú)法打開或創(chuàng)建數(shù)據(jù)庫(kù)圾旨，都會(huì)導(dǎo)致打開失敗踱讨。
if ([db open]){
//4.創(chuàng)表
BOOL result = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS student (studentID integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL);"];
if (result){
NSLog(@"創(chuàng)建表成功");
}else {
NSLog(@"創(chuàng)建表失敗");
}
}
如果創(chuàng)建失敗可以調(diào)用
- (NSString*)lastErrorMessage;和- (int)lastErrorCode;查看錯(cuò)誤信息
1.2.2 打開/關(guān)閉數(shù)據(jù)庫(kù)
打開數(shù)據(jù)庫(kù)
- (BOOL)open;
- (BOOL)openWithFlags:(int)flags;
- (BOOL)openWithFlags:(int)flags vfs:(NSString * _Nullable)vfsName;
*關(guān)閉數(shù)據(jù)庫(kù)
- (BOOL)close;
主要封裝了sqlite_close函數(shù)。
主要功能：
①清除緩存的prepare語(yǔ)句砍的；
②關(guān)閉數(shù)據(jù)庫(kù)（關(guān)閉數(shù)據(jù)的時(shí)候?qū)?shù)據(jù)庫(kù)當(dāng)中的prepare語(yǔ)句全部清除掉）
1.2.3 更新數(shù)據(jù)
使用
- (BOOL)executeUpdate:(NSString*)sql, ...;
- (BOOL)executeUpdateWithFormat:(NSString *)format, ... NS_FORMAT_FUNCTION(1,2);
- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments;
方法執(zhí)行更新操作痹筛，該方法返回BOOL型, 成功返回YES, 失敗返回NO.
說(shuō)明:
除了
SELECT外的SQL操作,都被視為更新操作.包括CREATE,UPDATE,INSERT,ALTER,COMMIT,BEGIN,DETACH,DELETE,DROP,END,EXPLAIN,VACUUM,REPLACE等;- (BOOL)executeUpdate:(NSString*)sql, ...;使用標(biāo)準(zhǔn)的SQL語(yǔ)句,參數(shù)用?來(lái)占位,參數(shù)必須是對(duì)象類型,不能是int,double``,bool等基本數(shù)據(jù)類型;- (BOOL)executeUpdateWithFormat:(NSString *)format, ... NS_FORMAT_FUNCTION(1,2);使用字符串的格式化構(gòu)建SQL語(yǔ)句,參數(shù)用%@、%d等來(lái)占位.- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments;
也可以把對(duì)應(yīng)的參數(shù)裝到數(shù)組里面?zhèn)鬟M(jìn)去,SQL語(yǔ)句中的參數(shù)用?代替.
如：
NSArray *sqlArr = @[@"靜靜", @11];
[db executeUpdate:@"INSERT INTO student (name,age) values (?,?)" withArgumentsInArray:sqlArr];
NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *file = [doc stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"%@",file);
//2.獲得數(shù)據(jù)庫(kù)
FMDatabase *db = [FMDatabase databaseWithPath:file];
//3.使用如下語(yǔ)句廓鞠，如果打開失敗帚稠，可能是權(quán)限不足或者資源不足。通常打開完操作操作后床佳，需要調(diào)用 close 方法來(lái)關(guān)閉數(shù)據(jù)庫(kù)滋早。在和數(shù)據(jù)庫(kù)交互 之前，數(shù)據(jù)庫(kù)必須是打開的砌们。如果資源或權(quán)限不足無(wú)法打開或創(chuàng)建數(shù)據(jù)庫(kù)杆麸，都會(huì)導(dǎo)致打開失敗搁进。
if ([db open]){
//4.創(chuàng)表
BOOL result = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS student (studentID integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL);"];
if (result){
NSLog(@"創(chuàng)建表成功");
// INSERT INTO student (name, age) VALUES (?, ?);
NSString * sql = @"INSERT INTO student (name, age) VALUES(?, ?);";
NSString * name = @"靜靜";
BOOL res = [db executeUpdate:sql, name, @2];
// BOOL res = [db executeUpdateWithFormat:@"INSERT INTO student (name, age) VALUES (%@, %d);", name, 1];
if (!res) {
NSLog(@"插入失敗");
} else {
NSLog(@"插入成功");
}
//查看最后一條錯(cuò)誤信息
NSString *errorStr = [db lastErrorMessage];
NSLog(@"%@",errorStr);
//查看最后一條錯(cuò)誤
NSError *error = [db lastError];
NSLog(@"%@",error);
[db close];
}else {
NSLog(@"創(chuàng)建表失敗");
}
}
運(yùn)行如圖:
還可以使用- (BOOL)executeStatements:(NSString *)sql;或者
- (BOOL)executeStatements:(NSString *)sql withResultBlock:(__attribute__((noescape)) FMDBExecuteStatementsCallbackBlock _Nullable)block;
將多個(gè)SQL執(zhí)行語(yǔ)句寫在一個(gè)字符串中，并執(zhí)行昔头。
NSString *sql1 = @"CREATE TABLE IF NOT EXISTS school (schoolID text PRIMARY KEY, schoolName text NOT NULL, address text NOT NULL);"
"insert into school (schoolID,schoolName,address) values ('1','第一中學(xué)','阿里部落');"
;
BOOL res1 = [db executeStatements:sql1];
運(yùn)行如圖：
基本上- (BOOL)executeStatements:(NSString *)sql;系列函數(shù)最終封裝的都是
- (BOOL)executeStatements:(NSString *)sql withResultBlock:(__attribute__((noescape)) FMDBExecuteStatementsCallbackBlock _Nullable)block;函數(shù)饼问，而此函數(shù)又是對(duì)sqlite3_exec函數(shù)的封裝。
1.2.4 查詢數(shù)據(jù)
查詢有以下方法：
所有查詢函數(shù)都是對(duì)-(FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray*)arrayArgs orDictionary:(NSDictionary *)dictionaryArgs orVAList:(va_list)args;的簡(jiǎn)單封裝揭斧。
-(FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray*)arrayArgs orDictionary:(NSDictionary *)dictionaryArgs orVAList:(va_list)args;此函數(shù)的大致執(zhí)行步驟請(qǐng)見(jiàn)XMIND
NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *file = [doc stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"%@",file);
//2.獲得數(shù)據(jù)庫(kù)
FMDatabase *db = [FMDatabase databaseWithPath:file];
if ([db open]) {
FMResultSet *results = [db executeQuery:@"SELECT * FROM student"];
// 2.遍歷結(jié)果
while ([results next]) {
int ID = [results intForColumn:@"studentID"];
NSString *name = [results stringForColumn:@"name"];
int age = [results intForColumn:@"age"];
NSLog(@"%d %@ %d", ID, name, age);
}
}
1.2.5 刪除數(shù)據(jù)
NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *file = [doc stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"%@",file);
//2.獲得數(shù)據(jù)庫(kù)
FMDatabase *db = [FMDatabase databaseWithPath:file];
if ([db open]) {
BOOL res = [db executeUpdate:@"delete from student where studentID = ?;",@(1)];
if (res) {
NSLog(@"刪除成功");
}else {
NSLog(@"刪除失敗");
}
}
1.3 多線程處理
FMDatabase這個(gè)類是線程不安全的莱革，如果在多個(gè)線程中同時(shí)使用一個(gè)
FMDatabase實(shí)例，會(huì)造成數(shù)據(jù)混亂等問(wèn)題讹开。
為了保證線程安全盅视，FMDB提供方便快捷的FMDatabaseQueue。
代碼如下：
NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *file = [doc stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"%@",file);
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:file];
dispatch_queue_t q1 = dispatch_queue_create("queue1", NULL);
dispatch_queue_t q2 = dispatch_queue_create("queue2", NULL);
dispatch_async(q1, ^{
for (int i = 0; i < 50; ++i) {
[queue inDatabase:^(FMDatabase *db2) {
if ([db2 open]){
BOOL result = [db2 executeUpdate:@"CREATE TABLE IF NOT EXISTS student (studentID integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL);"];
if (result) {
NSString *insertSql1= [NSString stringWithFormat:
@"INSERT INTO '%@' ('%@', '%@') VALUES (?, ?)",
@"student", @"name", @"age"];
NSString * name = [NSString stringWithFormat:@"深深 %d", i];
NSString * age = [NSString stringWithFormat:@"%d", 10+i];
BOOL res = [db2 executeUpdate:insertSql1, name, age];
if (!res) {
NSLog(@"error to inster data: %@", name);
} else {
NSLog(@"succ to inster data: %@", name);
}
}
}
}];
}
});
dispatch_async(q2, ^{
for (int i = 0; i < 50; ++i) {
[queue inDatabase:^(FMDatabase *db2) {
if ([db2 open]) {
BOOL result = [db2 executeUpdate:@"CREATE TABLE IF NOT EXISTS student (studentID integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL);"];
if (result) {
NSString *insertSql2= [NSString stringWithFormat:
@"INSERT INTO '%@' ('%@', '%@') VALUES (?, ?)",
@"student", @"name", @"age"];
NSString * name = [NSString stringWithFormat:@"靜靜 %d", i];
NSString * age = [NSString stringWithFormat:@"%d", 10+i];
BOOL res = [db2 executeUpdate:insertSql2, name, age];
if (!res) {
NSLog(@"error to inster data: %@", name);
} else {
NSLog(@"succ to inster data: %@", name);
}
}
}
}];
}
});
1.4 事務(wù)
什么是事務(wù)
說(shuō)一下事務(wù)是什么旦万，比如說(shuō)我們有一個(gè)學(xué)生表和一個(gè)學(xué)生成績(jī)表左冬，而且一個(gè)學(xué)生對(duì)應(yīng)一個(gè)學(xué)生成績(jī)。比如小明的成績(jī)是100分纸型，那么我們要寫兩個(gè)
sql語(yǔ)句對(duì)不同的表進(jìn)行插入數(shù)據(jù)。但是如果在這個(gè)過(guò)程中梅忌，小明這個(gè)學(xué)生成功的插入到數(shù)據(jù)庫(kù)狰腌，而成績(jī)插入時(shí)失敗了，怎么辦牧氮？這時(shí)事務(wù)就突出了它的作用琼腔。用事務(wù)可以對(duì)兩個(gè)表進(jìn)行同時(shí)插入，一旦一個(gè)表插入失敗踱葛，那么就會(huì)進(jìn)行事務(wù)回滾丹莲，就是讓另一個(gè)表也不進(jìn)行插入數(shù)據(jù)了。
簡(jiǎn)單的說(shuō)也就是尸诽，事務(wù)可以讓多個(gè)表的數(shù)據(jù)同時(shí)插入甥材，一旦有一個(gè)表操作失敗，那么其他表也都會(huì)失敗性含。當(dāng)然這種說(shuō)法是為了理解洲赵，不是嚴(yán)謹(jǐn)?shù)摹?/p>
那么對(duì)一個(gè)表大量插入數(shù)據(jù)時(shí)也可以用事務(wù)。比如
sqlite3商蕴。
數(shù)據(jù)庫(kù)中
insert into語(yǔ)句等操作是比較耗時(shí)的叠萍，假如我們一次性插入幾百幾千條數(shù)據(jù)就
會(huì)造成主線程阻塞，以至于UI界面卡住绪商。那么這時(shí)候我們就要開啟一個(gè)事物來(lái)進(jìn)行操作苛谷。
原因就是它以文件的形式存在磁盤中，每次訪問(wèn)時(shí)都要打開一次文件格郁，如果對(duì)數(shù)據(jù)庫(kù)進(jìn)行大量的操作腹殿，就很慢独悴。可是如果我們用事務(wù)的形式提交赫蛇，開始事務(wù)后绵患，進(jìn)行的大量操作語(yǔ)句都保存在內(nèi)存中，當(dāng)提交時(shí)才全部寫入數(shù)據(jù)庫(kù)悟耘，此時(shí)落蝙，數(shù)據(jù)庫(kù)文件也只用打開一次。如果操作錯(cuò)誤暂幼，還可以回滾事務(wù)筏勒。
1.4.1 基本使用
NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *file = [doc stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"%@",file);
FMDatabase *db = [FMDatabase databaseWithPath:file];
BOOL isSuccess=[db open];
if (!isSuccess) {
NSLog(@"打開數(shù)據(jù)庫(kù)失敗");
}
[db beginTransaction];
BOOL isRollBack = NO;
@try {
for (int i = 0; i<500; i++) {
NSString *nId = [NSString stringWithFormat:@"%d",i];
NSString *strName = [[NSString alloc] initWithFormat:@"student_%d",i];
NSString *sql = @"INSERT INTO student (name,age) VALUES (?,?)";
BOOL a = [db executeUpdate:sql,strName,nId];
if (!a) {
NSLog(@"插入失敗1");
}
}
}
@catch (NSException *exception) {
isRollBack = YES;
[db rollback];
}
@finally {
if (!isRollBack) {
[db commit];
}
}
[db close];
1.4.2 FMDB多線程使用事務(wù)
NSDate *date1 = [NSDate date];
// 創(chuàng)建表
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *dbPath = [docPath stringByAppendingPathComponent:@"student.sqlite"];
//多線程安全FMDatabaseQueue可以替代dataBase
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:dbPath];
//開啟事務(wù)
[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
if(![db open]){
return NSLog(@"事務(wù)打開失敗");
}
BOOL result = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS student (studentID integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL);"];
if (!result) {
NSLog(@"error when creating student table");
[db close];
}
for (int i = 0; i<500; i++) {
NSString *insertSql2= [NSString stringWithFormat:
@"INSERT INTO '%@' ('%@', '%@') VALUES (?, ?)",
@"student", @"name", @"age"];
NSString * name = [NSString stringWithFormat:@"靜靜 %d", i];
NSString * age = [NSString stringWithFormat:@"%d", 10+i];
BOOL result = [db executeUpdate:insertSql2, name, age];;
if ( !result ) {
//當(dāng)最后*rollback的值為YES的時(shí)候，事務(wù)回退旺嬉，如果最后*rollback為NO管行，事務(wù)提交
*rollback = YES;
return;
}
}
// [db close];
}];
NSDate *date2 = [NSDate date];
NSTimeInterval a = [date2 timeIntervalSince1970] - [date1 timeIntervalSince1970];
NSLog(@"FMDatabaseQueue使用事務(wù)插入500條數(shù)據(jù)用時(shí)%.3f秒",a);
1.5 加解密
使用
SQLCipher進(jìn)行加密，改之前的pod 'FMDB'為pod 'FMDB/SQLCipher'# FMDB with SQLCipher即可以用cocoapods來(lái)安裝支持SQLCipher加密數(shù)據(jù)庫(kù)的FMDB使用
-[FMDatabase setKey:]和-[FMDatabase setKeyWithData:]
來(lái)給數(shù)據(jù)庫(kù)設(shè)置密碼或者清除密碼邪媳，使用- [FMDatabase rekey:]和- [FMDatabase rekeyWithData:]輸入數(shù)據(jù)庫(kù)密碼以求驗(yàn)證用戶身份捐顷。
這兩類函數(shù)分別對(duì)sqlite3_key和sqlite3_rekey函數(shù)進(jìn)行了封裝。
使用方式與原來(lái)的方式一樣雨效，只需要數(shù)據(jù)庫(kù)open之后調(diào)用setKey設(shè)置一下秘鑰即可
下面摘了一段FMDatabase的open函數(shù)迅涮，在sqlite3_open成功后調(diào)用setKey方法設(shè)置秘鑰
- (BOOL)open {
if (_db) {
return YES;
}
int err = sqlite3_open([self sqlitePath], &_db );
if(err != SQLITE_OK) {
NSLog(@"error opening!: %d", err);
return NO;
} else {
//數(shù)據(jù)庫(kù)open后設(shè)置加密key
[self setKey:encryptKey_];
}
if (_maxBusyRetryTimeInterval > 0.0) {
// set the handler
[self setMaxBusyRetryTimeInterval:_maxBusyRetryTimeInterval];
}
return YES;
}
為了不修改FMDB的源代碼，我們可以繼承自FMDatabase類重寫需要setKey的幾個(gè)方法徽龟，這里我繼承FMDatabase定義了一個(gè)JJEncryptDatabase類叮姑，提供打開加密文件的功能
JJEncryptDatabase.h
#import "FMDatabase.h"
@interface JJEncryptDatabase : FMDatabase
+ (instancetype)databaseWithPath:(NSString*)aPath encryptKey:(NSString *)encryptKey;
- (instancetype)initWithPath:(NSString*)aPath encryptKey:(NSString *)encryptKey;
@end
JJEncryptDatabase.m
import "JJEncryptDatabase.h"
#import <sqlite3.h>
@interface JJEncryptDatabase (){
NSString *_encryptKey;
}
@end
@implementation JJEncryptDatabase
+ (instancetype)databaseWithPath:(NSString*)aPath encryptKey:(NSString *)encryptKey {
return [[[self class]alloc]initWithPath:aPath encryptKey:encryptKey];
}
- (instancetype)initWithPath:(NSString*)aPath encryptKey:(NSString *)encryptKey{
if (self == [self initWithPath:aPath]) {
_encryptKey = encryptKey;
}
return self;
}
#pragma mark 復(fù)寫父類方法
- (BOOL)open {
BOOL res = [super open];
if (res && _encryptKey) {
[self setKey:_encryptKey];
}
return res;
}
#if SQLITE_VERSION_NUMBER >= 3005000
- (BOOL)openWithFlags:(int)flags vfs:(NSString *)vfsName {
BOOL res = [super openWithFlags:flags vfs:vfsName];
if (res && _encryptKey) {
//數(shù)據(jù)庫(kù)open后設(shè)置加密key
[self setKey:_encryptKey];
}
return res;
}
#endif
@end
用法與FMDatabase一樣，只是需要傳入secretKey
加密使用
NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *file = [doc stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"%@",file);
BOOL res = [JJEncryptHelper encryptDatabase:file encryptKey:self.encryptKey];
NSString *msg = res ? @"encrypt success" : @"encrypt fail";
[[[UIAlertView alloc] initWithTitle:nil message:msg delegate:nil cancelButtonTitle:nil otherButtonTitles:@"ok", nil] show];
解密使用
NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *file = [doc stringByAppendingPathComponent:@"student.sqlite"];
NSLog(@"%@",file);
BOOL res = [JJEncryptHelper unEncryptDatabase:file encryptKey:@"123"];
NSString *msg = res ? @"decrypt success" : @"decrypt fail";
[[[UIAlertView alloc] initWithTitle:nil message:msg delegate:nil cancelButtonTitle:nil otherButtonTitles:@"ok", nil] show];
