最近公司項目做完了兔魂，沒事干烤芦。就自己把數(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ù)庫遷移補上随闽。
