最近公司项目做完了，没事干。就自己把数据库加进去了。用的是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;
}
以上就是基本的增删改查，下面有时间我还会把数据库迁移补上。
