MyBatis之【表的关联关系】

一、一对多关联

在一对多关联关系中，一方(客户)中有多方(订单)的集合 ，所以要使用<collection>标签来映射多方的属性。

1、示例表如下图：

2、方法一

（1）CustomerMapper接口

public interface CustomerMapper {

    // 实现通过查询顾客id，显示顾客及其订单
    Customer getById(int id);
}

（2）CustomerMapper.xml

<mapper namespace="com.jht.mapper.CustomerMapper">
    <resultMap id="customerMap" type="customer">
        <!-- 绑定主键 -->
        <id property="id" column="cid"></id>
        <!-- 绑定非主键 -->
        <result property="name" column="name"></result>
        <result property="age" column="age"></result>
        <!-- 绑定非表中属性 -->
        <collection property="ordersList" ofType="orders">
            <!-- 绑定主键 -->
            <id property="id" column="oid"></id>
            <!-- 绑定非主键 -->
            <result property="orderNumber" column="orderNumber"></result>
            <result property="orderPrice" column="orderNumber"></result>
        </collection>
    </resultMap>
    <select id="getById" parameterType="int" resultMap="customerMap">
        select c.id cid,name,age,o.id oid,orderNumber,orderPrice,customer_id
        from customer c left join orders o on c.id = o.customer_id
        where c.id = #{id}
    </select>
</mapper>

（3）测试

    @Test
    public void testGetById(){
        Customer cus = customerMapper.getById(3);
        System.out.println(cus);
    }

3、方法二

（1）CustomerMapper接口

public interface CustomerMapper {

    // 实现通过查询顾客id，显示顾客及其订单
    Customer getById(int id);

    // 方法二
    Customer getById2(int id);
}

（2）CustomerMapper.xml

    <resultMap id="customerMap2" type="customer">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="age" column="age"></result>
        <collection property="ordersList" ofType="orders" column="id" select="selectOrdersByCustomerId"/>
    </resultMap>
    <select id="getById2" parameterType="int" resultMap="customerMap2">
        select * from customer where id=#{id}
    </select>
    <select id="selectOrdersByCustomerId" parameterType="int" resultType="orders">
        select * from orders where customer_id = #{id}
    </select>

（3）测试

    @Test
    public void testGetById2(){
        Customer cus = customerMapper.getById2(1);
        System.out.println(cus);
    }

4、方法三（推荐方法）【每个mapper里只有自己的增删改查】

（1）CustomerMapper接口

public interface CustomerMapper {

    // 实现通过查询顾客id，显示顾客及其订单
    Customer getById(int id);

    // 方法二
    Customer getById2(int id);

    // 方法三
    Customer getById3(int id);
}

（2）CustomerMapper.xml

    <resultMap id="customerMap3" type="customer">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="age" column="age"></result>
        <collection property="ordersList" ofType="orders" column="id" select="com.jht.mapper.OrdersMapper.getByOrdersId"></collection>
    </resultMap>
    <select id="getById3" parameterType="int" resultMap="customerMap3">
        select * from customer where id=#{id}
    </select>

（3）OrdersMapper.xml

    <select id="getByOrdersId" resultType="orders" parameterType="int">
        select * from orders where customer_id=#{id}
    </select>

（4）测试

    @Test
    public void testGetById3(){
        Customer cus = customerMapper.getById3(1);
        System.out.println(cus);
    }

二、多对一关联

在多对一关联关系中，多方(订单)中持有一方(客户)的对象，要使用标签<association>标签来映射一方的属性。 

（1）OrdersMapper接口

public interface OrdersMapper {
    // 通过商品id查询该订单信息
    Orders getByOrdersId(Integer id);

    // 通过商品id查询该订单的购买人信息
    Orders getBuyer(Integer id);
}

（2）OrdersMapper.xml

    <select id="getByOrdersId" resultType="orders" parameterType="int">
        select * from orders where customer_id=#{id}
    </select>

    <resultMap id="ordersMap" type="orders">
        <id property="id" column="id"></id>
        <result property="orderName" column="orderName"></result>
        <result property="orderPrice" column="orderPrice"></result>
        <association property="customer" javaType="customer" column="customer_id" select="com.jht.mapper.CustomerMapper.getAll"/>
    </resultMap>
    <select id="getBuyer" parameterType="int" resultMap="ordersMap">
        select * from orders where id=#{id}
    </select>

（3）CustomerMapper.xml

    <!-- 嵌套查询 -->
    <select id="getAll" parameterType="int" resultType="customer">
        select * from customer where id = #{id}
    </select>

（4）测试

    @Test
    public void testGetBuyer(){
        Orders ors = ordersMapper.getBuyer(11);
        System.out.println(ors);
    }

三、一对一关联

四、多对多关联

多对多关联中，需要通过中间表化解关联关系。中间表描述两张主键表的关联。中间表没有对应的实体类。Mapper.xml文件中也没有中间表的对应标签描述，只是在查询语句中使用中间表来进行关联。

（1）BookMapper接口

public interface BookMapper {
    // 查询全部图书，并且查询每本书所属的类型
    List<Book> findAll();
}

（2）BookMapper.xml

<mapper namespace="com.jht.mapper.BookMapper">
    <resultMap id="bookMap" type="book">
        <id property="bid" column="bid"></id>
        <result property="bname" column="bname"></result>
        <collection property="categoryList" ofType="category">
            <id property="cid" column="cid"></id>
            <result property="cname" column="cname"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="bookMap">
        select *
            from book b inner join middle m on b.bid = m.m_bid
            inner join category c on c.cid = m.m_cid
    </select>
</mapper>

（3）测试

    @Test
    public void testFindAll(){
        List<Book> books = bookMapper.findAll();
        books.forEach(i -> System.out.println(i));
    }

五、总结

总结：无论是什么关联关系，如果某方持有另一方的集合，则使用<collection>标签完成映射，如果某方持有另一方的对象，则使用<association>标签完成映射。