Mybatis 多对多关系映射(二）

深碍√TFBOYSˉ_ 2023-10-05 15:22 15阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hseDIwMDgwODA4_size_16_color_FFFFFF_t_70][]

#### 如图所示： ####

（1） 一个用户User可以有多个订单列表
    （2） 一个订单Orders包括多个订单明细
    （3）一条订单明细Orderdetail包括一条商品信息

#### 查找用户购买的商品和订单及订单详情 ####

![20201209154443526.png][]

#### 实体类： ####

//用户类
    public class User {
        private int id;
        private String username;
        private Date birthday;
        private String gender;
        private String address;
    
        //    用户创建的订单列表
        private List<Orders> orders;
    
    .......................
    }
    
    
    /**
     * 商品
     */
    public class Items {
        private int id;
        private String name;
        private double price;
        private String detail;
        private String pic;
        private Date createtime;
    ..................
    }
    
    /**
     * 订单
     */
    public class Orders {
    
        private int id;
        private int userid;  //数据库表的字段 user_id
        private String number;
        private Date createtime;
        private String note;
    
        //一个订单包括多个订单明细  setXXX and getXXX
        private List<OrdersDetail> ordersDetails;
    
       ....
    }
    
    
    /**
     * 订单明细
     */
    public class OrdersDetail {
        private int id;
        private int ordersId;   //数据库表的字段 orders_id
        private int itemsId;  //数据库表的字段 items_id
        private int itemsNum;  //数据库表的字段 items_num
        //一条订单明细包括一条商品信息
        private Items item;
    .......
    }

#### UserMapper接口： ####

public interface UserMapper {
    
        public List<User> findUserItemResultMap();
    }

#### UserMapper.xml文件 ####

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
    <mapper namespace="org.hlx.mapper.UserMapper">
    
        <resultMap id="UserItemResultMap" type="User">
            
            <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="gender" property="gender"/>
            <result column="birthday" property="birthday"/>
            <result column="address" property="address"/>
    
            
            <collection property="orders" ofType="Orders" >
                <id column="id" property="id"/>
                <result column="user_id" property="userid"/>
                <result column="number" property="number"/>
                <result column="note" property="note"/>
                <result column="createtime" property="createtime"/>
    
                
                <collection property="ordersDetails" ofType="OrdersDetail">
                    <id column="id" property="id"/>
                    <result column="items_id" property="itemsId"/>
                    <result column="items_num" property="itemsNum"/>
                    <result column="orders_id" property="ordersId"/>
    
                    
                    <association property="item" javaType="Items">
                        <id column="id" property="id"/>
                        <result column="name" property="name"/>
                        <result column="price" property="price"/>
                        <result column="detail" property="detail"/>
                        <result column="createtime" property="createtime"/>
                    </association>
                </collection>
    
            </collection>
    
        </resultMap>
    
        <select id="findUserItemResultMap" resultMap="UserItemResultMap">
              select
                orders.*,
                `user`.id,
                `user`.address,
                `user`.username,
                `user`.birthday,
                orderdetail.id id,
                orderdetail.orders_id,
                orderdetail.items_id,
                orderdetail.items_num,
                items.id ,
                items.name,
                items.price,
                items.detail
                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>

#### 测试： ####

![20201209154939619.png][]

####  ####

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hseDIwMDgwODA4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201209154159467.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hseDIwMDgwODA4,size_16,color_FFFFFF,t_70
[20201209154443526.png]: https://img-blog.csdnimg.cn/20201209154443526.png
[20201209154939619.png]: https://img-blog.csdnimg.cn/20201209154939619.png