Mybatis——一对多关联映射

一对多关联映射

一对多关联映射有两种方式，都用到了collection元素

以购物网站中用户和订单之间的一对多关系为例

collection集合的嵌套结果映射

创建两个实体类和映射接口

package org.example.demo;
 
import lombok.Data;
 
import java.util.List;
 
@Data
public class User {
    private Integer userId;
    private String userName;
    private String password;
    private Cart cart;
    private List orderList;
}

package org.example.demo;
 
import lombok.Data;
 
@Data
public class Order {
    private Integer orderId;
    private double price;
}

实现根据用户id查询出所有用户信息，包括该用户的所有订单信息 

package org.example.mapper;
 
import org.example.demo.User;
 
import java.util.List;
 
public interface UserMapper {
 
    User findUserAndOrderListByUserId(Integer userId);
}

方式一：与association类似，集合的嵌套结果映射就是指通过一次SQL查询得到所有的结果 

<resultMap type="com.mybatis.entity.User" 
        id="userMap">
        <id property="id" column="id"/>
    <result property="userName" column="user_name"/>
    <result property="password" column="password"/>
resultMap>
<resultMap type="com.mybatis.entity.User" 
    id="userAndOrderListMap" extends="userMap">
    <collection property="orderList" 
        ofType="com.mybatis.entity.Order">
        <id property="id" column="order_id"/>
        <result property="price" column="price"/>
    collection>
resultMap>
<select id="findUserAndOrderListById" 
    resultMap="userAndOrderListMap">
    select u.id, u.user_name, u.password,
    o.order_id, o.price
    from user u
    left join orders o on u.id = o.user_id
    where u.id = #{id}
select>

resultMap元素中的extends属性可以实现结果映射的继承

collection的ofType属性指定集合中元素的类型，必选项 

    <resultMap id="userAndOrderMap" type="org.example.demo.User">
        <id property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="password" column="password"/>
        <collection property="orderList" ofType="org.example.demo.Order">
            <id property="orderId" column="order_id"/>
            <result property="price" column="price"/>
        collection>
    resultMap>
    <select id="findUserAndOrderListByUserId" resultMap="userAndOrderMap">
        select
            *
        from t_user u
        left join t_order o on u.user_id = o.user_id
        where u.user_id = #{userId};
    select>

 

collection集合的嵌套查询

集合的嵌套查询同样会执行额外的SQL查询

<resultMap type="com.mybatis.entity.User"
    id="userAndOrderListMap" extends="userMap">
    <collection property="orderList" column="{uid=id}"
        ofType="com.mybatis.entity.Order"       select="com.mybatis.mapper.OrderMapper.findOrdersByUserId">
    collection>
resultMap>
<select id="findUserAndOrderListById" 
    resultMap="userAndOrderListMap">
    select * from user where id = #{id}
select>

OrderMapper.xml

<resultMap type="com.mybatis.entity.Order"
    id="orderMap">
    <id property="id" column="order_id"/>
    <result property="price" column="price"/>
resultMap>
<select id="findOrdersByUserId"
    resultMap="orderMap">
    select * from orders where user_id = #{uid}
select>

对比两种方式

第一种方式属于“关联的嵌套结果映射“,即通过一次SQL查询根据表或指定的属性映射到不同的对象中

第二种方式属于“关联的嵌套查询”，利用简单的SQL语句，通过多次查询得到想要的结果，也可以实现延迟加载效果