MyBatis——第3章关联关系查询（一）

最新推荐文章于 2025-01-12 23:45:59 发布

Sachiel.

最新推荐文章于 2025-01-12 23:45:59 发布

阅读量204

点赞数

CC 4.0 BY-SA版权

分类专栏： SSM

本文链接：https://blog.youkuaiyun.com/Curry7895/article/details/82794677

SSM 专栏收录该内容

20 篇文章

订阅专栏

本文详细解析了MyBatis中的一对多、多对一、自关联及多对多关联查询方法，包括多表连接查询和延迟加载两种方式，并通过具体示例展示了如何使用XML配置实现这些查询。

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

外键肯定定义在多方表，有外键肯定充当在多方的。

3.1.1 一对多关联查询

一方可以看见多方，这里的一对多查询是指，在查询一方对象的时候，同时也将其关联的多方对象也都查询出来。

方式一：多表连接查询

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjpowernode.dao.ICountryDao">
	
	<resultMap type="Country" id="countryMapper">
		<id column="cid" property="cid"/>
		<result column="cname" property="cname"/>
		<collection property="minsters" 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 cid = countryId 
		and cid = #{id} 
	</select>
</mapper>

方式二：多表单独查询方式（用的比较多，延迟查询）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjpowernode.dao.IStudentDao">

	<select id="selectMinisterByCountry" resultType="Minister">
		select mid,mname by minister where mid = #{cid}
	</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 = 2
	</select>
</mapper>

<resultMap>:映射信息

type:所选的类名（一对多中一代表的类名）

<id>: 代表主键

column：表中字段

property：类中属性

<result> 其他键（属性）

<collection> 一对多中的多

property：代表一对多中的多的属性

ofType：集合集合成的类名

select ：单独查询的另一个选择

column：两个单独查询的相交点（关联属性）

3.1.2 多对一查询

方式一：多表连接查询

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjpowernode.dao.IMinisterDao">

	<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 cid = countryId
		and mid = #{mid}
	</select>
	
</mapper>

多对一的<association> 类比一对多的<collection>

方式二：多表单独查询

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjpowernode.dao.IMinisterDao">

	<select id="selectCountryById" resultMap="Country">
		select cid,cname from country where cid = #{countryId}
	</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
		from minister
		where mid = #{mid}
	</select>
	
</mapper>

关键是column=“countryId” 找到两个查询之间的关联点。

3.1.3 自关联查询

（1）以一对多方式处理

A、查询指定栏目的所有子孙栏目

递归调用

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjpowernode.dao.INewsLabelDao">

<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=#{id}
</select>
	
</mapper>

B、查询指定栏目及其所有子孙栏目

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjpowernode.dao.INewsLabelDao">

<select id="selectNewslabelByParent" resultMap="newsLabelMapper">
	select id,name from newslabel where pid = #{id}
</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=#{id}
</select>
	
</mapper>

(2)以多对一方式来处理

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjpowernode.dao.INewsLabelDao">


<resultMap type="NewsLabel" id="newslabelMapping">
	<id column="id" property="id"/>
	<result column="name" property="name"/>
	<association property="parent"
		javaType="NewsLabel"
		select ="selectNewsLabelById"
		column="pid"/>
</resultMap>


<select id="selectNewsLabelById" resultMap="newslabelMapping">
	select id,name,pid from newslabel where id = #{pid}
</select>
	
</mapper>

3.1.4 多对多关联查询

两个一对多构成的

中间件middle为多，student和course为一

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bjpowernode.dao.IStudentDao">


<resultMap type="Student" id="studentMapping">
	<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="selectNewsLabelById" resultMap="studentMapping">
	select sid,sname,cid,cname
	from student,middle,course
	where sid = studentId and cid = courseId and sid=#{sid}
</select>
	
</mapper>

多对多转换为一对多，采用多表连接查询方式解决。