高级映射-一对多查询-多对多查询

一 一对多查询-概述

1 需求

• 查询订单及订单明细

2 sql语句：

• 确定主查询表：订单表 orders
• 确定关联查询表：订单明细表 Orderdetail

    SELECT
     orders.*,
     user.username,
     user.sex,
     user.address,
     orderdetail.id orderdetail_id,
     orderdetail.items_id,
     orderdetail.item_num,
     orderdetail.orders_id
    FROM
     orders,
     USER,
     orderdetail
    WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id

sql语句执行结果

3 要求

• 对orders映射不能出现重复记录

4 思路

• 在orders.java类中添加List<orderDetail>orderDetail属性
• 最终会将订单信息映射到orders中，订单所对应的订单明细映射到orders中的ordersDetail属性中。
• 映射成的orders记录数为三条（orders信息不重复）
• 每个orders中的orderDetails属性存储了该订单所对应的订单明细

二 一对多查询-实现

1 在orders.java类中添加List<OrderdetailBean>OrderdetailBean属性，并生成get和set方法

OrdersBean添加List集合

2 OrdersMapperCustom

<?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-->
    <resultMap id="OrdersUserResultMap" type="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>
        <!--配置映射的关联的用户信息-->
        <!--association:用于映射关联查询单个对象的信息
        property:要将关联查询的用户信息映射到Orders中哪个属性-->
        <association property="userBean" javaType="entity.UserBean">
            <!--id:关联查询用户的唯一标识
            column：指定唯一标识用户信息的列
            javaType：映射到user的那个属性-->
            <id column="user_id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="sex" property="sex"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>
    <!--查询订单关联用户及订单明细，使用resultMap-->
    <resultMap id="OrdersAndOrderdetailResultMap" type="entity.OrdersBean" extends="OrdersUserResultMap">
        <!--订单信息-->
        <!--用户信息-->


        <!--明细信息
        一个订单关联查询出多条明细，要使用collection进行映射
        collection：对关联查询到的多条记录映射到集合对象
        property：将关联查询到的多条记录映射到OrdersBean属性
        ofType:指定映射到集合属性的poio的类型
        -->
        <collection property="orderdetailBeans" ofType="entity.OrderdetailBean">
            <!--id:订单明细唯一标识-->
            <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>
        </collection>
    </resultMap>
    <!--查询订单关联查询用户信息，使用resultType-->
    <select id="findOrdersUser" resultType="pojo.OrdersCustom">
    SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
    <!--查询订单关联查询用户信息，使用resultMap-->
    <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
    SELECT orders.*,user.username,user.sex,user.address FROM user,orders WHERE orders.user_id = user.id
</select>
    <!--查询订单关联用户及订单明细，使用resultMap-->
    <select id="findOrdersAndOrderdetailResultMap" resultMap="OrdersAndOrderdetailResultMap">
    SELECT
     orders.*,
     user.username,
     user.sex,
     user.address,
     orderdetail.id orderdetail_id,
     orderdetail.items_id,
     orderdetail.item_num,
     orderdetail.orders_id
    FROM
     orders,
     USER,
     orderdetail
    WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
    </select>
</mapper>

3 OrdersMapperCustom接口

public List<OrdersBean> findOrdersAndOrderdetailResultMap() throws Exception;

4 OrdersMapperTest 测试

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

测试结果

三 多对多查询-概述

1 需求

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

2 sql语句

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

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

3 思路

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

四 多对多查询-实现

1 配置映射

将用户信息映射到user中
在Oders中添加订单明细列表
在OrderDetail中添加items属性

2 OrdersMapperCustom

<?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 接口

    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);
    }

执行结果