Mybatis(多对多查询)

需求：查询用户及用户所购买的商品信

1. 需要查询的主表是：用户表
2. 关联表：由于用户和商品没有直接关联，通过订单和订单明细进行关联，所以关联表：items orders orderdetails
   
   • sql语句

SELECT
    orders.*, USER . NAME,
    USER .address,
    USER .birthday,
    USER .sex,
    orderdetail.id orderdetail_id,
    orderdetail.items_id,
    orderdetail.items_num,
    orderdetail.ordes_id,
    items.id items_id,
    items.price items_price,
    items. NAME items_name,
    items.detail items_detail
FROM
    orders,
    USER,
    orderdetail,
    items
WHERE
    orders.user_id = USER .id
AND orderdetail.ordes_id = orders.id
AND orderdetail.items_id = items.id

• 重要：映射思路

将用户信息映射到user中，在user中添加订单列表的属性List orderslist，将用户创建的订单映射到orderslist在Orders中添加订单明细列表属性 Listorderdetails，将订单的明细映射到orderdetails在Ordertail添加items属性，将订单明细商品信息映射到items

• 在user中添加订单列表的属性List orderslist

//用户创建的订单列表
    private List<Orders> orderslist;

• 用户创建的订单映射到orderslist在Orders中添加订单明细列表属性

//订单明细
    private List<Orderdetail> orderDetails;

• 将订单的明细映射到orderdetails在Ordertail添加items属性

//商品信息 
    private Items items;

• 订单明细商品信息映射到items

• resultType定义

<!-- 查询用户及其购买的商品 -->
 <resultMap type = "com.shagou.mybatis.po.User" id ="UserAndItemsResultMap">
    <!-- 配置用户信息 -->
        <id column = "user_id" property = "id"/>
        <result column = "name" property = "name"/>
        <result column = "sex" property = "sex"/>
        <result column = "address" property = "address"/>
    <!-- 配置订单信息 -->
    <collection property = "orderslist" ofType = "com.shagou.mybatis.po.Orders">
        <id column = "id" property = "id"/>
        <result column = "user_id" property = "userId"/>
        <result column = "number" property = "number"/>
        <result column = "createtime" property = "createtime"/>
        <result column = "note" property = "note"/>
        <!-- 配置订单明细信息 -->
        <collection property = "orderDetails" ofType = "com.shagou.mybatis.po.Orderdetail">
            <id column = "orderdetail_id" property = "id"/>
            <result column = "orders_id" property = "orders_id"/>
            <result column = "items_id" property = "items_id"/>
            <result column = "items_num" property = "items_num"/>
        <!-- 配置商品信息 -->
            <association property = "items" javaType = "com.shagou.mybatis.po.Items">
                <id column = "items_id" property = "id"/>
                <result column = "items_name" property = "name"/>
                <result column = "items_price" property = "price"/>
                <result column = "items_detail" property = "detail"/>
                </association>
        </collection>
    </collection>
 </resultMap>

• 配置xml

<!-- 查询用户及其商品，使用resultMap -->
    <select id = "findUserAndItemsResultMap" resultMap = "UserAndItemsResultMap">
SELECT
    orders.*, USER . NAME,
    USER .address,
    USER .birthday,
    USER .sex,
    orderdetail.id orderdetail_id,
    orderdetail.items_id,
    orderdetail.items_num,
    orderdetail.ordes_id,
    items.id items_id,
    items.price items_price,
    items. NAME items_name,
    items.detail items_detail
FROM
    orders,
    USER,
    orderdetail,
    items
WHERE
    orders.user_id = USER .id
AND orderdetail.ordes_id = orders.id
AND orderdetail.items_id = items.id
    </select>

• 编写接口

//查村用户购买的商品
    public List<User> findUserAndItemsResultMap()throws Exception;

• 测试类

//查询用户购买的商品
    @Test
    public void findUserAndItemsResultMap() throws Exception {
        SqlSession sqlSession = SqlSessionFactory.openSession();
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        List<User> list = ordersMapperCustom.findUserAndItemsResultMap();
        System.out.println(list);
        sqlSession.close();
    }