13.高级映射（多对多查询）

1.需求

查询用户及用户购买的商品信息

2.sql

查询主表：用户表
关联表：由于用户和商品没有直接关联，通过订单和订单明细进行关联。
orders、orderdedail、items

SELECT orders.* ,
    user.username,
    user.sex,
    user.address,
    orderdetail.id orderdetail_id,   
    orderdetail.items_id,
    orderdetail.items_num,
    orderdetail.orders_id,
    items.name items_name,
    items.pic items_pic,
    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

3.映射思路

将用户信息映射到user中
在user类中添加定单列表属性List<Orders> orderslist,将用户创建的订单映射到orderslist
在Orders中添加订单明细列表属性List<OrderDetail> orderdetials
在OrderDetail中添加Items属性，将订单明细所对应的商品映射到Items

4.mapper.xml

<!--查询用户及购买的商品信息，使用resultMap-->
    <select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
        SELECT orders.* ,
                user.username,
                user.sex,
                user.address,
                orderdetail.id orderdetail_id,
                orderdetail.items_id,
                orderdetail.items_num,
                orderdetail.orders_id,
                items.name items_name,
                items.pic items_pic,
                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>

5.定义resultMap

    <resultMap id="UserAndItemsResultMap" type="com.chinglee.mybatis.pojo.User">
        <id column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        <!-- 一个用户多个订单-->
        <collection property="ordersList" ofType="com.chinglee.mybatis.pojo.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.chinglee.mybatis.pojo.OrderDetail">
                <id column="orderdetail_id" property="id"/>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>
                <result column="orders_id" property="ordersId"/>
                <!--一个明细对应一个商品-->
                <association property="items" javaType="com.chinglee.mybatis.pojo.Items">
                    <id column="items_id" property="id"/>
                    <result column="items_name" property="name"/>
                    <result column="items_pic" property="pic"/>
                    <result column="items_price" property="price"/>
                </association>
            </collection>
        </collection>

    </resultMap>

在User中添加ordersList属性

 public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    //多对多映射，一个用户多个订单
    private List<Orders> ordersList;

在OrderDetail中添加items属性

public class OrderDetail {
 private int id;
 private int ordersId;
 private  int itemsId;
 private int itemsNum;

 //多对多映射，一个明细对应一个商品
    private Items items;

6.OrderCustomMapper.java

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

7.测试

 @Test
    public void findUserAndItemsResultMap() throws Exception {
        SqlSession sqlSession=sqlSessionFactory.openSession();
        //创建代理对象
        OrderCustomMapper orderCustomMapper= sqlSession.getMapper(OrderCustomMapper.class);
        //调用mapper的方法
        List<User> list =orderCustomMapper.findUserAndItemsResultMap();
        System.out.println(list);
        sqlSession.close();
    }

8.总结