MyBatis中关联关系查询sql写法

MyBatis中关联关系查询sql写法

一，一对多关系查询（以country与minister关系为例）

1，通过多表连接查询方式实现

 XML Code 

1 2 3 4 5 6 7 8 9 10 11 12

<resultMap type= "Country"  id= "countryMapper" >    <id column= "cid"  property= "cid"  />    <result column= "cname"  property= "cname"  />    <collection property= "ministers"  ofType= "Minister" >      <id column= "mid"  property= "mid"  />      <result column= "mname"  property= "mname"  />    </collection> </resultMap> <select id= "selectCountryById"  resultMap= "countryMapper" >     select cid,cname,mid,mname from country,minister where countryId=cid and cid=#{xxx} </select>

2，通过多表单独查询方式实现（开发中常用这种方式）

 XML Code 

1 2 3 4 5 6 7 8 9 10

<select id= "selectMinisterByCountry"  resultType= "Minister" >  select mid,mname from minister where countryId=#{ooo} </select> <resultMap type= "Country"  id= "countryMapper" >    <id column= "cid"  property= "cid"  />    <result column= "cname"  property= "cname"  />    <collection property= "ministers"  ofType= "Minister"  select= "selectMinisterByCountry"  column= "cid"  /> </resultMap> <select id= "selectCountryById"  resultMap= "countryMapper" >     select cid,cname from country where cid=#{xxx} </select>

二、多对一关系查询（以country与minister关系为例）

1，通过多表连接查询方式实现

 XML Code 

1 2 3 4 5 6 7 8 9 10 11 12

<resultMap type= "Minister"  id= "ministerMapper" >    <id column= "mid"  property= "mid"  />    <result column= "mname"  property= "mname"  />    <association property= "country"  javaType= "Country" >      <id column= "cid"  property= "cid"  />      <result column= "cname"  property= "cname"  />    </association> </resultMap> <select id= "selectMinisterById"  resultMap= "ministerMapper" >     select mid,mname,cid,cname from minister, country where countryId=cid and mid=#{xxx} </select>

2，通过多表单独查询方式实现

 XML Code 

1 2 3 4 5 6 7 8 9 10 11 12

<select id= "selectCountryById"  resultType= "Country" >     select cid,cname from country where cid=#{ooo} </select> <resultMap type= "Minister"  id= "ministerMapper" >    <id column= "mid"  property= "mid"  />    <result column= "mname"  property= "mname"  />    <association property= "country"  javaType= "Country"  select= "selectCountryById"  column= "countryId"  /> </resultMap> <select id= "selectMinisterById"  resultMap= "ministerMapper" >     select mid,mname,countryId from minister where mid=#{xxx} </select>

三、自关联查询（以NewsLabel为例）

1，以一对多方式实现-查询指定栏目的所有子孙栏目

 XML Code 

1 2 3 4 5 6 7 8 9

<resultMap type= "NewsLabel"  id= "newslabelMapper" >    <id column= "id"  property= "id"  />    <result column= "name"  property= "name"  />    <collection property= "children"  ofType= "NewsLabel"  select= "selectChildrenByParent"  column= "id"  /> </resultMap> <select id= "selectChildrenByParent"  resultMap= "newslabelMapper" >     select id,name from newslabel where pid=#{xxx} </select>

2，以一对多方式实现-查询指定栏目及其所有子孙栏目

 XML Code 

1 2 3 4 5 6 7 8 9 10 11 12

<select id= "selectNewslabelByParent"  resultMap= "newslabelMapper" >     select id,name from newslabel where pid=#{ooo} </select> <resultMap type= "NewsLabel"  id= "newslabelMapper" >    <id column= "id"  property= "id"  />    <result column= "name"  property= "name"  />    <collection property= "children"  ofType= "NewsLabel"  select= "selectNewslabelByParent"  column= "id"  /> </resultMap> <select id= "selectNewsLabelById"  resultMap= "newslabelMapper" >     select id,name from newslabel where id=#{xxx} </select>

3，以多对一方式实现

 XML Code 

1 2 3 4 5 6 7 8 9

<resultMap type= "NewsLabel"  id= "newslabelMapper" >    <id column= "id"  property= "id"  />    <result column= "name"  property= "name"  />    <association property= "parent"  javaType= "NewsLabel"  select= "selectNewsLabelById"  column= "pid"  /> </resultMap> <select id= "selectNewsLabelById"  resultMap= "newslabelMapper" >     select id,name,pid from newslabel where id=#{xxx} </select>

四、多对多关系查询（以student与course为例）

 XML Code 

1 2 3 4 5 6 7 8 9 10 11 12

<resultMap type= "Student"  id= "studentMapper" >    <id column= "sid"  property= "sid"  />    <result column= "sname"  property= "sname"  />    <collection property= "courses"  ofType= "Course" >      <id column= "cid"  property= "cid"  />      <result column= "cname"  property= "cname"  />    </collection> </resultMap> <select id= "selectStudentById"  resultMap= "studentMapper" >     select sid,sname,cid,cname from student,middle,course where sid=studentId and cid=courseId and sid=#{xxx} </select>