本文主要從以下幾個方面介紹FMDB,保存,查詢,條件查詢,更新,條件刪除來介紹
本文演示代碼下載地址
本文生成的數(shù)據(jù)表使用Navicat打開
1.簡介
FMDB是iOS平臺的SQLite數(shù)據(jù)庫框架割疾,它是以O(shè)C的方式封裝了SQLite的C語言API冶共,它相對于cocoa自帶的C語言框架有如下的優(yōu)點:
使用起來更加面向?qū)ο螅∪チ撕芏嗦闊┥婺佟⑷哂嗟腃語言代碼
對比蘋果自帶的Core Data框架舌涨,更加輕量級和靈活
提供了多線程安全的數(shù)據(jù)庫操作方法攻礼,有效地防止數(shù)據(jù)混亂
2.核心類
FMDB有三個主要的類:
FMDatabase
一個FMDatabase對象就代表一個單獨的SQLite數(shù)據(jù)庫,用來執(zhí)行SQL語句
FMResultSet
使用FMDatabase執(zhí)行查詢后的結(jié)果集
FMDatabaseQueue
用于在多線程中執(zhí)行多個查詢或更新届慈,它是線程安全的
3.打開數(shù)據(jù)庫和c語言框架一樣族壳,F(xiàn)MDB通過指定SQLite數(shù)據(jù)庫文件路徑來創(chuàng)建FMDatabase對象憔辫,但FMDB更加容易理解,使用起來更容易仿荆,使用之前一樣需要導(dǎo)入sqlite3.dylib贰您。打開數(shù)據(jù)庫,創(chuàng)建表(也叫更新)的方法如下:
//獲取Document文件夾下的數(shù)據(jù)庫文件,沒有則創(chuàng)建
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *dbPath = [docPath stringByAppendingPathComponent:@"user.db"];
NSLog(@"dbPath = %@",dbPath);
//獲取數(shù)據(jù)庫并打開
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
if (![dataBase open]) {
NSLog(@"打開數(shù)據(jù)庫失敗");
return ;
}
//創(chuàng)建表(FMDB中只有update和query操作拢操,除了查詢其他都是update操作)
[dataBase executeUpdate:@"create table if not exists user(name text,gender text,age integer) "];
插入數(shù)據(jù)的操作:
//常用方法有以下3種:
/* 執(zhí)行更新的SQL語句锦亦,字符串里面的"?",依次用后面的參數(shù)替代令境,必須是對象杠园,不能是int等基本類型 */
- (BOOL)executeUpdate:(NSString *)sql,... ;
/* 執(zhí)行更新的SQL語句,可以使用字符串的格式化進行構(gòu)建SQL語句 */
- (BOOL)executeUpdateWithFormat:(NSString*)format,... ;
/* 執(zhí)行更新的SQL語句舔庶,字符串中有"?"抛蚁,依次用arguments的元素替代 */
- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments;
/* 1. 直接使用完整的SQL更新語句 */
[database executeUpdate:@"insert into mytable(num,name,sex) values(0,'liuting','m');"];
NSString *sql = @"insert into mytable(num,name,sex) values(?,?,?);";
/* 2. 使用不完整的SQL更新語句,里面含有待定字符串"?"惕橙,需要后面的參數(shù)進行替代 */
[database executeUpdate:sql,@0,@"liuting",@"m"];
/* 3. 使用不完整的SQL更新語句瞧甩,里面含有待定字符串"?",需要數(shù)組參數(shù)里面的參數(shù)進行替代 */
[database executeUpdate:sql
withArgumentsInArray:@[@0,@"liuting",@"m"]];
/* 4. SQL語句字符串可以使用字符串格式化弥鹦,這種我們應(yīng)該比較熟悉 */
[database executeUpdateWithFormat:@"insert into mytable(num,name,sex) values(%d,%@,%@);",0,@"liuting","m"];
本demo采用第二種方法
//常用方法有以下3種:
// - (BOOL)executeUpdate:(NSString*)sql, ...
// - (BOOL)executeUpdateWithFormat:(NSString*)format, ...
// - (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments
//插入數(shù)據(jù)
BOOL inser = [dataBase executeUpdate:@"insert into user values(?,?,?)",_nameTextField.text,_sexTextField.text,_ageTextField.text];
if (inser) {
UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"提示" message:@"信息保存成功" delegate:self cancelButtonTitle:nil otherButtonTitles:@"確定", nil];
[alert show];
}
[dataBase close];
5.查詢
查詢方法也有3種肚逸,使用起來相當簡單:
// 全部查詢
- (FMResultSet *)executeQuery:(NSString*)sql, ...
// 條件查詢
- (FMResultSet *)executeQueryWithFormat:(NSString*)format, ...
- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)arguments
// 演示如下:
//1.執(zhí)行查詢
FMResultSet *result = [database executeQuery:@"SELECT * FROM t_person"];
//2.遍歷結(jié)果集
while ([result next]) {
NSString *name = [result stringForColumn:@"name"];
int age = [result intForColumn:@"age"];
}
/*
FMResultSet獲取不同數(shù)據(jù)格式的方法
/* 獲取下一個記錄 */
- (BOOL)next;
/* 獲取記錄有多少列 */
- (int)columnCount;
/* 通過列名得到列序號,通過列序號得到列名 */
- (int)columnIndexForName:(NSString *)columnName;
- (NSString *)columnNameForIndex:(int)columnIdx;
/* 獲取存儲的整形值 */
- (int)intForColumn:(NSString *)columnName;
- (int)intForColumnIndex:(int)columnIdx;
/* 獲取存儲的長整形值 */
- (long)longForColumn:(NSString *)columnName;
- (long)longForColumnIndex:(int)columnIdx;
/* 獲取存儲的布爾值 */
- (BOOL)boolForColumn:(NSString *)columnName;
- (BOOL)boolForColumnIndex:(int)columnIdx;
/* 獲取存儲的浮點值 */
- (double)doubleForColumn:(NSString *)columnName;
- (double)doubleForColumnIndex:(int)columnIdx;
/* 獲取存儲的字符串 */
- (NSString *)stringForColumn:(NSString *)columnName;
- (NSString *)stringForColumnIndex:(int)columnIdx;
/* 獲取存儲的日期數(shù)據(jù) */
- (NSDate *)dateForColumn:(NSString *)columnName;
- (NSDate *)dateForColumnIndex:(int)columnIdx;
/* 獲取存儲的二進制數(shù)據(jù) */
- (NSData *)dataForColumn:(NSString *)columnName;
- (NSData *)dataForColumnIndex:(int)columnIdx;
/* 獲取存儲的UTF8格式的C語言字符串 */
- (const unsigned cahr *)UTF8StringForColumnName:(NSString *)columnName;
- (const unsigned cahr *)UTF8StringForColumnIndex:(int)columnIdx;
/* 獲取存儲的對象彬坏,只能是NSNumber朦促、NSString、NSData栓始、NSNull */
- (id)objectForColumnName:(NSString *)columnName;
- (id)objectForColumnIndex:(int)columnIdx;
*/
本文demo中代碼演示:
//查詢?nèi)?- (IBAction)query:(id)sender {
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *dbPath = [docPath stringByAppendingPathComponent:@"user.db"];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
if (![dataBase open]) {
NSLog(@"打開數(shù)據(jù)庫失敗");
return ;
}
FMResultSet *resultSet = [dataBase executeQuery:@"select * from user"];
while ([resultSet next]) {
NSString *name = [resultSet stringForColumn:@"name"];
NSString *genter = [resultSet stringForColumn:@"gender"];
int age = [resultSet intForColumn:@"age"];
NSLog(@"Name:%@,Gender:%@,Age:%d",name,genter,age);
}
[dataBase close];
}
//條件查詢
- (IBAction)queryByCondition:(id)sender {
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *dbPath = [docPath stringByAppendingPathComponent:@"user.db"];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
if (![dataBase open]) {
return ;
}
// FMResultSet *resultSet = [dataBase executeQuery:@"select *from user where name = ?",@"ZY"];
FMResultSet *resultSet = [dataBase executeQueryWithFormat:@"select * from user where name = %@",@"zy"];
while ([resultSet next]) {
NSString *name = [resultSet stringForColumnIndex:0];
NSString *gender = [resultSet stringForColumn:@"gender"];
int age = [resultSet intForColumn:@"age"];
NSLog(@"Name:%@,Gender:%@,Age:%d",name,gender,age);
}
[dataBase close];
}
6 :條件刪除的方法
- (IBAction)deleteByCondition:(id)sender
{
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *dbPath = [docPath stringByAppendingPathComponent:@"user.db"];
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
if (![dataBase open]) {
return ;
}
BOOL delete = [dataBase executeUpdateWithFormat:@"delete from user where name = %@",@"zy"];
if (delete) {
UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"提示" message:@"信息刪除成功" delegate:self cancelButtonTitle:nil otherButtonTitles:@"確定", nil];
[alert show];
}
[dataBase close];
}
7.線程安全
產(chǎn)考使用FMDB事務(wù)批量更新數(shù)據(jù)庫速度問題里面的代碼進行使用
在多個線程中同時使用一個FMDatabase實例是不明智的务冕。不要讓多個線程分享同一個FMDatabase實例,它無法在多個線程中同時使用混滔。 如果在多個線程中同時使用一個FMDatabase實例洒疚,會造成數(shù)據(jù)混亂等問題。所以坯屿,請使用 FMDatabaseQueue油湖,它是線程安全的。以下是使用方法:
1. 創(chuàng)建
NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES).firstObject;
NSString *filePath = [path stringByAppendingPathComponent:@"FMDB.db"];
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:path];
2. 操作數(shù)據(jù)庫
[queue inDatabase:^(FMDatabase*db) {
//FMDatabase數(shù)據(jù)庫操作
}];
**3.本文的使用實例
- (IBAction)save:(id)sender {
//獲取Document文件夾下的數(shù)據(jù)庫文件领跛,沒有則創(chuàng)建
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *dbPath = [docPath stringByAppendingPathComponent:@"user.db"];
NSLog(@"dbPath = %@",dbPath);
//獲取數(shù)據(jù)庫并打開
// FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
//多線程安全FMDatabaseQueue可以替代dataBase
FMDatabaseQueue *dataBasequeue = [FMDatabaseQueue databaseQueueWithPath:dbPath];
[dataBasequeue inDatabase:^(FMDatabase *db) {
if (![db open]) {
NSLog(@"打開數(shù)據(jù)庫失敗");
return ;
}
//創(chuàng)建表(FMDB中只有update和query操作乏德,除了查詢其他都是update操作)
[db executeUpdate:@"create table if not exists user(name text,gender text,age integer) "];
//常用方法有以下3種:
// - (BOOL)executeUpdate:(NSString*)sql, ...
// - (BOOL)executeUpdateWithFormat:(NSString*)format, ...
// - (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments
//插入數(shù)據(jù)
BOOL inser = [db executeUpdate:@"insert into user values(?,?,?)",_nameTextField.text,_sexTextField.text,_ageTextField.text];
if (inser) {
UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"提示" message:@"信息保存成功" delegate:self cancelButtonTitle:nil otherButtonTitles:@"確定", nil];
[alert show];
}
[db close];
}];
}
7:而且可以輕松地把簡單任務(wù)包裝到事務(wù)里:
之所以將事務(wù)放到FMDB中去說并不是因為只有FMDB才支持事務(wù),而是因為FMDB將其封裝成了幾個方法來調(diào)用吠昭,不用自己寫對應(yīng)的SQL而已,假如你要對數(shù)據(jù)庫中的Stutent表插入新數(shù)據(jù)喊括,那么該事務(wù)的具體過程是:開始新事物->插入數(shù)據(jù)->提交事務(wù),那么當我們要往該表內(nèi)插入500條數(shù)據(jù)矢棚,如果按常規(guī)操作處理就要執(zhí)行500次“開始新事物->插入數(shù)據(jù)->提交事務(wù)”的過程郑什。
好吧,今天的重點來了,舉個例子:假如北京的一家A工廠接了上海一家B公司的500件產(chǎn)品的訂單蒲肋,思考一下:A工廠是生產(chǎn)完一件立即就送到B公司還是將500件產(chǎn)品全部生產(chǎn)完成后再送往B公司蘑拯?答案肯定是后者,因為前者浪費了大量的時間兜粘、人力物力花費在往返于北京和上海之間申窘。同樣這個道理也能用在我們的數(shù)據(jù)庫操作上,下面是我自己對使用事務(wù)和不使用事務(wù)的兩種測試:
SQLite進行事務(wù)的SQL語句:
只要在執(zhí)行SQL語句前加上以下的SQL語句孔轴,就可以使用事務(wù)功能了:
開啟事務(wù)的SQL語句剃法,"begin transaction;"
進行提交的SQL語句,"commit transaction;"
進行回滾的SQL語句路鹰,"rollback transaction;"
一: FMDatabase使用事務(wù)的方法:
-(void)transaction {
NSDate *date1 = [NSDate date];
// 創(chuàng)建表
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *dbPath = [docPath stringByAppendingPathComponent:@"mytable1.db"];
NSLog(@"dbPath = %@",dbPath);
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
// 注意這里的判斷一步都不能少,特別是這里open的判斷
if (![dataBase open]) {
NSLog(@"打開數(shù)據(jù)庫失敗");
return ;
}
NSString *sqlStr = @"create table if not exists mytable1(num integer,name varchar(7),sex char(1),primary key(num));";
BOOL res = [dataBase executeUpdate:sqlStr];
if (!res) {
NSLog(@"error when creating mytable1");
[dataBase close];
}
// 開啟事務(wù)
[dataBase beginTransaction];
BOOL isRollBack = NO;
@try {
for (int i = 0; i<500; i++) {
NSNumber *num = @(i+1);
NSString *name = [[NSString alloc] initWithFormat:@"student_%d",i];
NSString *sex = (i%2==0)?@"f":@"m";
NSString *sql = @"insert into mytable1(num,name,sex) values(?,?,?);";
BOOL result = [dataBase executeUpdate:sql,num,name,sex];
if ( !result ) {
NSLog(@"插入失敶蕖!");
return;
}
}
}
@catch (NSException *exception) {
isRollBack = YES;
// 事務(wù)回退
[dataBase rollback];
}
@finally {
if (!isRollBack) {
//事務(wù)提交
[dataBase commit];
}
}
[dataBase close];
NSDate *date2 = [NSDate date];
NSTimeInterval a = [date2 timeIntervalSince1970] - [date1 timeIntervalSince1970];
NSLog(@"FMDatabase使用事務(wù)插入500條數(shù)據(jù)用時%.3f秒",a);
}
二: FMDatabase不使用事務(wù)的方法:
//二: FMDatabase不使用事務(wù)的方法:
-(void)noTransaction {
NSDate *date1 = [NSDate date];
// 創(chuàng)建表
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *dbPath = [docPath stringByAppendingPathComponent:@"mytable3.db"];
NSLog(@"dbPath = %@",dbPath);
FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];
// 注意這里的判斷一步都不能少,特別是這里open的判斷
if (![dataBase open]) {
NSLog(@"打開數(shù)據(jù)庫失敗");
return ;
}
NSString *sqlStr = @"create table if not exists mytable3(num integer,name varchar(7),sex char(1),primary key(num));";
BOOL res = [dataBase executeUpdate:sqlStr];
if (!res) {
NSLog(@"error when creating mytable1");
[dataBase close];
}
for (int i = 0; i<500; i++) {
NSNumber *num = @(i+1);
NSString *name = [[NSString alloc] initWithFormat:@"student_%d",i];
NSString *sex = (i%2==0)?@"f":@"m";
NSString *sql = @"insert into mytable3(num,name,sex) values(?,?,?);";
BOOL result = [dataBase executeUpdate:sql,num,name,sex];
if ( !result ) {
NSLog(@"插入失斀恩脂!");
return;
}
}
[dataBase close];
NSDate *date2 = [NSDate date];
NSTimeInterval a = [date2 timeIntervalSince1970] - [date1 timeIntervalSince1970];
NSLog(@"FMDatabase不使用事務(wù)插入500條數(shù)據(jù)用時%.3f秒",a);
}
是否使用事務(wù)的比較結(jié)果如下:
2017-01-18 00:28:57.455 Location[5319:300127] FMDatabase使用事務(wù)插入500條數(shù)據(jù)用時0.018秒
2017-01-18 00:28:58.509 Location[5319:300127] FMDatabase不使用事務(wù)插入500條數(shù)據(jù)用時1.054秒
三: FMDatabaseQueue使用事務(wù)的方法:
//多線程事務(wù)
- (void)transactionByQueue {
NSDate *date1 = [NSDate date];
// 創(chuàng)建表
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
NSString *dbPath = [docPath stringByAppendingPathComponent:@"mytable2.db"];
//多線程安全FMDatabaseQueue可以替代dataBase
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:dbPath];
//開啟事務(wù)
[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
if(![db open]){
return NSLog(@"事務(wù)打開失敗");
}
NSString *sqlStr = @"create table mytable2(num integer,name varchar(7),sex char(1),primary key(num));";
BOOL res = [db executeUpdate:sqlStr];
if (!res) {
NSLog(@"error when creating mytable2 table");
[db close];
}
for (int i = 0; i<500; i++) {
NSNumber *num = @(i+1);
NSString *name = [[NSString alloc] initWithFormat:@"student_%d",i];
NSString *sex = (i%2==0)?@"f":@"m";
NSString *sql = @"insert into mytable2(num,name,sex) values(?,?,?);";
BOOL result = [db executeUpdate:sql,num,name,sex];
if ( !result ) {
//當最后*rollback的值為YES的時候,事務(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ù)用時%.3f秒",a);
}