Mybatis(一对多查询)

需求：查询订单及订单明细的信息

• sql语句
  
  1. 确定主表：订单表
  2. 确定馆关联查询表：订单明细表
  3. 在一对一基础查询之上添加订单明细表即可

SELECT
    orders.*, USER . NAME,
    USER .address,
    USER .birthday,
    USER .sex,
    orderdetail.id orderdetail_id,
    orderdetail.items_id,
    orderdetail.items_num,
    orderdetail.ordes_id
FROM
    orders,
    USER,
    orderdetail
WHERE
    orders.user_id = USER .id
AND orderdetail.ordes_id = orders.id

使用resultType将上边的查询结果集映射到pojo中，订单信息的就是重复，对orders映射不能出现重复记录
- 在orders.java类中添加List orderDetails属性
最终会将订单信息映射到orders中，订单所对应的订单明细映射到orders中的orderDetails属性中，在orders中添加list订单明细属性

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

• 定义resultType

<!-- 订单以及订单明细
      使用extends继承，不用再配置用户及订单信息
  -->
 <resultMap type = "com.shagou.mybatis.po.Orders" id ="OrdersAndOrderDetailResultMap" extends ="OrdersUserResultMap">
        <!-- 订单明细信息 
            一个订单关联查询出了多条明细，要使用collection进行映射
            collection：对关联查询到多条记录映射到集合对象中
            property：将关联查询到多条记录映射到com.shagou.mybatis.po.Orders哪个属性
            ofType：指定映射list集合属性中pojo类型
        -->

        <collection property = "orderDetails" ofType = "com.shagou.mybatis.po.Orderdetail">
            <!-- id:订单明细唯一标识
                property：要将订单明细的唯一标识映射到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"/>
        </collection>
 </resultMap>

• 配置xml

<!-- 查询订单关联查询用户及订单明细，使用resultMap -->
    <select id = "findOrdersAndOrderDetailResultMap" resultMap = "OrdersAndOrderDetailResultMap">
SELECT
    orders.*, USER . NAME,
    USER .address,
    USER .birthday,
    USER .sex,
    orderdetail.id orderdetail_id,
    orderdetail.items_id,
    orderdetail.items_num,
    orderdetail.ordes_id
FROM
    orders,
    USER,
    orderdetail
WHERE
    orders.user_id = USER .id
AND orderdetail.ordes_id = orders.id
    </select>

• 编写接口

//查询订单关联查询用户查询订单明细
    public List<Orders> findOrdersAndOrderDetailResultMap()throws Exception;

• 测试类

//关联查询订单信息及订单明细
    @Test
    public void findOrdersAndOrderDetailResultMap() throws Exception {
        SqlSession sqlSession = SqlSessionFactory.openSession();
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        List<Orders> list = ordersMapperCustom.findOrdersAndOrderDetailResultMap();
        System.out.println(list);
        sqlSession.close();
    }

小结：

mybatis使用collection对关联查询的多条记录映射到一个list集合属性中