FMDB基本使用和SQL语句其中的坑

最近公司项目做完了，没事干。就自己把数据库加进去了。用的是FMDB，而且有二次简陋封装。

FMDB的基本使用已经有么很多文章，我这里主要是介绍一些坑。

• 建表

_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类型。

• 根据某个值再数据库查找

- (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;
}

这里我有一个疑问，写完这个去测试的时候提示我应该去调用[set close]。如果我使用while的话则不需要。

• 插入新数据

- (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");
        }
    }];
}

就这里是巨大的坑，当你用字符串去保存数字时，如果数字开头是0的时候，保存到数据库的话会把0去掉。这是我往数据库存手势密码发现的。例如手势密码是03678，存到数据库就成了3678。解决办法在下面更新数据的方法里。同时存bool类型需要转成NSNumber类型。

• 更新数据

- (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 = '%@'加单引号表明这个是一个字符串而不是一串数字，SQL会自己判断如果不加则会被认为是数字就自动把开头0去掉。

• 根据指定key得到数据库的值

- (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;
}

以上就是基本的增删改查，下面有时间我还会把数据库迁移补上。