mabatis多表联查的映射嵌套

1.表和实体类

现有用户user,订单order,订单明细orderdetail,商品product。

2.表关系

user和order一对多

order和orderdetail一对多

orderdetail和product一对一

3.多表联查要求

如有以下多表联查要求:

根据用户id查询用户信息、订单信息、订单明细信息以及商品信息。

得出sql语句如下:

 select tb_product.id pid, tb_product.name productname, tb_product.price pprice, tb_product.description, hh.*
        from tb_product
                 inner join
             (select odd.id oddid, odd.productid, odd.num, odd.price, ou.*
              from tb_orderdetail odd
                       INNER JOIN
                   (select tb_order.id oid,
                           tb_order.userid,
                           tb_order.createtime,
                           tb_order.state,
                           u.id        uid,
                           u.username,
                           u.password,
                           u.sex,
                           u.brithday,
                           u.address
                    from tb_order
                             inner JOIN
                             (select tb_user.* from tb_user where id = #{id}) u
                             on tb_order.userid = u.id) ou
                   ON odd.ordereid = ou.oid) hh
             on tb_product.id = hh.productid

4.实体类代码:

1.user实体类
 

/**
 * @author ${杨威}
 * @version 2024/06/19 09:34
 **/
import lombok.Data;

import java.util.Date;
import java.util.List;

@Data
public class User {
    private Integer id;
    private String username;
    private String password;
    private Character sex;
    private Date birthday;
    private String address;
    //关联属性 订单
    private List<Order> orders;
}

2.order实体类

/**
 * @author ${杨威}
 * @version 2024/06/19 09:33
 **/
import lombok.Data;

import java.util.Date;
import java.util.List;

@Data
public class Order {
    private Integer id;
    private Integer userId;
    private Date createTime;
    private String state;

//    //关联属性 用户
//    private User user;

    //关联属性 订单明细
    private List<OrderDetail> orderDetails;


}

3.orderdetail实体类

/**
 * @author ${杨威}
 * @version 2024/06/19 09:34
 **/
import lombok.Data;

import java.util.List;

@Data
public class OrderDetail {
    private Integer id;
    private Integer productId;
    private Integer orderId;
    private Integer num;
    private Double price;

//    //关联属性 订单
//    private Order order;

    //关联属性 商品
    private Product products;



}

4.product实体类

import lombok.Data;

import java.util.List;

/**
 * @author ${杨威}
 * @version 2024/06/19 09:31
 **/

@Data
public class Product {
    private Integer id;
    private String name;
    private Double price;
    private String description;

//    //集合关联属性 订单明细
//    private List<OrderDetail> orderDetails;
}

5.userMapper.xml映射文件思路

对每个实体类都写一个resultMap映射，然后如果一个实体类A有个其他实体类B属性映射，如果是单映射属性就用<assosiation>,若是集合映射属性就用<collection>标签，最后加个属性resultMap引入实体类B的resultMap。

6.userMapper.xml映射文件代码

<?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="com.yw.mapper.UserMapper">




    <resultMap id="userProductMap" type="com.yw.entity.Product">
            <id column="pid" property="id"/>
            <result column="productname" property="name"/>
            <result column="pprice" property="price"/>
            <result column="description" property="description"/>
    </resultMap>

    <resultMap id="userOderDetialMap" type="com.yw.entity.OrderDetail">
        <id column="oddid" property="id"/>
        <result column="productId" property="productId" />
        <result column="num" property="num"/>
        <result column="price" property="price"/>
        <!-- 商品信息映射 -->
        <association property="products" javaType="com.yw.entity.Product" resultMap="userProductMap">
<!--            <id column="pid" property="id"/>-->
        </association>
    </resultMap>

    <resultMap id="userOderMap" type="com.yw.entity.Order">
        <id column="oid" property="id"/>
        <result column="userId" property="userId" />
        <result column="createTime" property="createTime"/>
        <result column="state" property="state"/>
        <collection property="orderDetails" ofType="com.yw.entity.OrderDetail" resultMap="userOderDetialMap">
<!--            <id column="oddid" property="id"/>-->
        </collection>
    </resultMap>

    <resultMap id="userInfoMap" type="com.yw.entity.User">
        <!--user属性映射-->
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="sex" property="sex"/>
        <result column="brithday" property="birthday"/>
        <result column="address" property="address"/>
        <!--集合关联属性的映射 orders   结果集与集合元素映射
            标签: collection
              属性:
                property: 关联属性名
                ofType: 集合元素类型
        -->
        <collection property="orders" ofType="com.yw.entity.Order" resultMap="userOderMap">
<!--            <id column="oid" property="id"/>-->
        </collection>
    </resultMap>
    <select id="selectUserInfo" resultMap="userInfoMap">
        select tb_product.id pid, tb_product.name productname, tb_product.price pprice, tb_product.description, hh.*
        from tb_product
                 inner join
             (select odd.id oddid, odd.productid, odd.num, odd.price, ou.*
              from tb_orderdetail odd
                       INNER JOIN
                   (select tb_order.id oid,
                           tb_order.userid,
                           tb_order.createtime,
                           tb_order.state,
                           u.id        uid,
                           u.username,
                           u.password,
                           u.sex,
                           u.brithday,
                           u.address
                    from tb_order
                             inner JOIN
                             (select tb_user.* from tb_user where id = #{id}) u
                             on tb_order.userid = u.id) ou
                   ON odd.ordereid = ou.oid) hh
             on tb_product.id = hh.productid


    </select>
</mapper>