http://blog.csdn.net/fengsh998/article/details/8123392
在iOS CoreData中,多表查询上相对来说,没有SQL直观,但COREDATA的功能还是可以完成相关操作的。
多表查询,表与表之间肯定存在某种关系,如果对于类似外连接,左连接等操作,在COREDATA中就显得无力(请高手指教了)。
在上节中,介绍了一下数据库的关系查询操作。
下面使用CoreData进行关系数据库的表与表之间的关系演示。
生成COREDATA和如何设置关系就不再详谈了,见之前的文章。
建立好的关系图:
一步步建立上面关系图:
先建立部门表,员工表,职位表,工资等级表,开户银行表
上面建立表之后,我们还需要建立表之间的关系
部门和员工之间的关系:1 V N
部门和职位的关系:1 V N
员工与职位的关系:多对一关系
员工与开户行:一个员工只能提供一个开户行,但一个开户行可以给多名员工进行开卡。所以关系为N V 1;
职位和工资等级:一个职位只对应一个工资等级;1V1
下面插入测试数据:
{
Department *dept = [NSEntityDescription insertNewObjectForEntityForName:@"Department" inManagedObjectContext:self.managedObjectContext];
dept.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 *sy =[NSEntityDescription insertNewObjectForEntityForName:@"Salary" inManagedObjectContext:self.managedObjectContext];
sy.sy_level = @"D";
sy.sy_scale = [NSNumber numberWithDouble:0.1];
Salary *sy2 =[NSEntityDescription insertNewObjectForEntityForName:@"Salary" inManagedObjectContext:self.managedObjectContext];
sy2.sy_level = @"C";
sy2.sy_scale = [NSNumber numberWithDouble:0.15];
Salary *sy3 =[NSEntityDescription insertNewObjectForEntityForName:@"Salary" inManagedObjectContext:self.managedObjectContext];
sy3.sy_level = @"B";
sy3.sy_scale = [NSNumber numberWithDouble:0.4];
Salary *sy4 =[NSEntityDescription insertNewObjectForEntityForName:@"Salary" inManagedObjectContext:self.managedObjectContext];
sy4.sy_level = @"A";
sy4.sy_scale = [NSNumber numberWithDouble:0.8];
Post *pt= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt.pt_name = @"行政专员";
pt.dept = dept;
Post *pt2= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt2.salary = sy3;
pt2.pt_name = @"人事经理";
pt2.dept = dept;
Post *pt3= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt3.pt_name = @"开发工程师";
pt3.dept = dept2;
pt3.salary = sy2;
pt= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt.pt_name = @"架构师";
pt.dept = dept2;
pt= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt.pt_name = @"项目经理";
pt.dept =dept2;
Post *pt6= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt6.pt_name = @"测试工程师";
pt6.dept = dept2;
pt6.salary = sy;
pt= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt.pt_name = @"销售代表";
pt.dept = dept3;
pt= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt.pt_name = @"销售经理";
pt.dept = dept3;
Post *pt9= [NSEntityDescription insertNewObjectForEntityForName:@"Post" inManagedObjectContext:self.managedObjectContext];
pt9.pt_name = @"大客户经理";
pt9.dept = dept3;
pt9.salary = sy4;
Bank *bk = [NSEntityDescription insertNewObjectForEntityForName:@"Bank" inManagedObjectContext:self.managedObjectContext];
bk.bk_name = @"招行";
bk.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 *em = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em.em_age = [NSNumber numberWithInt:20];
em.em_name = @"张三";
em.em_sex = [NSNumber numberWithInt:1];
em.em_bankcardid = @"46326587439043";
em.dept = dept2;
em.post = pt3;
em.bank = bk2;
em = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em.em_age = [NSNumber numberWithInt:18];
em.em_name = @"李四";
em.em_sex = [NSNumber numberWithInt:2];
em.em_bankcardid = @"32565443246567";
em.dept = dept;
em.post = pt2;
em.bank = bk3;
em = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em.em_age = [NSNumber numberWithInt:26];
em.em_name = @"欧阳";
em.em_sex = [NSNumber numberWithInt:2];
em.em_bankcardid = @"14354654656767";
em.dept = dept3;
em.post = pt9;
em.bank = bk3;
em = [NSEntityDescription insertNewObjectForEntityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
em.em_age = [NSNumber numberWithInt:22];
em.em_name = @"欧阳";
em.em_sex = [NSNumber numberWithInt:2];
em.em_bankcardid = @"9873429837433";
em.dept = dept2;
em.post = pt6;
em.bank = bk3;
[self.managedObjectContext save:nil];
}
数据库中数据:
1、查询开发部门中名为张三的工资等级
NSEntityDescription * emEty = [NSEntityDescription entityForName:@"Employee" inManagedObjectContext:self.managedObjectContext];
NSFetchRequest *frq = [[[NSFetchRequest alloc]init]autorelease];
[frq setEntity:emEty];
NSPredicate * cdt = [NSPredicate predicateWithFormat:@"em_name = %@",@"张三"];
[frq setPredicate:cdt];
NSArray *objs =[self.managedObjectContext executeFetchRequest:frq error:nil];
//NSLog(@"%i",[objs count]);
NSLog(@"%@",((Employee*)[objs objectAtIndex:0]).post.salary.sy_level);
2、查询运维部名为欧阳的工资等级及开户银行
NSFetchRequest *fetch = [[[NSFetchRequest alloc]init]autorelease];
[fetch setEntity:entity];
NSPredicate * qcmd = [NSPredicate predicateWithFormat:@"em_name = %@ ",@"欧阳"];
[fetch setPredicate:qcmd];
NSArray * obs = [self.managedObjectContext executeFetchRequest:fetch error:nil];
NSPredicate * filter = [NSPredicate predicateWithFormat:@"dept.dp_deptname = %@",@"POD"];
NSArray * ret = [obs filteredArrayUsingPredicate:filter]; //从数组中进行过滤。
NSLog(@"%@",((Employee*)[ret objectAtIndex:0]).post.salary.sy_level);
多表查询主要就在于表之间建立好相关的关联关系(relationship),其次就是充分的使用NSPredicate 这个查询条件来进行过滤。