IOS CoreData 多表查询demo解析

时间:2021-12-08 20:14:52

在IOS CoreData中,多表查询上相对来说,没有SQL直观,但CoreData的功能还是可以完成相关操作的。

下面使用CoreData进行关系数据库的表与表之间的关系演示。生成CoreData和如何设置关系就不再详谈了,见之前的文章。

建立好的关系图:

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

一步步建立上面关系图:

员工表:

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

部门表:

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

职位表:

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

工资等级表:

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

开户银行表:

IOS CoreData 多表查询demo解析IOS CoreData 多表查询demo解析

建立表之后,我们还需要建立表之间的关系

部门和员工之间的关系:1 ->  N

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

部门和职位的关系:1  ->  N

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

职位与员工的关系:1 ->  N

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

开户行与员工:一个员工只能提供一个开户行,但一个开户行可以给多名员工进行开卡。所以关系为1 -> N;

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

职位和工资等级:一个职位只对应一个工资等级;1  -  1

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

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];

数据库中数据:

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

IOS CoreData 多表查询demo解析

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这个查询条件来进行过滤。