高级映射-多对多查询

1.需求
查询用户及用户购买商品信息

2.思路

• 将用户信息映射到user中。
• 在UserBean类中添加订单列表属性List<OrdersBean> ordersBean，将用户创建的订单映射到ordersBean。
• 在OdersBean中添加订单明细列表属性List<OrderDetailBean> orderDetailBeans，将订单的明细映射到orderDetailBeans。
• 在OrderDetail中添加items属性ItemsBean itemsBean，将订单明细所对应的商品映射到itemsBean。

3.实现
（1）配置映射

将用户信息映射到user中

在Oders中添加订单明细列表

在OrderDetail中添加items属性

（2）配置文件
OrdersMapperCustom.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.OrdersMapperCustom">

    <!--查询用户及用户购买商品信息-->
    <resultMap id="UserAndItemsResultMap" type="entity.UserBean">
        <!--用户信息-->
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
        <!--订单信息-->
        <collection property="ordersBeans" ofType="entity.OrdersBean">
            <id column="id" property="id"></id>
            <result column="user_id" property="user_id"></result>
            <result column="number" property="number"></result>
            <result column="createtime" property="createtime"></result>
            <result column="note" property="note"></result>
            <!--订单明细-->
            <collection property="orderdetailBeans" ofType="entity.OrderdetailBean">
                <id column="orderdetail_id" property="id"></id>
                <result column="items_id" property="orders_id"></result>
                <result column="items_id" property="items_id"></result>
                <result column="orders_id" property="orders_id"></result>
                <!--商品信息-->
                <association property="itemsBean" javaType="entity.ItemsBean">
                <id column="items_id" property="id"></id>
                    <result column="items_name" property="name"></result>
                    <result column="items_detail" property="detail"></result>
                    <result column="items_price" property="price"></result>
                </association>
            </collection>
        </collection>
    </resultMap>
 
    <!--查询用户及用户购买商品信息-->
    <select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
  SELECT
 orders.*,
 user.username,
 user.sex,
 user.address,
 orderdetail.id orderdetail_id,
 orderdetail.items_id,
 orderdetail.item_num,
 orderdetail.orders_id,
 items.name items_name,
 items.detail items_detail,
 items.price items_price
FROM
 orders,
USER,
 orderdetail,
 items
WHERE
 orders.user_id = user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id
    </select>
</mapper>

（3）OrdersMapperCustom.java接口

public List<UserBean> findUserAndItemsResultMap() throws Exception;

（4）测试

  @Test
    public void findUserAndItemsResultMap() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        List<UserBean> userBeans  = ordersMapperCustom.findUserAndItemsResultMap();
        System.out.println(userBeans);
    }

测试结果