当前位置：首页 > news >正文

Mybatis——一对多关联映射

news 来源：原创 2024/5/17 12:26:45

一对多关联映射

一对多关联映射有两种方式，都用到了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<Order> 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.pricefrom user uleft join orders o on u.id = o.user_idwhere 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 uleft join t_order o on u.user_id = o.user_idwhere 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>