MyBatis中关联关系查询sql写法

你的名字 2022-06-01 07:23 301阅读 0赞

# MyBatis中关联关系查询sql写法 #

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

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

XML Code

<table style="font-family:Consolas,&quot;Courier New&quot;; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:#efefef; width:100%; vertical-align:text-top; color:#000000"> 
 <div style="margin:7px"> 
 &lt;resultMap&nbsp;type= 
 "Country" 
 &nbsp;id= 
 "countryMapper" 
 &gt; 
 &nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "cid" 
 &nbsp;property= 
 "cid" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "cname" 
 &nbsp;property= 
 "cname" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;collection&nbsp;property= 
 "ministers" 
 &nbsp;ofType= 
 "Minister" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "mid" 
 &nbsp;property= 
 "mid" 
 &nbsp;/&gt; 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "mname" 
 &nbsp;property= 
 "mname" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;/collection&gt; 
 
 &lt;/resultMap&gt; 
 
 &lt;select&nbsp;id= 
 "selectCountryById" 
 &nbsp;resultMap= 
 "countryMapper" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;cid,cname,mid,mname&nbsp;from&nbsp;country,minister&nbsp;where&nbsp;countryId=cid&nbsp;and&nbsp;cid=#{xxx} 
 
 &lt;/select&gt; 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

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

XML Code

<table style="font-family:Consolas,&quot;Courier New&quot;; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:#efefef; width:100%; vertical-align:text-top; color:#000000"> 
 <div style="margin:7px"> 
 &lt;select&nbsp;id= 
 "selectMinisterByCountry" 
 &nbsp;resultType= 
 "Minister" 
 &gt; 
 </div> 
 <div style="margin:7px"> 
 &nbsp;select&nbsp;mid,mname&nbsp;from&nbsp;minister&nbsp;where&nbsp;countryId=#{ooo} 
 </div> 
 <div style="margin:7px"> 
 &lt;/select&gt; 
 
 &lt;resultMap&nbsp;type= 
 "Country" 
 &nbsp;id= 
 "countryMapper" 
 &gt; 
 &nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "cid" 
 &nbsp;property= 
 "cid" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "cname" 
 &nbsp;property= 
 "cname" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;collection&nbsp;property= 
 "ministers" 
 &nbsp;ofType= 
 "Minister" 
 &nbsp;select= 
 "selectMinisterByCountry" 
 &nbsp;column= 
 "cid" 
 &nbsp;/&gt; 
 
 &lt;/resultMap&gt; 
 
 &lt;select&nbsp;id= 
 "selectCountryById" 
 &nbsp;resultMap= 
 "countryMapper" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;cid,cname&nbsp;from&nbsp;country&nbsp;where&nbsp;cid=#{xxx} 
 
 &lt;/select&gt; 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

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

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

XML Code

<table style="font-family:Consolas,&quot;Courier New&quot;; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:#efefef; width:100%; vertical-align:text-top; color:#000000"> 
 <div style="margin:7px"> 
 &lt;resultMap&nbsp;type= 
 "Minister" 
 &nbsp;id= 
 "ministerMapper" 
 &gt; 
 &nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "mid" 
 &nbsp;property= 
 "mid" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "mname" 
 &nbsp;property= 
 "mname" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;association&nbsp;property= 
 "country" 
 &nbsp;javaType= 
 "Country" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "cid" 
 &nbsp;property= 
 "cid" 
 &nbsp;/&gt; 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "cname" 
 &nbsp;property= 
 "cname" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;/association&gt; 
 
 &lt;/resultMap&gt; 
 
 &lt;select&nbsp;id= 
 "selectMinisterById" 
 &nbsp;resultMap= 
 "ministerMapper" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;mid,mname,cid,cname&nbsp;from&nbsp;minister,&nbsp;country&nbsp;where&nbsp;countryId=cid&nbsp;and&nbsp;mid=#{xxx} 
 
 &lt;/select&gt; 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

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

XML Code

<table style="font-family:Consolas,&quot;Courier New&quot;; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:#efefef; width:100%; vertical-align:text-top; color:#000000"> 
 <div style="margin:7px"> 
 &lt;select&nbsp;id= 
 "selectCountryById" 
 &nbsp;resultType= 
 "Country" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;cid,cname&nbsp;from&nbsp;country&nbsp;where&nbsp;cid=#{ooo} 
 
 &lt;/select&gt; 
 
 &lt;resultMap&nbsp;type= 
 "Minister" 
 &nbsp;id= 
 "ministerMapper" 
 &gt; 
 &nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "mid" 
 &nbsp;property= 
 "mid" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "mname" 
 &nbsp;property= 
 "mname" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;association&nbsp;property= 
 "country" 
 &nbsp;javaType= 
 "Country" 
 &nbsp;select= 
 "selectCountryById" 
 &nbsp;column= 
 "countryId" 
 &nbsp;/&gt; 
 
 &lt;/resultMap&gt; 
 
 &lt;select&nbsp;id= 
 "selectMinisterById" 
 &nbsp;resultMap= 
 "ministerMapper" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;mid,mname,countryId&nbsp;from&nbsp;minister&nbsp;where&nbsp;mid=#{xxx} 
 
 &lt;/select&gt; 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

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

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

XML Code

<table style="font-family:Consolas,&quot;Courier New&quot;; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:#efefef; width:100%; vertical-align:text-top; color:#000000"> 
 <div style="margin:7px"> 
 &lt;resultMap&nbsp;type= 
 "NewsLabel" 
 &nbsp;id= 
 "newslabelMapper" 
 &gt; 
 &nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "id" 
 &nbsp;property= 
 "id" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "name" 
 &nbsp;property= 
 "name" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;collection&nbsp;property= 
 "children" 
 &nbsp;ofType= 
 "NewsLabel" 
 &nbsp;select= 
 "selectChildrenByParent" 
 &nbsp;column= 
 "id" 
 &nbsp;/&gt; 
 
 &lt;/resultMap&gt; 
 
 &lt;select&nbsp;id= 
 "selectChildrenByParent" 
 &nbsp;resultMap= 
 "newslabelMapper" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;id,name&nbsp;from&nbsp;newslabel&nbsp;where&nbsp;pid=#{xxx} 
 
 &lt;/select&gt; 
 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

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

XML Code

<table style="font-family:Consolas,&quot;Courier New&quot;; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:#efefef; width:100%; vertical-align:text-top; color:#000000"> 
 <div style="margin:7px"> 
 &lt;select&nbsp;id= 
 "selectNewslabelByParent" 
 &nbsp;resultMap= 
 "newslabelMapper" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;id,name&nbsp;from&nbsp;newslabel&nbsp;where&nbsp;pid=#{ooo} 
 
 &lt;/select&gt; 
 
 &lt;resultMap&nbsp;type= 
 "NewsLabel" 
 &nbsp;id= 
 "newslabelMapper" 
 &gt; 
 &nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "id" 
 &nbsp;property= 
 "id" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "name" 
 &nbsp;property= 
 "name" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;collection&nbsp;property= 
 "children" 
 &nbsp;ofType= 
 "NewsLabel" 
 &nbsp;select= 
 "selectNewslabelByParent" 
 &nbsp;column= 
 "id" 
 &nbsp;/&gt; 
 
 &lt;/resultMap&gt; 
 
 &lt;select&nbsp;id= 
 "selectNewsLabelById" 
 &nbsp;resultMap= 
 "newslabelMapper" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;id,name&nbsp;from&nbsp;newslabel&nbsp;where&nbsp;id=#{xxx} 
 
 &lt;/select&gt; 
 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

### 3，以多对一方式实现 ###

XML Code

<table style="font-family:Consolas,&quot;Courier New&quot;; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:#efefef; width:100%; vertical-align:text-top; color:#000000"> 
 <div style="margin:7px"> 
 &lt;resultMap&nbsp;type= 
 "NewsLabel" 
 &nbsp;id= 
 "newslabelMapper" 
 &gt; 
 &nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "id" 
 &nbsp;property= 
 "id" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "name" 
 &nbsp;property= 
 "name" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;association&nbsp;property= 
 "parent" 
 &nbsp;javaType= 
 "NewsLabel" 
 &nbsp;select= 
 "selectNewsLabelById" 
 &nbsp;column= 
 "pid" 
 &nbsp;/&gt; 
 
 &lt;/resultMap&gt; 
 
 &lt;select&nbsp;id= 
 "selectNewsLabelById" 
 &nbsp;resultMap= 
 "newslabelMapper" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;id,name,pid&nbsp;from&nbsp;newslabel&nbsp;where&nbsp;id=#{xxx} 
 
 &lt;/select&gt; 
 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

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

XML Code

<table style="font-family:Consolas,&quot;Courier New&quot;; font-size:12px; vertical-align:text-top; line-height:15px"> 
 <tbody> 
 <tr> 
 <td style="color:#008284; background-color:#e3e3e3; vertical-align:text-top"> 
 <div style="margin:7px; text-align:right; white-space:nowrap">
 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 
 </div> </td> 
 <td style="background-color:#008284; padding:1px"> 
 <div style="border:1px #008284 solid"></div> </td> 
 <td style="background-color:#efefef; width:100%; vertical-align:text-top; color:#000000"> 
 <div style="margin:7px"> 
 &lt;resultMap&nbsp;type= 
 "Student" 
 &nbsp;id= 
 "studentMapper" 
 &gt; 
 &nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "sid" 
 &nbsp;property= 
 "sid" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "sname" 
 &nbsp;property= 
 "sname" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;collection&nbsp;property= 
 "courses" 
 &nbsp;ofType= 
 "Course" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 &lt;id&nbsp;column= 
 "cid" 
 &nbsp;property= 
 "cid" 
 &nbsp;/&gt; 
 &nbsp;&nbsp;&nbsp;&nbsp; 
 &lt;result&nbsp;column= 
 "cname" 
 &nbsp;property= 
 "cname" 
 &nbsp;/&gt; 
 &nbsp;&nbsp; 
 &lt;/collection&gt; 
 
 &lt;/resultMap&gt; 
 
 &lt;select&nbsp;id= 
 "selectStudentById" 
 &nbsp;resultMap= 
 "studentMapper" 
 &gt; 
 &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;sid,sname,cid,cname&nbsp;from&nbsp;student,middle,course&nbsp;where&nbsp;sid=studentId&nbsp;and&nbsp;cid=courseId&nbsp;and&nbsp;sid=#{xxx} 
 
 &lt;/select&gt; 
 
 </div> </td> 
 </tr> 
 </tbody> 
</table>