Mybatis 多对多关系映射(二）

如图所示：

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

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

实体类：

//用户类
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>

测试：