最近公司項目做完了，沒事干。就自己把數(shù)據(jù)庫加進去了。用的是FMDB，而且有二次簡陋封裝。

FMDB的基本使用已經(jīng)有么很多文章，我這里主要是介紹一些坑。

• 建表

_instance.dbQueue = [FMDatabaseQueue databaseQueueWithPath:SQLFilePath];
    
[_instance.dbQueue inDatabase:^(FMDatabase *db) {
        
     if ([db executeStatements:@"CREATE TABLE IF NOT EXISTS USERDATA(number integer primary key not NULL, user_name text, net_asset text, earned_amount text, usable_amount text, open_newnet integer, to_callback_principal text, to_callback_earned text, open_fingerMark integer, gesture_code text)"]) {
          XZLog(@"create success");
      } else {
          XZLog(@"create failed");
     }
}];

FMDB不支持BOOL類型，所以如果要存的話還是integer類型。

• 根據(jù)某個值再數(shù)據(jù)庫查找

- (BOOL)hasDataFromKey:(NSString *)key
{
    __block BOOL flag = NO;
    [_instance.dbQueue inDatabase:^(FMDatabase *db) {
        
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT user_name FROM USERDATA WHERE user_name = %@", key];
        if ([set next]) {
            flag = YES;
        }
        [set close];
    }];
    
    return flag;
}

這里我有一個疑問，寫完這個去測試的時候提示我應(yīng)該去調(diào)用[set close]。如果我使用while的話則不需要。

• 插入新數(shù)據(jù)

- (void)insertData:(XZUserInfomationData *)data;
{
    [_instance.dbQueue inDatabase:^(FMDatabase *db) {
        NSString *sql = @"INSERT INTO USERDATA (user_name, net_asset, earned_amount, usable_amount, open_newnet, to_callback_principal, to_callback_earned, open_fingerMark, gesture_code) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        if ([db executeUpdate:sql, data.user_name, data.net_asset, data.earned_amount, data.usable_amount, [NSNumber numberWithBool:data.open_newnet], data.to_callback_principal, data.to_callback_earned, [NSNumber numberWithBool:data.open_fingerMark], data.gesture_code]) {
            XZLog(@"insert success");
        } else {
            XZLog(@"insert failed");
        }
    }];
}

就這里是巨大的坑，當你用字符串去保存數(shù)字時，如果數(shù)字開頭是0的時候，保存到數(shù)據(jù)庫的話會把0去掉。這是我往數(shù)據(jù)庫存手勢密碼發(fā)現(xiàn)的。例如手勢密碼是03678，存到數(shù)據(jù)庫就成了3678。解決辦法在下面更新數(shù)據(jù)的方法里。同時存bool類型需要轉(zhuǎn)成NSNumber類型。

• 更新數(shù)據(jù)

- (void)updateData:(XZUserInfomationData *)data
{
    if (!data.gesture_code.length) { // 這個是為了判斷可以忽略
       data.gesture_code = @"0";
    }
    
    [_instance.dbQueue inDatabase:^(FMDatabase *db) {
        
        NSString *sql = [NSString stringWithFormat:@"UPDATE USERDATA SET net_asset = %@, earned_amount = %@, open_newnet = %d, usable_amount = %@, to_callback_principal = %@, to_callback_earned = %@, open_fingerMark = %d, gesture_code = '%@' WHERE user_name = '%@'", data.net_asset, data.earned_amount, data.open_newnet, data.usable_amount, data.to_callback_principal, data.to_callback_earned, data.open_fingerMark, data.gesture_code, data.user_name];
        if ([db executeUpdate:sql]) {
            XZLog(@"update success");
        } else {
            XZLog(@"update failed");
        }
    }];
}

更新gesture_code = '%@'加單引號表明這個是一個字符串而不是一串數(shù)字，SQL會自己判斷如果不加則會被認為是數(shù)字就自動把開頭0去掉。

• 根據(jù)指定key得到數(shù)據(jù)庫的值

- (XZUserInfomationData *)dataFromKey:(NSString *)key
{
    XZUserInfomationData *data = [XZUserInfomationData sharedInstanceData];
    
    [_instance.dbQueue inDatabase:^(FMDatabase *db) {
        
        FMResultSet *set = [db executeQueryWithFormat:@"SELECT * FROM USERDATA WHERE user_name = %@", key];
        
        while ([set next]) {
            
            data.user_name = [set stringForColumn:@"user_name"];
            data.net_asset = [set stringForColumn:@"net_asset"];
            data.earned_amount = [set stringForColumn:@"earned_amount"];
            data.open_newnet = [set boolForColumn:@"open_newnet"];
            data.usable_amount = [set stringForColumn:@"usable_amount"];
            data.to_callback_principal = [set stringForColumn:@"to_callback_principal"];
            data.to_callback_earned = [set stringForColumn:@"to_callback_earned"];
            data.open_fingerMark = [set boolForColumn:@"open_fingerMark"];
            data.gesture_code = [set stringForColumn:@"gesture_code"];
        }
    }];
    
    return data;
}

以上就是基本的增刪改查，下面有時間我還會把數(shù)據(jù)庫遷移補上。