FMDB
1、簡(jiǎn)述：
* FMDB是iOS平臺(tái)的SQLite數(shù)據(jù)庫(kù)框架，是對(duì)libsqlite3框架的封裝
* FMDB以O(shè)C的方式封裝了SQLite的C語言API
2、FMDB的優(yōu)點(diǎn)：
* 使用起來更加面向?qū)ο?，省去了很多麻煩、冗余的C語言代碼
* 對(duì)比蘋果自帶的Core Data框架，更加輕量級(jí)和靈活
* 提供了多線程安全的數(shù)據(jù)庫(kù)操作方法，有效地防止數(shù)據(jù)混亂
3、FMDB的github地址 傳送門
4、FMDB的三個(gè)核心類
FMDatabase —— 一個(gè)FMDatabase對(duì)象就代表一個(gè)單獨(dú)的SQLite數(shù)據(jù)庫(kù) 用來執(zhí)行SQL語句
FMResultSet —— 使用FMDatabase執(zhí)行查詢后的結(jié)果集
FMDatabaseQueue —— 用于在多線程中執(zhí)行多個(gè)查詢或更新，它是線程安全的
5、基本使用
1、下載FMDB文件的GitHub，并將FMDB文件夾添加到項(xiàng)目中（也可使用CocoaPods導(dǎo)入）—— pod'FMDB'
2、導(dǎo)入libsqlite3.0框架，導(dǎo)入頭文件FMDatabase.h
3、代碼實(shí)現(xiàn)，與SQLite使用步驟相似，創(chuàng)建數(shù)據(jù)庫(kù)路徑，獲得數(shù)據(jù)庫(kù)路徑，打開數(shù)據(jù)庫(kù)，然后對(duì)數(shù)據(jù)庫(kù)進(jìn)行增、刪、改、查操作，最后關(guān)閉數(shù)據(jù)庫(kù)。
實(shí)例Demo
本例是一個(gè)學(xué)生的數(shù)據(jù)庫(kù)表格student，每個(gè)學(xué)生都自己所選的課程class，具體信息如下圖Model：
1、具體操作界面
2、上代碼
2.1、創(chuàng)建 DataForFMDB.h 類，導(dǎo)入頭文件
#import"DataForFMDB.h"
#import <FMDB.h>
2.2、創(chuàng)建FMDB單例，以便全局共享
@interface DataForFMDB (){
FMDatabase *fmdb;
}
@end
@implementation DataForFMDB
static DataForFMDB *theData = nil;
+(instancetype)sharedDataBase{
@synchronized(self) {
if(!theData) {
theData = [[DataForFMDB alloc] init];
[theData initDataBase];
}
}
return theData;
}
-(void)initDataBase{
//獲得Documents目錄路徑
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
//文件路徑
NSString *filePath = [documentPath stringByAppendingPathComponent:@"student.db"];
//實(shí)例化FMDataBase對(duì)象
NSLog(@"---path:%@",filePath);
fmdb = [FMDatabase databaseWithPath:filePath];
if([fmdb open]) {
//初始化數(shù)據(jù)表
[self addStudentTable];
[self addClassTable];
[fmdb close];
}else{
NSLog(@"數(shù)據(jù)庫(kù)打開失敗---%@", fmdb.lastErrorMessage);
}
}
-(void)addStudentTable{
NSString*studentSQL =@"create table if not exists student (id integerPrimary Key Autoincrement, sId integer, sName text, sAge integer)";
BOOL studentSuccess = [fmdb executeUpdate:studentSQL];
if(!studentSuccess) {
NSLog(@"studentTable創(chuàng)建失敗---%@",fmdb.lastErrorMessage);
}
}
-(void)addClassTable{
NSString*classSQL =@"create table if not exists class (id integerPrimary Key Autoincrement,scId integer, cName text)";
BOOL classSuccess = [fmdbexecuteUpdate:classSQL];
if(!classSuccess) {
NSLog(@"classTable創(chuàng)建失敗---%@",fmdb.lastErrorMessage);
}
}
2.3、獲取student表全部?jī)?nèi)容
-(NSMutableArray*)getAllStudent{
[fmdb open];
NSMutableArray *array = [NSMutableArray new];
FMResultSet *result = [fmdb executeQuery:@"select * from student"];
while([result next]) {
StudentFMDBModel *student = [[StudentFMDBModel alloc] init];
student.sId = [[result stringForColumn:@"sId"] integerValue];
student.sName= [result stringForColumn:@"sName"];
student.sAge= [[result stringForColumn:@"sAge"] integerValue];
[array addObject:student];
}
[fmdb close];
return array;
}
//調(diào)用
self.dataArray= [[DataForFMDB sharedDataBase] getAllStudent];
2.4、student表添加內(nèi)容
-(void)addStudent:(StudentFMDBModel*)student{
[fmdb open];
NSString *SQL =@"insert into student(sId,sName,sAge) values(?,?,?)";
BOOL isAddSuccess = [fmdb executeUpdate:SQL,@(student.sId),student.sName,@(student.sAge)];
if(!isAddSuccess) {
NSLog(@"studentTable插入信息失敗--%@",fmdb.lastErrorMessage);
}
[fmdb close];
}
//調(diào)用
[[DataForFMDB sharedDataBase] addStudent:student];
2.5、student表刪除內(nèi)容
-(void)deleteStudent:(StudentFMDBModel*)student{
[fmdb open];
NSString *SQL =@"delete from student where sId = ?";
BOOL isDeleteSuccess = [fmdb executeUpdate:SQL,@(student.sId)];
if(!isDeleteSuccess) {
NSLog(@"studentTable刪除某一信息失敗--%@",fmdb.lastErrorMessage);
}
[fmdb close];
}
//調(diào)用
[[DataForFMDB sharedDataBase] deleteStudent:self.dataArray[indexPath.row]];
2.6、student表修改內(nèi)容
-(void)updateStudent:(StudentFMDBModel*)student{
[fmdb open];
NSString *SQL1 =@"update student set sName = ? where sId = ?";
NSString *SQL2 =@"update student set sAge = ? where sId = ?";
BOOL isSuccess1 = [fmdbexecuteUpdate: SQL1, student.sName,@(student.sId)];
BOOL isSuccess2 = [fmdbexecuteUpdate: SQL2,@(student.sAge),@(student.sId)];
if(!isSuccess1) {
NSLog(@"student.sName修改失敗--%@",fmdb.lastErrorMessage);
}
if(!isSuccess2) {
NSLog(@"student.sAge修改失敗--%@",fmdb.lastErrorMessage);
}
[fmdb close];
}
//調(diào)用
[[DataForFMDB sharedDataBase] updateStudent:student];
2.7、刪除student表
-(void)deleteAllStudent{
[fmdb open];
NSString *SQL =@"delete from student";
BOOL isSuccess = [fmdb executeUpdate:SQL];
if(!isSuccess) {
NSLog(@"studentTable全部刪除失敗--%@",fmdb.lastErrorMessage);
}
//student表刪除以后，對(duì)應(yīng)的class也要?jiǎng)h除
[self deleteAllClass];
[fmdb close];
}
//調(diào)用
[[DataForFMDB sharedDataBase] deleteAllStudent];
2.8、獲取某一student class表的全部課程
-(NSMutableArray*)getAllClassFromStudent:(StudentFMDBModel*)student{
[fmdb open];
NSMutableArray *array = [NSMutableArray new];
FMResultSet *result = [fmdb executeQuery:[NSString stringWithFormat:@"select * from class where scId = %ld", student.sId]];
while([result next]) {
StudentClassModel*class = [[StudentClassModel alloc] init];
class.cName= [result stringForColumn:@"cName"];
[array addObject:class];
}
[fmdb close];
return array;
}
//調(diào)用
self.dataArray= [[DataForFMDB sharedDataBase] getAllClassFromStudent: student];
2.9、給class表添加課程
-(void)addClass:(StudentClassModel*)clas toStudent:(StudentFMDBModel*)student{
[fmdb open];
//scId integer, cName text
NSString*SQL = [NSStringstringWithFormat:@"insert into class (scId, cName) values (%ld,?)", student.sId];
BOOL isSuccess = [fmdb executeUpdate:SQL, clas.cName];
if(!isSuccess) {
NSLog(@"classTable插入信息失敗--%@",fmdb.lastErrorMessage);
}
[fmdb close];
}
//調(diào)用
[[DataForFMDB sharedDataBase] addClass: class toStudent: student];
2.10、給class表刪除課程
-(void)deleteClass:(StudentClassModel*)clas toStudent:(StudentFMDBModel*)student{
[fmdb open];
NSString *SQL = [NSString stringWithFormat:@"delete from class where scId = %ld and cName = ?", student.sId];
BOOL isSuccess = [fmdb executeUpdate:SQL,clas.cName];
if(!isSuccess) {
NSLog(@"classTable刪除某一信息失敗--%@",fmdb.lastErrorMessage);
}
[fmdb close];
}
//調(diào)用
[[DataForFMDB sharedDataBase] deleteClass: self.dataArray[indexPath.row] toStudent: student];
2.11、刪除student下某一的全部class
-(void)deleteAllCarsFromStudent:(StudentFMDBModel*)student{
[fmdb open];
NSString *SQL = [NSString stringWithFormat:@"delete from class where scId = %ld", student.sId];
BOOL isSuccess = [fmdb executeUpdate:SQL];
if(!isSuccess) {
NSLog(@"student下某一的全部class刪除失敗--%@",fmdb.lastErrorMessage);
}
[fmdb close];
}
//調(diào)用
[[DataForFMDB sharedDataBase] deleteAllClassFromStudent: student];
2.12、//刪除class表
-(void)deleteAllClass{
NSString *SQL =@"delete from class";
BOOL isSuccess = [fmdb executeUpdate:SQL];
if(!isSuccess) {
NSLog(@"classt全部刪除失敗--%@",fmdb.lastErrorMessage);
}
}
2.13、由名字查找學(xué)生student信息
-(NSMutableArray*)seachAllInfoWith:(NSString*)str{
[fmdb open];
NSMutableArray*array = [NSMutableArray new];
//通過名字查詢學(xué)生信息
NSString *SQL = [NSString stringWithFormat:@"select * from student where sName = '%@' ", str]; // '%@' 可以查詢中文
FMResultSet *result = [fmdb executeQuery:SQL];
while([result next]) {
StudentFMDBModel *student = [[StudentFMDBModel alloc] init];
student.sId= [result intForColumn:@"sId"];
student.sName= [result stringForColumn:@"sName"];
[array addObject:student];
}
[fmdb close];
return array;
}
//調(diào)用
self.dataArray= [[DataForFMDB sharedDataBase] seachAllInfoWith: textField.text];
2.14、全部信息
數(shù)據(jù)代碼：
@property(nonatomic,strong)NSMutableArray*studentArray;//student數(shù)據(jù)源數(shù)組
@property(nonatomic,strong)NSMutableArray*allInfoArray;//student對(duì)應(yīng)class數(shù)據(jù)源數(shù)組
self.studentArray = [[DataForFMDB sharedDataBase] getAllStudent];
for(inti =0; i<self.studentArray.count; i++){
StudentFMDBModel *student =? self.studentArray[i];
NSMutableArray *array = [[DataForFMDB sharedDataBase] getAllClassFromStudent:student];
[self.allInfoArray addObject:array];
}
知識(shí)點(diǎn)總結(jié)
1、數(shù)據(jù)庫(kù)插入命令SQL ?insert into
//1.executeUpdate:不確定的參數(shù)用？來占位（后面參數(shù)必須是oc對(duì)象，“；”代表語句結(jié)束）
[fmdb executeUpdate:@“insert into student(sId,sName,sAge) values(?,?,?);” ,@(student.sId), student.sName, @(student.sAge)];? //int / integer 類型的要加 “@(xxx)”轉(zhuǎn)成NSNumber類型的
//2.executeUpdateWithForamat：不確定的參數(shù)用%@，%d等來占位 （參數(shù)為原始數(shù)據(jù)類型，執(zhí)行語句不區(qū)分大小寫
?[fmdb executeUpdateWithForamat:@“insert intot student (sId,sName,sAge)values(%ld，%@,%ld);”,student.sId, student.sName, student.sAge]; ? ?
//3.參數(shù)是數(shù)組的使用方式?
[fmdb executeUpdate:@“insert into student (sId,sName,sAge) values(?,?,?);” withArgumentsInArray:@[@(student.sId), student.sName, @(student.sAge)]];
2、數(shù)據(jù)庫(kù)刪除命令SQL ?delete
//1.不確定的參數(shù)用？來占位 （后面參數(shù)必須是oc對(duì)象,需要將int包裝成OC對(duì)象）
[fmdb executeUpdate:@“delete from student where sId = ?;”,@(student.sId)];
//2.不確定的參數(shù)用%@，%d等來占位
[fmdb executeUpdateWithFormat:@“delete from student where name = %@;”,student.sName];
3、數(shù)據(jù)庫(kù)修改命令SQL ?update
//修改學(xué)生的名字?
[fmdb executeUpdate:@“update student set sName = ? where sId = ?”,student.sName,@(student.sId)];
4、數(shù)據(jù)庫(kù)查詢命令SQL ?select ... from
select命令就是查詢，執(zhí)行查詢的方法是以-excuteQuery開頭的。執(zhí)行查詢時(shí)，如果成功返回FMResultSet對(duì)象，錯(cuò)誤返回nil。與執(zhí)行更新相當(dāng)，支持使用NSError參數(shù)。同時(shí)，你也可以使用-lastErrorCode和-lastErrorMessage獲知錯(cuò)誤信息。
FMResultSet獲取不同數(shù)據(jù)格式的方法：
intForColumn:
longForColumn:
longLongIntForColumn:
boolForColumn:
doubleForColumn:
stringForColumn:
dataForColumn:
dataNoCopyForColumn:
UTF8StringForColumnIndex:
objectForColumn:
5、數(shù)據(jù)庫(kù)銷毀命令SQL drop ...
//如果表格存在 則銷毀
?[fmdb executeUpadate:@“drop table if existst student;”];
6、使用FMDatabaseQueue類實(shí)現(xiàn)多線程操作
在多個(gè)線程中同時(shí)使用一個(gè)FMDatabase實(shí)例是不明智的?，F(xiàn)在你可以為每 個(gè)線程創(chuàng)建一個(gè)FMDatabase對(duì)象，不要讓多個(gè)線程分享同一個(gè)實(shí)例，他無法在多個(gè)線程中同事使用。否則程序會(huì)時(shí)不時(shí)崩潰或者報(bào)告異常。所以，不要初始化FMDatabase對(duì)象，然后在多個(gè)線程中使用。這時(shí)候，我們就需要使 用FMDatabaseQueue來創(chuàng)建隊(duì)列執(zhí)行事務(wù)。
//1.創(chuàng)建隊(duì)列
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:aPath];?
__block BOOL whoopsSomethingWrongHappened =true;
//2.把任務(wù)包裝到事務(wù)里
[queue inTransaction:^(FMDatabase *db, BOOL *rollback)? ? {? whoopsSomethingWrongHappened &=? [db executeUpdate:@“insert into myTable values (?)”,? ? [NSNumber numberWith:1]];
whoopsSomethingWrongHappened &= [db executeUpdata:@“insert into myTable values (?)”, [NSNumber numberWithInt:2]];
whoopsSomethingWrongHappened &= [db executeUpdata:@“insert into myTable values(?)”[NSNumber? numberWithInt:3]];
//如果有錯(cuò)誤 返回
if(!whoopsSomethingWrongHappened)? {? ?
?*rollback = YES;
return;
?}
}];
——————————————————
有什么問題歡迎大家提問喲，O(∩_∩)O~~
