MyBatis中关联关系查询sql写法

最新推荐文章于 2025-05-21 20:18:40 发布

原创最新推荐文章于 2025-05-21 20:18:40 发布 · 717 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SSM #MyBatis

SSM框架专栏收录该内容

19 篇文章

订阅专栏

本文介绍MyBatis中实现各种关联查询的方法，包括一对多、多对一、自关联及多对多关系的数据获取策略。文章详细展示了如何使用XML映射文件定义查询逻辑，以及如何通过连接查询和独立查询两种方式实现数据的高效加载。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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