需求声明：最近做公司的项目，用到车辆品牌车系车型，上面给了一个数据库包含三个表（品牌，车系，车型三个表），让我们直接用，每次进入应用程序需要请求接口，传上面三个表中一个字段的最大值，根据返回我们移动端需要手动插入到数据库，总结下本次主要的几个点
1.外部数据库在Xcode中如何使用
2.数据库插入大量数据时候的解决办法
由于iOS中的沙盒机制，外部拖入的数据库文件我们无法对其进行修改，可读不可写（这里是外部拖入的文件，而不是应用程序自己创建的文件），所以我们需要进行一些额外操作，需要将元数据库文件拷贝到沙盒中，这里就需要用到文件相关的类NSFileManager，具体拷贝如下
//1、获得数据库文件在工程中的路径——源路径。
NSString *sourcesPath = [[NSBundle mainBundle] pathForResource:@"XSW_CAR_4"ofType:@"db"];
//2、获得沙盒中Document文件夹的路径——目的路径
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentPath = [paths objectAtIndex:0];
NSString *desPath = [documentPath stringByAppendingPathComponent:@"XSW_CAR_USER.db"];
//3、通过NSFileManager类，将工程中的数据库文件�复制到沙盒中。
NSFileManager *fileManager = [NSFileManager defaultManager];
if (![fileManager fileExistsAtPath:desPath])
{
NSError *error ;
if ([fileManager copyItemAtPath:sourcesPath toPath:desPath error:&error]) {
NSLog(@"数据库移动成功");
}
else {
NSLog(@"数据库移动失败");
}
}
那么以后使用对数据进行操作的时候使用的就是XSW_CAR_USER.db而不是XSW_CAR_4.db，以后的增删改查都是用XSW_CAR_USER.db这个就可以的
数据库的具体操作就不用说了，这里说有一下这个大量数据插入时候的处理，如果数据量小可能不会出现这个问题，但是当返回的数据上千条的时候处理起来就会有卡顿，严重影响用户体验，不要说什么可以放在子线程啊，这个需要你亲自去试试，数据库插入的时候为了保证安全性会做一些上锁解锁解锁处理，本次我的处理方式
NSString *doc =[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) lastObject];
NSString *fileName = [doc stringByAppendingPathComponent:@"XSW_CAR_USER.db"];
FMDatabase *dataBase = [FMDatabase databaseWithPath:fileName];
[dataBase open];
//请求之后有三个数组，分别对应三个表中的数据（每一条数据是一个字典，由于车型那个标数据量非常大，所以采用try - catch，一般的插入严重影响用体验）
NSDictionary* dic = (NSDictionary*)obj;
NSMutableArray* first = [NSMutableArray arrayWithArray:dic[@"brand"]];
NSMutableArray* second = [NSMutableArray arrayWithArray:dic[@"series"]];
NSMutableArray* third = [NSMutableArray arrayWithArray:dic[@"car"]];
[dataBase beginTransaction];
BOOL isRollBack = NO;
@try {
for (NSDictionary* dic in first) {
NSString *sql = @"insert into XSW_BRAND (id, idNo, name, abc, ad, cs, img) values(?, ?, ?, ?, ?, ?, ?) ";
BOOL res = [dataBase executeUpdate:sql, dic[@"id"], dic[@"idNo"], dic[@"name"], dic[@"abc"], dic[@"ad"], dic[@"cs"], dic[@"img"]];
if (!res) {
NSLog(@"BRAND error to insert data");
} else {
NSLog(@"BRAND success to insert data");
}
}
for (NSDictionary* dic in second) {
NSString* sql = @"insert into XSW_SERIES (id, idNo, name, bid, img) values(?, ?, ?, ?, ?) ";
BOOL res = [dataBase executeUpdate:sql, dic[@"id"], dic[@"idNo"] , dic[@"name"], dic[@"bid"], dic[@"img"]];
if (!res) {
NSLog(@"SERIES error to insert data");
} else {
NSLog(@"SERIES success to insert data");
}
}
for (NSDictionary* dic in third) {
NSString* sql = @"insert into XSW_MODEL (id, idNo, cid, name, year) values(?, ?, ?, ?, ?) ";
BOOL res = [dataBase executeUpdate:sql, dic[@"id"], dic[@"idNo"], dic[@"cid"], dic[@"name"], dic[@"year"]];
if (!res) {
NSLog(@"MODEL error to insert data");
} else {
NSLog(@"MODEL success to insert data");
}
}
}
@catch (NSException *exception) {
isRollBack = YES;
[dataBase rollback];
}
@finally {
if (!isRollBack) {
[dataBase commit];
}
}
[dataBase close];
具体的步骤基本上就这些了，下面贴上完整的逻辑代码，仅供参考，是我自己项目里的东西，仅供参考，仅供参考，仅供参考
-(void) loadDataFormDB{
//1、获得数据库文件在工程中的路径——源路径。
NSString *sourcesPath = [[NSBundle mainBundle] pathForResource:@"XSW_CAR_4"ofType:@"db"];
//2、获得沙盒中Document文件夹的路径——目的路径
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentPath = [paths objectAtIndex:0];
NSString *desPath = [documentPath stringByAppendingPathComponent:@"XSW_CAR_USER.db"];
//3、通过NSFileManager类，将工程中的数据库文件�复制到沙盒中。
NSFileManager *fileManager = [NSFileManager defaultManager];
if (![fileManager fileExistsAtPath:desPath])
{
NSError *error ;
if ([fileManager copyItemAtPath:sourcesPath toPath:desPath error:&error]) {
NSLog(@"数据库移动成功");
}
else {
NSLog(@"数据库移动失败");
}
}else{
NSLog(@"已经存在改数据库");
NSString *doc =[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES) lastObject];
NSString *fileName = [doc stringByAppendingPathComponent:@"XSW_CAR_USER.db"];
FMDatabase *dataBase = [FMDatabase databaseWithPath:fileName];
[dataBase open];
//Ken
NSMutableArray* brand = [NSMutableArray array];
FMResultSet* resultBrand = [dataBase executeQuery:@"select * from XSW_BRAND order by idNo ASC;"];
while ([resultBrand next]) {
VehicledModel *model = [[VehicledModel alloc] init];
model.carBrandPinyin = [resultBrand objectForColumnName:@"cs"];
model.carName = [resultBrand objectForColumnName:@"name"];
model.carId = [resultBrand objectForColumnName:@"id"];
model.carImageName = [resultBrand objectForColumnName:@"img"];
model.carNameFirstLetter = [resultBrand objectForColumnName:@"abc"];
model.carBrandAbbreviation = [resultBrand objectForColumnName:@"ad"];
model.idNo = [resultBrand objectForColumnName:@"idNo"];
[brand addObject:model];
}
VehicledModel *brandModel= (VehicledModel*)brand.lastObject;
NSLog(@"BRAND:%@", brandModel.idNo);
NSMutableArray* series = [NSMutableArray array];
FMResultSet* resultSeries = [dataBase executeQuery:@"select * from XSW_SERIES order by idNo ASC;"];
while ([resultSeries next]) {
SeriesModel *model = [[SeriesModel alloc] init];
model.seriesName = [resultSeries objectForColumnName:@"name"];
model.seriesId = [resultSeries objectForColumnName:@"id"];
model.bid = [resultSeries objectForColumnName:@"bid"];
model.seriesImageName = [resultSeries objectForColumnName:@"img"];
model.idNo = [resultSeries objectForColumnName:@"idNo"];
[series addObject:model];
}
SeriesModel *seriesmodel = (SeriesModel*)series.lastObject;
NSLog(@"series:%@",seriesmodel.idNo);
NSMutableArray* models = [NSMutableArray array];
FMResultSet* resultModel = [dataBase executeQuery:@"select * from XSW_MODEL order by idNo ASC;"];
while ([resultModel next]) {
TypeModel *model = [[TypeModel alloc] init];
model.modelId = [resultModel objectForColumnName:@"id"];
model.modelName = [resultModel objectForColumnName:@"name"];
model.year = [resultModel objectForColumnName:@"year"];
model.idNo = [resultModel objectForColumnName:@"idNo"];
model.cid = [resultModel objectForColumnName:@"cid"];
[models addObject:model];
}
TypeModel *typeModel = (TypeModel*)models.lastObject;
NSLog(@"typeModel:%@", typeModel.idNo);
ReqHeaderModel *headerModel = [[ReqHeaderModel alloc] init];
headerModel.token = [UserCenter getToken];
headerModel.reqTime = [UserCenter getSystemCurrentTime];
NSDictionary* body = @{
@"brand":[NSString stringWithFormat:@"%@", brandModel.idNo],
@"series":[NSString stringWithFormat:@"%@", seriesmodel.idNo],
@"car":[NSString stringWithFormat:@"%@", typeModel.idNo]
};
NSDictionary *dic = @{
@"uid":@"",
@"body":[MPMessagePackWriter writeObject:body error:nil]
};
NSData *data = [dic mp_messagePack];
[PublicViewModel getPublicDataFromWebWithHeaderParma:headerModel Parma:data Type:DOSYSCARDATA View:self.window Hud:nil isRefresh:NO tableView:nil picker:nil Callback:^(id model) {
if ([model isKindOfClass:[NSError class]]) {
}else{
XSWBaseModel *baseModel = model;
NSObject* obj = [MPMessagePackReader readData:baseModel.body error:nil];
if ([obj isKindOfClass:[NSString class]]) {
NSLog(@"暂不需要更新数据库");
}else{
NSDictionary* dic = (NSDictionary*)obj;
NSMutableArray* first = [NSMutableArray arrayWithArray:dic[@"brand"]];
NSMutableArray* second = [NSMutableArray arrayWithArray:dic[@"series"]];
NSMutableArray* third = [NSMutableArray arrayWithArray:dic[@"car"]];
[dataBase beginTransaction];
BOOL isRollBack = NO;
@try {
for (NSDictionary* dic in first) {
NSString *sql = @"insert into XSW_BRAND (id, idNo, name, abc, ad, cs, img) values(?, ?, ?, ?, ?, ?, ?) ";
BOOL res = [dataBase executeUpdate:sql, dic[@"id"], dic[@"idNo"], dic[@"name"], dic[@"abc"], dic[@"ad"], dic[@"cs"], dic[@"img"]];
if (!res) {
NSLog(@"BRAND error to insert data");
} else {
NSLog(@"BRAND success to insert data");
}
}
for (NSDictionary* dic in second) {
NSString* sql = @"insert into XSW_SERIES (id, idNo, name, bid, img) values(?, ?, ?, ?, ?) ";
BOOL res = [dataBase executeUpdate:sql, dic[@"id"], dic[@"idNo"] , dic[@"name"], dic[@"bid"], dic[@"img"]];
if (!res) {
NSLog(@"SERIES error to insert data");
} else {
NSLog(@"SERIES success to insert data");
}
}
for (NSDictionary* dic in third) {
NSString* sql = @"insert into XSW_MODEL (id, idNo, cid, name, year) values(?, ?, ?, ?, ?) ";
BOOL res = [dataBase executeUpdate:sql, dic[@"id"], dic[@"idNo"], dic[@"cid"], dic[@"name"], dic[@"year"]];
if (!res) {
NSLog(@"MODEL error to insert data");
} else {
NSLog(@"MODEL success to insert data");
}
}
}
@catch (NSException *exception) {
isRollBack = YES;
[dataBase rollback];
}
@finally {
if (!isRollBack) {
[dataBase commit];
}
}
[dataBase close];
}
}
}];
}
}
就这样，希望可以帮助到有需要的小伙伴。。。
网友评论