Mybatis多对多关联映射实例

电玩女神 2022-06-15 06:44 231阅读 0赞

# Mybatis多对多关联映射实例 #

需求：查询购买了商品的用户的信息和用户的订单信息以及订单的明细。

这句话有点绕，稍微捋一捋，假设天明在京东商城有过一次购物经历

这一次他买了三个旺仔小馒头和4瓶旺仔牛奶

现在的需求就是需要

（1）、查询出天明的信息

（2）、天明下的那个订单的信息

（3）以及订单里面购物清单的详细信息。

（4）、详细信息中商品的信息

#### 关联思路：       ####

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

#### sql语句 ####

SELECT
                o.id as orderId,
                o.note,
                o.createtime,
                o.number,
                o.user_id,
                u.address,
                u.sex,
                u.username,
                od.id AS orderdetail_id,
                od.items_id AS itemsId,
                od.items_num AS itemsNum,
                od.orders_id AS ordersID,
                i.createtime,
                i.detail,
                i.id AS itemID,
                i.name AS itemName
            FROM
                USER u,
                orders o,
                orderdetail od,
                items i
            WHERE
                o.user_id = u.id and o.id = od.orders_id AND od.items_id = i.id

#### mapper.xml ####

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.hl.mybatis.first.mapper.OrdersCustomMapper">
        
        <resultMap type="com.hl.myabtis.first.beas.User" id="ordersAndOrderdetailItemsResultMap">
            
            <id column="user_id" property="id"/>
            <result column="address" property="address"/>
            <result column="sex" property="sex"/>
            <result column="username" property="username"/>
            
            <collection property="orderlist" ofType="com.hl.myabtis.first.beas.Orders">
                <id column="orderId" property="id"/>
                <result column="user_id" property="user_id"/>
                <result column="number" property="number"/>
                <result column="createtime" property="createtime"/>
                <result column="note" property="note"/>
                        
                <collection property="orderdetails" ofType="com.hl.myabtis.first.beas.Orderdetail">
                    <id column="orderdetail_id" property="id"/>
                    <result column="itemsId" property="items_id"/>
                    <result column="itemsNum" property="items_number"/>
                    <result column="ordersID" property="orders_id"/>
                    
                    <association property="items" javaType="com.hl.myabtis.first.beas.Items">
                        <id column="itemID" property="id"/>
                        <result column="itemName" property="name"/>
                        <result column="price" property="price"/>
                        <result column="detail" property="detail"/>
                        <result column="pic" property="pic"/>
                        <result column="createtime" property="createtime"/>
                    </association>
                </collection>
            </collection>
            
    
            
        </resultMap>
        
        <select id="findOrdersAndOrderdetailAndItemsResultMap" resultMap="ordersAndOrderdetailItemsResultMap">
            SELECT
                o.id as orderId,
                o.note,
                o.createtime,
                o.number,
                o.user_id,
                u.address,
                u.sex,
                u.username,
                od.id AS orderdetail_id,
                od.items_id AS itemsId,
                od.items_num AS itemsNum,
                od.orders_id AS ordersID,
                i.createtime,
                i.detail,
                i.id AS itemID,
                i.name AS itemName
            FROM
                USER u,
                orders o,
                orderdetail od,
                items i
            WHERE
                o.user_id = u.id and o.id = od.orders_id AND od.items_id = i.id
        </select>   
    </mapper>

**层层嵌套，不要迷糊！**