Mybatis关联查询

用户表user：
订单表：orders
订单明细表：orderdetail：
商品表：items

user和orders：
user—->orders：一个用户可以创建多个订单，一对多
orders—>user：一个订单只由一个用户创建，一对一

orders和orderdetail：
orders—>orderdetail：一个订单可以包括 多个订单明细，一对多
orderdetail–> orders：一个订单明细只能包括在一个订单中，一对一

orderdetail和items：
orderdetail—>itesms：一个订单明细只对应一个商品信息，一对一
items–> orderdetail:一个商品可以包括在多个订单明细 ，一对多

1.查询订单信息，关联查询创建订单的用户信息
一对一：orders—>user （一个订单只由一个用户创建）
1.1使用 ResultType

实体类

//继承Order然后添加User属性
public class OrdersCustom extends Orders{
    private String username;
    private String sex;
    private String address;
    /* get set方法*/
}

接口

    public List<OrdersCustom>findOrderUser()throws Exception;

xml

    <select id="findOrderUser" resultType="cn.itcast.mybatis.pojo.OrdersCustom">
    SELECT
        orders.* ,
        USER.username,
        USER.sex,
        USER.address
    FROM
        orders,  
        USER 
    where 
        orders.user_id=user.id
    </select>

1.2 使用ResultMap
实体类

//继承Order然后添加User属性
public class OrdersCustom extends Orders{
    private String username;
    private String sex;
    private String address;
    /* get set方法*/
}

接口

    public List<Orders>findOrdersUserResultMap()throws Exception;

xml

<resultMap type="cn.itcast.mybatis.pojo.Orders" id="OrdersUserResultMap">
    <id column="id" property="id"/>
    <result column="user_id" property="userId"/> 
    <result column="number" property="number"/>
    <result column="createtime" property="createtime"/>
    <result column="note" property="note"/>
    
  <association property="user"  javaType="cn.itcast.mybatis.pojo.User">
            <id column="user_id" property="id"/> 
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>   
  </association> 
</resultMap>

 <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
    SELECT
        orders.* ,
        USER.username,
        USER.sex,
        USER.address
    FROM
        orders,
        USER 
    WHERE 
        orders.user_id=user.id
</select>

2.查询订单关联的订单明细信息
一对多：orders–>orderdetails（一个订单有多个明细）
实体类

//表orders
public class Orders {
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;

    //添加user用户属性信息
    private User user;

    //添加订单明细 Orderdetail的属性信息
    private List<Orderdetail>orderdetails;
   
   /* get set方法*/    
}

接口

    public List<Orders>findOrdersAndOrderDetailResultMap()throws Exception;

xml

<resultMap type="cn.itcast.mybatis.pojo.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">
    <collection property="orderdetails" ofType="cn.itcast.mybatis.pojo.Orderdetail">
        <id column="orderdetail_id" property="id"/>
        <result column="items_id" property="itemsId"/>
        <result column="items_num" property="itemsNum"/>
        <result column="orders_id" property="ordersId"/>
    </collection>     
</resultMap>

<select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
    SELECT
        orders.* ,
        USER.username,
        USER.sex,
        USER.address,
        orderdetail.id orderdetail_id,<!--别名-->
        orderdetail.orders_id,
        orderdetail.items_id,
        orderdetail.items_num
    FROM
        orders,
        USER,
        orderdetail 
    WHERE 
        orders.user_id=user.id AND orderdetail.orders_id=orders.id
</select>

3.查询用户及用户购买的商品信息
多对多 user—>itesms（一个用户可以购买多个商品，一个商品可以被多个用户购买）

实体类

public class User{
    private String username;
    private String sex;
    private String address;
   
    //添加订单属性
    private List<Orders>orderList;
  
    /* get set方法*/
}

public class Orders{
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;

    //添加user用户属性信息
    private User user;

    //添加订单明细 Orderdetail的属性信息
    private List<Orderdetail>orderdetails;
}

public class Orderdetail{
   
    /* ··· */

    //添加商品属性信息
    private Items items
}

接口

    public List<User>findUserAndItemsResultMap()throws Exception;

xml

<!-- 查询用户和购买商品的resultMap -->
<resultMap type="cn.itcast.mybatis.pojo.User" id="UserAndItemsResultMap">
<!-- (1) 配置用户信息 --> 
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>       
<!-- (2) 配置订单信息,一个用户对应多个订单，使用collection映射 -->     
 <collection property="orderList" ofType="cn.itcast.mybatis.pojo.Orders">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
    <!-- (3) 配置订单明细信息 ,一个订单包括 多个订单明细 -->       
    <collection property="orderdetails" ofType="cn.itcast.mybatis.pojo.Orderdetail">
            <id column="orderdetail_id" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
            <result column="orders_id" property="ordersId"/>        
            <!-- (4) 配置商品信息,一个订单明细对应一个商品-->
            <association property="items" javaType="cn.itcast.mybatis.pojo.Items">
                <id column="items_id" property="id"/>
                <result column="items_name" property="name"/>
                <result column="items_detail" property="detail"/>
                <result column="items_price" property="price"/>
            </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.items_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>