解決辦法：
因為賬單用FMDB保存到了數(shù)據(jù)庫，所以要在數(shù)據(jù)庫的表中单寂，添加一個字段：區(qū)分不同的用戶——》那就是用戶名了(手機號)
如果一個手機可以在多個設(shè)備登錄啄刹，消息的狀態(tài)，應(yīng)該用"手機號+設(shè)備ID"作為唯一標示凄贩。
一.創(chuàng)建表
創(chuàng)建表的sql語句：
"create table if not exists bill(loginName varchar(2048),orderId varchar(2048), tradeTime varchar(2048), amount varchar(256), status varchar(256), bizDesc varchar(2048),partyName varchar(2048),cardNo varchar(256),bizCode varchar(256))"
#pragma mark---來創(chuàng)建表
- (void)createTable{ NSString *path = [NSHomeDirectory() stringByAppendingString:@"/Library/Caches/Container.db"]; FMDatabase *database = [FMDatabase databaseWithPath:path]; if ([database open]) { NSString *createBillTable = @"create table if not exists bill(loginName varchar(2048),orderId varchar(2048), tradeTime varchar(2048), amount varchar(256), status varchar(256), bizDesc varchar(2048),partyName varchar(2048),cardNo varchar(256),bizCode varchar(256))"; if (![database executeUpdate:createBillTable]) { NSLog(@"create error %@", database.lastErrorMessage); } } [database close]; }
二.插入一條數(shù)據(jù)
插入一條數(shù)據(jù)的sql語句："insert into bill (loginName,orderId,tradeTime,amount,status,bizDesc,partyName,cardNo,bizCode) values(?,?,?,?,?,?,?,?,?)"
SP_LOGIN_TOKEN.loginName登陸的時候，已保存袱讹，直接用
- (BOOL)insertTrip:(NSDictionary *)billDic { NSString *path = [NSHomeDirectory() stringByAppendingString:@"/Library/Caches/Container.db"]; FMDatabase *database = [FMDatabase databaseWithPath:path]; BOOL result = FALSE; NSString *insertSql =@"insert into bill (loginName,orderId,tradeTime,amount,status,bizDesc,partyName,cardNo,bizCode) values(?,?,?,?,?,?,?,?,?)"; if ([database open]) { result = [database executeUpdate:insertSql, SP_LOGIN_TOKEN.loginName, [billDic objectForKey:@"orderId"], [billDic objectForKey:@"tradeTime"], [billDic objectForKey:@"amount"], [billDic objectForKey:@"status"], [billDic objectForKey:@"bizDesc"], [billDic objectForKey:@"partyName"], [billDic objectForKey:@"cardNo"], [billDic objectForKey:@"bizCode"]]; [database close];
if (result == FALSE) { NSLog(@"insertError:%@",database.lastErrorMessage); } } return result; }
三.批量插入數(shù)據(jù)
（循環(huán)）插入一條數(shù)據(jù)的sql語句："insert into bill (loginName,orderId,tradeTime,amount,status,bizDesc,partyName,cardNo,bizCode) values(?,?,?,?,?,?,?,?,?)"
-(BOOL)insertDateTrip:(NSArray *)billArr{ BOOL result = FALSE; NSString *path = [NSHomeDirectory() stringByAppendingString:@"/Library/Caches/Container.db"]; FMDatabase *database = [FMDatabase databaseWithPath:path]; if ([database open]) { NSInteger count = [billArr count]; BOOL isRollBack = NO; @try { //可能出現(xiàn)問題的代碼寫在此處 //手動開啟一個事務(wù)(對sqlite3 開啟一個事務(wù)) [database beginTransaction]; for (int i = 0; i < count; i++) { NSDictionary *dicSubApp = [billArr objectAtIndex:i]; NSString *insertSql =@"insert into bill (loginName,orderId,tradeTime,amount,status,bizDesc,partyName,cardNo,bizCode) values(?,?,?,?,?,?,?,?,?)"; NSLog(@"insertSql====%@",insertSql); result = [database executeUpdate:insertSql, SP_LOGIN_TOKEN.loginName, [dicSubApp objectForKey:@"orderId"], [dicSubApp objectForKey:@"tradeTime"], [dicSubApp objectForKey:@"amount"], [dicSubApp objectForKey:@"status"], [dicSubApp objectForKey:@"bizDesc"], [dicSubApp objectForKey:@"partyName"], [dicSubApp objectForKey:@"cardNo"] [dicSubApp objectForKey:@"bizCode"] ]; if (!result) { NSLog(@"insertError:%@",database.lastErrorMessage); break; } } } @catch (NSException *exception) { //一旦try中的代碼出現(xiàn)了異常疲扎，此處能捕獲到異常 NSLog(@"error:%@",exception.reason); isRollBack = YES; //對數(shù)據(jù)庫執(zhí)行事務(wù)的回滾操作，回到最初的狀態(tài) [database rollback]; } @finally { //不管出不出現(xiàn)異常捷雕，代碼都可以執(zhí)行到此處 if (isRollBack ==NO) { //提交事務(wù) [database commit]; } } [database close]; } return result; }
四.查詢一條數(shù)據(jù)
查詢一條數(shù)據(jù) sql語句：select * from bill where loginName = '%@' and orderId = '%@'
- (NSDictionary*)isTripExist:(id)orderId{ NSString *path = [NSHomeDirectory() stringByAppendingString:@"/Library/Caches/Container.db"]; FMDatabase *database = [FMDatabase databaseWithPath:path]; if([database open]){ NSDictionary *dic; NSString *sql = [NSString stringWithFormat:@"select * from bill where loginName = '%@' and orderId = '%@'",SP_LOGIN_TOKEN.loginName,orderId];// FMResultSet *set = [database executeQuery:sql]; while ([set next]) { dic = @{@"loginName":SP_LOGIN_TOKEN.loginName, @"orderId":[set stringForColumn:@"orderId"], @"tradeTime":[set stringForColumn:@"tradeTime"], @"amount":[set stringForColumn:@"amount"], @"status":[set stringForColumn:@"status"], @"bizDesc":[set stringForColumn:@"bizDesc"], @"partyName":[set stringForColumn:@"partyName"], @"cardNo":[set stringForColumn:@"cardNo"], @"bizCode":[set stringForColumn:@"bizCode"] }; } [database close]; return dic; }else{ NSLog(@"could not dababase!"); return nil; } }
五.批量查詢數(shù)據(jù)
多條件查詢 sql語句：select * from bill where loginName = '%@'
-(NSArray *)querSelectSql{ NSString *path = [NSHomeDirectory() stringByAppendingString:@"/Library/Caches/Container.db"]; FMDatabase *database = [FMDatabase databaseWithPath:path]; if ([database open]){ [database setShouldCacheStatements:YES]; NSMutableArray *FinalArray=[NSMutableArray arrayWithCapacity:0]; FMResultSet *set = nil; NSString *sql = [NSString stringWithFormat:@"select * from bill where loginName = '%@'",SP_LOGIN_TOKEN.loginName]; set=[database executeQuery:sql]; NSLog(@"%@",[database lastErrorMessage]); while ([set next]){ NSDictionary *dic = @{@"orderId":[set stringForColumn:@"orderId"], @"tradeTime":[set stringForColumn:@"tradeTime"], @"amount":[set stringForColumn:@"amount"], @"status":[set stringForColumn:@"status"], @"bizDesc":[set stringForColumn:@"bizDesc"], @"partyName":[set stringForColumn:@"partyName"], @"cardNo":[set stringForColumn:@"cardNo"], @"bizCode":[set stringForColumn:@"bizCode"] }; [FinalArray addObject:dic]; } [database close]; return FinalArray; } else{ NSLog(@"could not open dababase!”); return nil; } }
六.批量更新數(shù)據(jù)
（循環(huán)）一條更新 sql語句：update bill where loginName ='%@' set %@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@‘(錯誤)
應(yīng)該是：（loginName必須是唯一標示.用orderId或tradeTime）
update bill set %@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@‘ where loginName ='%@‘
-(BOOL)updateDataWithBill:(NSArray *)billArray{ BOOL result = FALSE; NSString *path = [NSHomeDirectory() stringByAppendingString:@"/Library/Caches/Container.db"]; FMDatabase *database = [FMDatabase databaseWithPath:path]; if ([database open]) { NSInteger count = [billArray count]; for (int i = 0; i < count; i++) { NSDictionary *dicSubApp = [billArray objectAtIndex:i]; NSString *sql = [NSString stringWithFormat:@"update bill where loginName ='%@' set %@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@'", SP_LOGIN_TOKEN.loginName, @"orderId",[dicSubApp objectForKey:@"orderId"], @"tradeTime",[dicSubApp objectForKey:@"tradeTime"], @"amount",[dicSubApp objectForKey:@"amount"], @"status",[dicSubApp objectForKey:@"status"], @"bizDesc",[dicSubApp objectForKey:@"bizDesc"], @"partyName",[dicSubApp objectForKey:@"partyName"], @"cardNo",[dicSubApp objectForKey:@"cardNo"], @"bizCode",[dicSubApp objectForKey:@"bizCode"]]; result = [database executeUpdate:sql]; if (result == FALSE) { NSLog(@"SubApp 數(shù)據(jù)批量更新失敗"); break; } } [database commit]; [database close]; } return result; }
七.分頁查詢數(shù)據(jù)
BILL_LIMIT_COUNT 20
分頁查詢數(shù)據(jù) sql語句 : select * from bill where loginName = '%@' ORDER BY tradeTime DESC limit '%d' offset '%d'
#pragma mark -通過頁碼來查詢賬單數(shù)據(jù)（分頁) -(NSArray*)querSelectSqlBillStartPage:(int)page{ NSString *path = [NSHomeDirectory() stringByAppendingString:@"/Library/Caches/Container.db"]; FMDatabase *database = [FMDatabase databaseWithPath:path]; if ([database open]){ [database setShouldCacheStatements:YES]; NSMutableArray *FinalArray=[NSMutableArray arrayWithCapacity:0]; FMResultSet *set = nil; NSString *sql =[NSString stringWithFormat: @"select * from bill where loginName = '%@' ORDER BY tradeTime DESC limit '%d' offset '%d'",SP_LOGIN_TOKEN.loginName,BILL_LIMIT_COUNT,page * BILL_LIMIT_COUNT]; set=[database executeQuery:sql]; NSLog(@"%@",[database lastErrorMessage]); while ([set next]){ NSDictionary *dic = @{@"orderId":[set stringForColumn:@"orderId"], @"tradeTime":[set stringForColumn:@"tradeTime"], @"amount":[set stringForColumn:@"amount"], @"status":[set stringForColumn:@"status"], @"bizDesc":[set stringForColumn:@"bizDesc"], @"partyName":[set stringForColumn:@"partyName"], @"cardNo":[set stringForColumn:@"cardNo"], @"bizCode":[set stringForColumn:@"bizCode"] }; [FinalArray addObject:dic]; } [database close]; return FinalArray; } else{ NSLog(@"could not open dababase!"); return nil; } }
