解决办法：
因为账单用FMDB保存到了数据库，所以要在数据库的表中，添加一个字段：区分不同的用户——》那就是用户名了(手机号)
如果一个手机可以在多个设备登录，消息的状态，应该用"手机号+设备ID"作为唯一标示。
一.创建表
创建表的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---来创建表
- (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]; }
二.插入一条数据
插入一条数据的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; }
三.批量插入数据
（循环）插入一条数据的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 { //可能出现问题的代码写在此处 //手动开启一个事务(对sqlite3 开启一个事务) [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中的代码出现了异常，此处能捕获到异常 NSLog(@"error:%@",exception.reason); isRollBack = YES; //对数据库执行事务的回滚操作，回到最初的状态 [database rollback]; } @finally { //不管出不出现异常，代码都可以执行到此处 if (isRollBack ==NO) { //提交事务 [database commit]; } } [database close]; } return result; }
四.查询一条数据
查询一条数据 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; } }
五.批量查询数据
多条件查询 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; } }
六.批量更新数据
（循环）一条更新 sql语句：update bill where loginName ='%@' set %@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@',%@='%@‘(错误)
应该是：（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 数据批量更新失败"); break; } } [database commit]; [database close]; } return result; }
七.分页查询数据
BILL_LIMIT_COUNT 20
分页查询数据 sql语句 : select * from bill where loginName = '%@' ORDER BY tradeTime DESC limit '%d' offset '%d'
#pragma mark -通过页码来查询账单数据（分页) -(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; } }
