在IOS CoreData中,多表查询上相对来说,没有SQL直观,但CoreData的功能还是可以完成相关操作的。
下面使用CoreData进行关系数据库的表与表之间的关系演示。生成CoreData和如何设置关系就不再详谈了,见之前的文章。
建立好的关系图:
一步步建立上面关系图:
员工表:
部门表:
职位表:
工资等级表:
开户银行表:
建立表之后,我们还需要建立表之间的关系
部门和员工之间的关系:1 -> N
部门和职位的关系:1 -> N
职位与员工的关系:1 -> N
开户行与员工:一个员工只能提供一个开户行,但一个开户行可以给多名员工进行开卡。所以关系为1 -> N;
职位和工资等级:一个职位只对应一个工资等级;1 - 1
IOS CoreData 多表查询demo解析(二)--插入测试数据
下面插入测试数据:
Department *dept1 = [NSEntityDescription insertNewObjectForEntityForName:@"Department" inManagedObjectContext:self.managedObjectContext];
dept1.dp_deptname = @"HR";
Department *dept2 = [NSEntityDescription insertNewObjectForEntityForName:@"Department" inManagedObjectContext:self.managedObjectContext];
dept2.dp_deptname = @"DEV";
Department *dept3 = [NSEntityDescription insertNewObjectForEntityForName:@"Department" inManagedObjectContext:self.managedObjectContext];
dept3.dp_deptname = @"POD";
Salary *sy1 =[NSEntityDescription insertNewObjectForEntityForName:@"Salary" inManagedObjectContext:self.managedObjectContext];
sy1.sy_level = @"D";
sy1.sy_scale = 0.1;//[NSNumber numberWithDouble:0.1];
Salary *sy2 =[NSEntityDescription insertNewObjectForEntityForName:@"Salary" inManagedObjectContext:self.managedObjectContext];
sy2.sy_level = @"C";
sy2.sy_scale = 0.15;//[NSNumber numberWithDouble:0.15];
Salary *sy3 =[NSEntityDescription insertNewObjectForEntityForName:@"Salary" inManagedObjectContext:self.managedObjectContext];
sy3.sy_level = @"B";
sy3.sy_scale = 0.4;//[NSNumber numberWithDouble:0.4];
Salary *sy4 =[NSEntityDescription insertNewObjectForEntityForName:@"Salary" inManagedObjectContext:self.managedObjectContext];
sy4.sy_level = @"A";
sy4.sy_scale = 0.8;//[NSNumber numberWithDouble:0.8];
Post *pt1 = [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt1.pt_name = @"行政专员";
pt1.dept = dept1;
pt1.salary = sy2;
Post *pt2 = [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt2.pt_name = @"人事经理";
pt2.dept = dept1;
pt2.salary = sy3;
Post *pt3 = [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt3.pt_name = @"开发工程师";
pt3.dept = dept2;
pt3.salary = sy2;
Post *pt4 = [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt4.pt_name = @"架构师";
pt4.dept = dept2;
pt4.salary = sy3;
Post *pt5 = [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt5.pt_name = @"项目经理";
pt5.dept =dept2;
pt5.salary = sy3;
Post *pt6= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt6.pt_name = @"测试工程师";
pt6.dept = dept2;
pt6.salary = sy1;
Post *pt7 = [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt7.pt_name = @"销售代表";
pt7.dept = dept3;
pt7.salary = sy1;
Post *pt8 = [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt8.pt_name = @"销售经理";
pt8.dept = dept3;
pt8.salary = sy2;
Post *pt9= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt9.pt_name = @"大客户经理";
pt9.dept = dept3;
pt9.salary = sy4;
Bank *bk1 = [NSEntityDescription insertNewObjectForEntityForName:@"Bank" inManagedObjectContext:self.managedObjectContext];
bk1.bk_name = @"招行";
bk1.bk_address = @"广州";
Bank *bk2 = [NSEntityDescription insertNewObjectForEntityForName:@"Bank" inManagedObjectContext:self.managedObjectContext];
bk2.bk_name = @"浦发";
bk2.bk_address = @"上海";
Bank *bk3 = [NSEntityDescription insertNewObjectForEntityForName:@"Bank" inManagedObjectContext:self.managedObjectContext];
bk3.bk_name = @"工行";
bk3.bk_address = @"深圳";
Employee *em1 = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em1.em_age = 20;//[NSNumber numberWithInt:20];
em1.em_name = @"张三";
em1.em_sex = 1;//[NSNumber numberWithInt:1];
em1.em_bankcardid = @"46326587439043";
em1.dept = dept1;
em1.post = pt1;
em1.bank = bk3;
Employee *em2 = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em2.em_age = 18;//[NSNumber numberWithInt:18];
em2.em_name = @"李四";
em2.em_sex = 2;//[NSNumber numberWithInt:2];
em2.em_bankcardid = @"32565443246567";
em2.dept = dept1;
em2.post = pt2;
em2.bank = bk3;
Employee *em3 = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em3.em_age = 26;//[NSNumber numberWithInt:26];
em3.em_name = @"欧文";
em3.em_sex = 2;//[NSNumber numberWithInt:2];
em3.em_bankcardid = @"14354654656767";
em3.dept = dept2;
em3.post = pt3;
em3.bank = bk2;
Employee *em4 = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em4.em_age = 22;//[NSNumber numberWithInt:22];
em4.em_name = @"张三";
em4.em_sex = 2;//[NSNumber numberWithInt:2];
em4.em_bankcardid = @"9873425837433";
em4.dept = dept2;
em4.post = pt4;
em4.bank = bk2;
Employee *em5 = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em5.em_age = 28;//[NSNumber numberWithInt:18];
em5.em_name = @"李四";
em5.em_sex = 1;//[NSNumber numberWithInt:2];
em5.em_bankcardid = @"32565743246567";
em5.dept = dept2;
em5.post = pt5;
em5.bank = bk3;
Employee *em6 = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em6.em_age = 21;//[NSNumber numberWithInt:26];
em6.em_name = @"欧阳";
em6.em_sex = 1;//[NSNumber numberWithInt:2];
em6.em_bankcardid = @"14354614656767";
em6.dept = dept2;
em6.post = pt6;
em6.bank = bk1;
Employee *em7 = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em7.em_age = 22;//[NSNumber numberWithInt:22];
em7.em_name = @"王飞";
em7.em_sex = 2;//[NSNumber numberWithInt:2];
em7.em_bankcardid = @"9878429837433";
em7.dept = dept3;
em7.post = pt7;
em7.bank = bk1;
Employee *em8 = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em8.em_age = 26;//[NSNumber numberWithInt:18];
em8.em_name = @"张前";
em8.em_sex = 1;//[NSNumber numberWithInt:2];
em8.em_bankcardid = @"32565443246167";
em8.dept = dept3;
em8.post = pt8;
em8.bank = bk2;
Employee *em9 = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em9.em_age = 21;//[NSNumber numberWithInt:26];
em9.em_name = @"谢阳";
em9.em_sex = 1;//[NSNumber numberWithInt:2];
em9.em_bankcardid = @"14354694656767";
em9.dept = dept3;
em9.post = pt9;
em9.bank = bk1;
[self saveContext];
数据库中数据:
1、查询 HR 部门的张三
//查询 开发部门中张三的工资等级
NSLog(@"----------查询 开发部门中张三的工资等级----------");
NSLog(@"----------第1种方法----------");
//第1种方法
NSEntityDescription * emEty = [NSEntityDescription entityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
NSFetchRequest *frq = [[NSFetchRequest alloc]init];
[frq setEntity:emEty];
NSPredicate * cdt = [NSPredicate predicateWithFormat:@"em_name = %@", @"张三"];
[frq setPredicate:cdt];
NSArray *objs =[self.managedObjectContext executeFetchRequest:frq error:nil];
NSPredicate * filter = [NSPredicate predicateWithFormat:@"dept.dp_deptname = %@",@"HR"];
NSArray * ret = [objs filteredArrayUsingPredicate:filter];
Employee* r = (Employee*)[ret lastObject];
NSLog(@"%@", r.em_name);
NSLog(@"%d", r.em_age);
NSLog(@"%d", r.em_sex);
NSLog(@"%@", r.em_bankcardid);
NSLog(@"%@", r.post.salary.sy_level);
NSLog(@"%f", r.post.salary.sy_scale);
NSLog(@"%@", r.bank.bk_name);
NSLog(@"%@", r.bank.bk_address);
//[self.managedObjectContext deleteObject:r];
//[self saveContext];
NSLog(@"----------第2种方法----------");
//第2种方法
NSEntityDescription * emEty1 = [NSEntityDescription entityForName:@"Department" inManagedObjectContext:self.managedObjectContext];
NSFetchRequest *frq1 = [[NSFetchRequest alloc]init];
[frq1 setEntity:emEty1];
NSPredicate * cdt1 = [NSPredicate predicateWithFormat:@"dp_deptname = %@", @"HR"];
[frq1 setPredicate:cdt1];
Department *objs1 =[[self.managedObjectContext executeFetchRequest:frq1 error:nil] lastObject];
NSSet * ret1 = objs1.employeeship;
NSPredicate * filter1 = [NSPredicate predicateWithFormat:@"em_name = %@",@"张三"];
Employee* r1 = [[ret1 filteredSetUsingPredicate:filter1] anyObject];
NSLog(@"%@", r1.em_name);
NSLog(@"%d", r1.em_age);
NSLog(@"%d", r1.em_sex);
NSLog(@"%@", r1.em_bankcardid);
NSLog(@"%@", r1.post.salary.sy_level);
NSLog(@"%f", r1.post.salary.sy_scale);
NSLog(@"%@", r1.bank.bk_name);
NSLog(@"%@", r1.bank.bk_address);
2、查询 运维部欧阳
//查询运维部名为欧阳的工资等级及开户银行
NSLog(@"----------查询运维部名为欧阳的工资等级及开户银行----------");
NSLog(@"----------第1种方法----------");
//第1种方法
NSEntityDescription * entity = [NSEntityDescription entityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
NSFetchRequest *fetch = [[NSFetchRequest alloc] init];
[fetch setEntity:entity];
NSPredicate * qcmd = [NSPredicate predicateWithFormat:@"em_name = %@ ",@"欧阳"];
[fetch setPredicate:qcmd];
NSArray * obs = [self.managedObjectContext executeFetchRequest:fetch error:nil];
NSPredicate * filter2 = [NSPredicate predicateWithFormat:@"dept.dp_deptname = %@",@"DEV"];
NSArray * ret2 = [obs filteredArrayUsingPredicate:filter2]; //从数组中进行过滤。
Employee* r2 = [ret2 lastObject];
NSLog(@"%@", r2.em_name);
NSLog(@"%d", r2.em_age);
NSLog(@"%d", r2.em_sex);
NSLog(@"%@", r2.em_bankcardid);
NSLog(@"%@", r2.post.salary.sy_level);
NSLog(@"%f", r2.post.salary.sy_scale);
NSLog(@"%@", r2.bank.bk_name);
NSLog(@"%@", r2.bank.bk_address);
NSLog(@"----------第2种方法----------");
//第2种方法
NSEntityDescription * emEty3 = [NSEntityDescription entityForName:@"Department" inManagedObjectContext:self.managedObjectContext];
NSFetchRequest *frq3 = [[NSFetchRequest alloc]init];
[frq3 setEntity:emEty3];
NSPredicate * cdt3 = [NSPredicate predicateWithFormat:@"dp_deptname = %@", @"DEV"];
[frq3 setPredicate:cdt3];
Department *objs3 =[[self.managedObjectContext executeFetchRequest:frq3 error:nil] lastObject];
NSSet * ret3 = objs3.employeeship;
NSPredicate * filter3 = [NSPredicate predicateWithFormat:@"em_name = %@",@"欧阳"];
Employee* r3 = [[ret3 filteredSetUsingPredicate:filter3] anyObject];
NSLog(@"%@", r3.em_name);
NSLog(@"%d", r3.em_age);
NSLog(@"%d", r3.em_sex);
NSLog(@"%@", r3.em_bankcardid);
NSLog(@"%@", r3.post.salary.sy_level);
NSLog(@"%f", r3.post.salary.sy_scale);
NSLog(@"%@", r3.bank.bk_name);
NSLog(@"%@", r3.bank.bk_address);
多表查询主要就在于表之间建立好相关的关联关系(relationship),其次就是充分的使用NSPredicate这个查询条件来进行过滤。