MyBatis 多表连接查询

浅浅的花香味﹌ 2021-11-22 16:28 423阅读 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();

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

![1422100-20190515215901238-879016357.png][]

# 二、通过 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>

![1422100-20190515215735069-1937880299.png][]

原文：https://blog.csdn.net/weidong\_y/article/details/80557941

转载于:https://www.cnblogs.com/panchanggui/p/10872506.html

[1422100-20190515215901238-879016357.png]: /images/20211122/a7d0838a02554cb89465713af4300ecf.png
[1422100-20190515215735069-1937880299.png]: /images/20211122/97944ac095724e4ea11d93770d42a6eb.png