MyBatis 多表连接查询

雨点打透心脏的1/2处 2021-06-26 16:06 552阅读 0赞

多表连接的两种方式（数据库逻辑模型）：

1.一对一关系

2.一对多关系

# 一、通过 resultMap 和 association 实现一对一关系 #

在 mapper.xml 文件里面的代码：

<resultMap type="com.pojo.TRecruitment" id="tRecruitmentCollegeResultMap">
    	<id property="id" column="id" />
    	<result property="title" column="title" />
    	<result property="litimg" column="litimg" />
    	<result property="publishedTime" column="published_time" />
    	<result property="author" column="author" />
    	<result property="collegeId" column="college_id" />
    	<result property="type" column="type" />
    	<result property="details" column="details" />
    	
    	
    	
    	
    	<association property="tCollege" javaType="com.pojo.TCollege" >
    		
    		<id property="collegeId" column="college_id" />
    		<result property="collegeName" column="college_name" />
    		<result property="collegeImg" column="college_img" />
    	</association>
    </resultMap>
    
    
    <select id="querytTRecruitmentResultMap" resultMap="tRecruitmentCollegeResultMap">
    	SELECT
    	r.id,
    	r.title,
    	r.litimg,
    	r.published_time,
    	r.author,
    	r.type,
    	r.details,
    	c.college_name
    	FROM
    	`t_recruitment` r
    	LEFT JOIN `t_college` c ON r.college_id = c.college_id
    </select>

在 mapper.java 文件里面写接口：

List<TRecruitment> querytTRecruitmentResultMap();

在对应的实体类中声明另外一个实体类：

![70][]

# 二、通过 resultMap 和 collection 实现一对多关系 #

xml 文件：

<resultMap type="User" id="UserAndOrdersResultMap">
    
    	
    	<id column="id" property="id" />
    	<result column="username" property="username" />
    	<result column="birthday" property="birthday" />
    	<result column="sex" property="sex" />
    	<result column="address" property="address" />
    
    	
    	<collection property="ordersList" ofType="Orders">
    		<id column="oid" property="id" />
    		<result column="user_id" property="userId" />
    		<result column="number" property="number" />
    		<result column="createtime" property="createtime" />
    	</collection>
    
    </resultMap>
    
    <select id="findUserAndOrders" resultMap="UserAndOrdersResultMap">
    	SELECT u.*, o.`id` oid, o.`number`, o.`createtime`
    	FROM USER u, orders o
    	WHERE u.`id` = o.`user_id`;
    </select>

![70 1][]

[70]: /images/20210626/c552660f5acb4d47ab6041934519b171.png
[70 1]: /images/20210626/ad2c2202c3d24bf1826addaee5a9ebe8.png