FMDB介紹

FMDB是iOS平臺(tái)的SQLite數(shù)據(jù)庫(kù)框架
FMDB以O(shè)C的方式封裝了SQLite的C語(yǔ)言API
使用起來(lái)更加面向?qū)ο?，省去了很多麻煩、冗余的C語(yǔ)言代碼
提供了多線程安全的數(shù)據(jù)庫(kù)操作方法，有效地防止數(shù)據(jù)混亂

FMDB基本使用

FMDB GitHub鏈接: https://github.com/ccgus/fmdb
1.導(dǎo)入FMDB文件
2.導(dǎo)入系統(tǒng)依賴庫(kù)sqlite3.0.tbd

創(chuàng)建數(shù)據(jù)庫(kù)并打開

/*
 1. 如果該路徑下已經(jīng)存在該數(shù)據(jù)庫(kù)，直接獲取該數(shù)據(jù)庫(kù);
 2. 如果不存在就創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù);
 3. 如果傳@""，會(huì)在臨時(shí)目錄創(chuàng)建一個(gè)空的數(shù)據(jù)庫(kù)，當(dāng)數(shù)據(jù)庫(kù)關(guān)閉時(shí)，數(shù)據(jù)庫(kù)文件也被刪除;
 4. 如果傳nil，會(huì)在內(nèi)存中臨時(shí)創(chuàng)建一個(gè)空的數(shù)據(jù)庫(kù)，當(dāng)數(shù)據(jù)庫(kù)關(guān)閉時(shí)，數(shù)據(jù)庫(kù)文件也被刪除;
*/
+ (FMDatabase *)databaseWithPath:(NSString *)filePath;

 // 1.獲得數(shù)據(jù)庫(kù)文件的路徑
    NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *filename = [doc stringByAppendingPathComponent:@"my.sqlite"];
    
 // 2.得到數(shù)據(jù)庫(kù)
    FMDatabase* db = [FMDatabase databaseWithPath:filename];

  // 3.打開數(shù)據(jù)庫(kù)
    if ([db open]) {
        NSLog(@"打開數(shù)據(jù)庫(kù)成功");
    }else {
        NSLog(@"打開數(shù)據(jù)庫(kù)失敗");
    }

執(zhí)行更新

在FMDB中，除查詢以外的所有操作，都稱為“更新”
create、drop、insert、update、delete等
使用executeUpdate:方法執(zhí)行更新

/* 執(zhí)行更新的SQL語(yǔ)句，字符串里面的"?"，依次用后面的參數(shù)替代，必須是對(duì)象，不能是int等基本類型 */
- (BOOL)executeUpdate:(NSString *)sql,... ;
/* 執(zhí)行更新的SQL語(yǔ)句，可以使用字符串的格式化進(jìn)行構(gòu)建SQL語(yǔ)句 */
- (BOOL)executeUpdateWithFormat:(NSString*)format,... ;
/* 執(zhí)行更新的SQL語(yǔ)句，字符串中有"?"，依次用arguments的元素替代 */
- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments;

/* 1. 直接使用完整的SQL更新語(yǔ)句 */
[database executeUpdate:@"insert into mytable(num,name,sex) values(0,'liuting','m');"];

NSString *sql = @"insert into mytable(num,name,sex) values(?,?,?);";
/* 2. 使用不完整的SQL更新語(yǔ)句，里面含有待定字符串"?"，需要后面的參數(shù)進(jìn)行替代 */
[database executeUpdate:sql,@0,@"liuting",@"m"];
/* 3. 使用不完整的SQL更新語(yǔ)句，里面含有待定字符串"?"，需要數(shù)組參數(shù)里面的參數(shù)進(jìn)行替代 */
[database executeUpdate:sql 
   withArgumentsInArray:@[@0,@"liuting",@"m"]];

/* 4. SQL語(yǔ)句字符串可以使用字符串格式化，這種我們應(yīng)該比較熟悉 */
[database executeUpdateWithFormat:@"insert into mytable(num,name,sex) values(%d,%@,%@);",0,@"liuting","m"];

執(zhí)行查詢

 - (FMResultSet *)executeQuery:(NSString*)sql, ...

- (FMResultSet *)executeQueryWithFormat:(NSString*)format, ...

 - (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)arguments

• 處理結(jié)果FMResultSet的方法

/* 獲取下一個(gè)記錄 */
- (BOOL)next;
/* 獲取記錄有多少列 */
- (int)columnCount;
/* 通過(guò)列名得到列序號(hào)，通過(guò)列序號(hào)得到列名 */
- (int)columnIndexForName:(NSString *)columnName;
- (NSString *)columnNameForIndex:(int)columnIdx;
/* 獲取存儲(chǔ)的整形值 */
- (int)intForColumn:(NSString *)columnName;
- (int)intForColumnIndex:(int)columnIdx;
/* 獲取存儲(chǔ)的長(zhǎng)整形值 */
- (long)longForColumn:(NSString *)columnName;
- (long)longForColumnIndex:(int)columnIdx;
/* 獲取存儲(chǔ)的布爾值 */
- (BOOL)boolForColumn:(NSString *)columnName;
- (BOOL)boolForColumnIndex:(int)columnIdx;
/* 獲取存儲(chǔ)的浮點(diǎn)值 */
- (double)doubleForColumn:(NSString *)columnName;
- (double)doubleForColumnIndex:(int)columnIdx;
/* 獲取存儲(chǔ)的字符串 */
- (NSString *)stringForColumn:(NSString *)columnName;
- (NSString *)stringForColumnIndex:(int)columnIdx;
/* 獲取存儲(chǔ)的日期數(shù)據(jù) */
- (NSDate *)dateForColumn:(NSString *)columnName;
- (NSDate *)dateForColumnIndex:(int)columnIdx;
/* 獲取存儲(chǔ)的二進(jìn)制數(shù)據(jù) */
- (NSData *)dataForColumn:(NSString *)columnName;
- (NSData *)dataForColumnIndex:(int)columnIdx;
/* 獲取存儲(chǔ)的UTF8格式的C語(yǔ)言字符串 */
- (const unsigned cahr *)UTF8StringForColumnName:(NSString *)columnName;
- (const unsigned cahr *)UTF8StringForColumnIndex:(int)columnIdx;
/* 獲取存儲(chǔ)的對(duì)象，只能是NSNumber、NSString、NSData、NSNull */
- (id)objectForColumnName:(NSString *)columnName;
- (id)objectForColumnIndex:(int)columnIdx;

• 如何使用

+ (NSArray *)shops
{// 得到結(jié)果集
    FMResultSet *set = [_db executeQuery:@"SELECT * FROM t_shop;"];
    
    // 不斷往下取數(shù)據(jù)
    NSMutableArray *shops = [NSMutableArray array];
    while (set.next) {
        // 獲得當(dāng)前所指向的數(shù)據(jù)
        HMShop *shop = [[HMShop alloc] init];
        shop.name = [set stringForColumn:@"name"];
        shop.price = [set doubleForColumn:@"price"];
        [shops addObject:shop];
    }
    return shops;
}

• 關(guān)閉數(shù)據(jù)庫(kù)

[_db close];

FMDabaseQueue的使用

FMDatabase這個(gè)類是線程不安全的，如果在多個(gè)線程中同時(shí)使用一個(gè)FMDatabase實(shí)例，會(huì)造成數(shù)據(jù)混亂等問(wèn)題
為了保證線程安全，F(xiàn)MDB提供方便快捷的FMDatabaseQueue類

創(chuàng)建FMDabaseQueue

NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"shops.sqlite"];
 FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:path];

使用FMDabaseQueue

[queue inDatabase:^(FMDatabase*db) {
    //插入記錄到表中
    NSString *sqlStr = @"CREATE TABLE IF NOT EXISTS t_shop (id integer PRIMARY KEY, name text NOT NULL, price real);"；
    BOOL result = [db executeUpdate:sqlStr];
    if (!result) {
        NSLog(@"error when insert into database table");
        [db close];
    }
}];

事務(wù)

事務(wù)，是指作為單個(gè)邏輯工作單元執(zhí)行的一系列操作，要么完整地執(zhí)行，要么完全地不執(zhí)行。

比如你要更新數(shù)據(jù)庫(kù)的大量數(shù)據(jù)，我們需要確保所有的數(shù)據(jù)更新成功才行，如果在更新期間出現(xiàn)錯(cuò)誤，就不好辦了。如果我們不使用事務(wù)，我們的更新操作直接對(duì)每個(gè)記錄生效，萬(wàn)一遇到更新錯(cuò)誤，已經(jīng)更新的數(shù)據(jù)怎么辦？難道我們要一個(gè)一個(gè)去找出來(lái)修改回來(lái)嗎？事務(wù)就是解決這種情況的，只有所有的數(shù)據(jù)更新正確才成功，如果有一條更新錯(cuò)誤，那么所有的數(shù)據(jù)都回到解放前，變?yōu)闆]有更新狀態(tài)。

來(lái)個(gè)demo更好理解吧

• FMDatabase使用事務(wù)

//事務(wù) 
-(void)startTransaction {
    // 開啟事務(wù)
    [self.database beginTransaction]; 
    BOOL isRollBack = NO; 
    @try { 
        for (int i = 0; i<100; i++) { 
            NSNumber *num = @(i+1); 
            NSString *name = [[NSString alloc] initWithFormat:@"student_%d",i]; 
            NSString *sql = @"insert into mytable(num,name) values(?,?);"; 
            BOOL result = [database executeUpdate:sql,num,name,sex]; 
            if ( !result ) { 
                NSLog(@"插入失敗！");
                return; 
            } 
        } 
    } 
    @catch (NSException *exception) { 
        isRollBack = YES; 
        // 事務(wù)回退
        [self.database rollback]; 
    } 
    @finally { 
        if (!isRollBack) { 
            //事務(wù)提交
            [self.database commit]; 
        } 
    } 
}

• FMDatabaseQueue使用事務(wù)

//多線程事務(wù) 
- (void)transactionByQueue { 
    //開啟事務(wù)
    [self.queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
        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 mytable(num,name,sex) values(?,?,?);"; 
            BOOL result = [db executeUpdate:sql,num,name,sex]; 
            if ( !result ) { 
                //當(dāng)最后*rollback的值為YES的時(shí)候，事務(wù)回退，如果最后*rollback為NO，事務(wù)提交
                *rollback = YES;
                 return; 
            } 
        }
     }]; 
}

demo展示

@interface ViewController ()

@property (nonatomic, strong) FMDatabase *db;

@end

@implementation ViewController
@synthesize db;

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
    
    // 1.獲得數(shù)據(jù)庫(kù)文件的路徑
    NSString *doc = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *filename = [doc stringByAppendingPathComponent:@"operDB.sqlite"];
    
    // 2.得到數(shù)據(jù)庫(kù)
    db = [FMDatabase databaseWithPath:filename];
}

- (IBAction)openDB:(id)sender {
    // 3.打開數(shù)據(jù)庫(kù)
    if ([db open]) {
        NSLog(@"打開operDB成功");
    }else {
        NSLog(@"打開operDB失敗");
    }
}

- (IBAction)createShopCarTable:(id)sender {
    // 4.創(chuàng)表
    if ([db open]) {
        BOOL result = [db executeUpdate:@"CREATE TABLE IF NOT EXISTS shopCar (dbid integer PRIMARY KEY AUTOINCREMENT, specId integer NOT NULL, shopNum integer NOT NULL default 1, shopInfo TEXT NOT NULL);"];
        if (result) {
            NSLog(@"成功創(chuàng)表");
        } else {
            NSLog(@"創(chuàng)表失敗");
        }
        [db close];
    }
}

- (IBAction)insertData:(id)sender {
    if ([db open]) {
        
        for (int i = 0; i < 5; i++) {
            NSDictionary *dict = @{@"shopId":@(i+1),@"shopName":@"歐陽(yáng)公仔",@"shopPrice":@(55.9 + i)};
            NSString *shopInfo = [dict mj_JSONString];
            NSString *insertSql1= [NSString stringWithFormat:@"INSERT INTO shopCar (specId, shopNum, shopInfo) VALUES (%d, %d, '%@')", i+2, i+3, shopInfo];
            BOOL res = [db executeUpdate:insertSql1];
            
            if (res) {
                NSLog(@"數(shù)據(jù)插入表成功");
            } else {
                NSLog(@"數(shù)據(jù)插入表失敗");
            }
        }
        
        [db close];
        
    }
}

- (IBAction)selectAllData:(id)sender {
    if ([db open]) {
        NSString * sql = @"SELECT * FROM shopCar";
        FMResultSet * rs = [db executeQuery:sql];
        while ([rs next]) {
            int dbId = [rs intForColumn:@"dbid"];
            int specId = [rs intForColumn:@"specId"];
            int shopNum = [rs intForColumn:@"shopNum"];
            NSString * shopInfo = [rs stringForColumn:@"shopInfo"];
            NSLog(@"dbId = %d, specId = %d, shopNum = %d  shopInfo = %@", dbId, specId, shopNum, shopInfo);
        }
        [db close];
    }
}

- (IBAction)selectDbidData:(id)sender {
    if ([db open]) {
        NSString * sql = @"SELECT * FROM shopCar WHERE dbid = '3'";
        FMResultSet * rs = [db executeQuery:sql];
        while ([rs next]) {
            int dbId = [rs intForColumn:@"dbid"];
            int specId = [rs intForColumn:@"specId"];
            int shopNum = [rs intForColumn:@"shopNum"];
            NSString * shopInfo = [rs stringForColumn:@"shopInfo"];
            NSLog(@"dbId = %d, specId = %d, shopNum = %d  shopInfo = %@", dbId, specId, shopNum, shopInfo);
        }
        [db close];
    }
}

- (IBAction)updataDbidData:(id)sender {
    if ([db open]) {
        NSString *updateSql = [NSString stringWithFormat:@"update shopCar set shopNum = '88' where dbid ='3'"];
        BOOL res = [db executeUpdate:updateSql];
        if (res) {
            NSLog(@"修改數(shù)據(jù)成功");
        } else {
            NSLog(@"修改數(shù)據(jù)失敗");
        }
        [db close];
        
    }
}

- (IBAction)deleteDbidData:(id)sender {
    if ([db open]) {
        NSString *updateSql = [NSString stringWithFormat:@"DELETE FROM shopCar WHERE dbid = '3'"];
        BOOL res = [db executeUpdate:updateSql];
        if (res) {
            NSLog(@"刪除指定數(shù)據(jù)成功");
        } else {
            NSLog(@"刪除指定數(shù)據(jù)失敗");
        }
        [db close];
        
    }
}

- (IBAction)deleteAllData:(id)sender {
    if ([db open]) {
        //清空數(shù)據(jù)表并將自增字段清零
        NSString *deleteSql = @"DELETE FROM shopCar";
        [db executeUpdate:@"UPDATE sqlite_sequence set seq = 0 where name = 'shopCar'"];
        BOOL res = [db executeUpdate:deleteSql];
        if (res) {
            NSLog(@"清空shopCar表數(shù)據(jù)成功");
        } else {
            NSLog(@"清空shopCar表數(shù)據(jù)失敗");
        }
        
        [db close];
    }
}

- (IBAction)deleteShopCarTable:(id)sender {
    
    if ([db open]) {
        
        NSString *deleteSql = @"DROP TABLE shopCar";
        BOOL res = [db executeUpdate:deleteSql];
        
        if (res) {
            NSLog(@"刪除shopCar表成功");
        } else {
            NSLog(@"刪除shopCar表失敗");
        }
        [db close];
    }
}


- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

@end