复习第四天

1. 多表关联查询

1. resultMap的基础使用场景

解决实体与结果集的映射

<resultMap id="usersMapper" type="com.bjsxt.pojo.users">
    <id property="userid" column="id"/>
    <result property="username" column="name"/>
    <result property="usersex" column="sex"/>
</resultMap>

2. 标签 一对一的关联查询

// id代表resultMap的主键，而result代表其属性。
// property:关联对象的属性
// javaType:关联对象的类型(可以省略)
// column:在新的查询中用哪个列的值作为查询条件
<resultMap id="usersAndRolesMapper" type="com.bjsxt.pojo.Users">
    <id property="userid" column="id"/>
    <result property="username" column="name"/>
    <result property="usersex" column="sex"/>
    <association property="roles" javaType="com.bjsxt.pojo.Roles">
        <id property="roleid" column="roleid"/>
        <result property="rolename" column="rolename"/>
    </association>
</resultMap>
<select id="selectUsersAndRoles" resultMap="usersAndRolesMapper">
    select u.userid id, u.username name, u.usersex sex
    from users as u ,roles as r where u.userid = r.user_id and u.userid = #{userid}
</select>

3. 标签 一对多的关联查询

// property指定关联对象的属性
// javaType关联对象的类型
// ofType指定集合里存放的对象类型
// column在新的查询中用哪个列的值作为查询条件
<resultMap id="usersAndOrdersMapper" type="com.bjsxt.pojo.Users">
    <id property="userid" column="userid"/>
    <result property="username" column="username"/>
    <result property="usersex" column="usersex"/>
 
    <collection property="orders" ofType="com.bjsxt.pojo.Orders">
        <id property="orderid" column="orderid"/>
        <result property="orderprice" column="orderprice"/>
    </collection>
</resultMap> <!--根据用户 ID 查询用户以及该用户所对应的所有订单-->
 
<select id="selectUsersAndOrders" resultMap="usersAndOrdersMapper">
    select * from users as u ,orders as o
    where u.userid = o.user_id and u.userid = #{userid}
</select>

4. 多对多查询

<resultMap id="usersAndOrdersAndItems" type="com.bjsxt.pojo.Users"> 
  <id property="userid" column="userid"/> 
  <result property="username" column="username"/> 
  <result property="usersex" column="usersex"/> 
  
  <collection property="orders" ofType="com.bjsxt.pojo.Orders"> 
      <id property="orderid" column="orderid"/> 
      <result property="orderprice" column="orderprice"/> 
      <collection property="items" ofType="com.bjsxt.pojo.Items"> 
          <id property="itemid" column="itemid"/> 
          <result property="itemid" column="itemid"/> 
          <result property="itemname" column="itemname"/> 
          <result property="itemprice" column="itemprice"/> 
      </collection> 
  </collection> 
</resultMap> 
<!--根据用户 ID 查询用户与订单以及订单中所包含的商品--> 
<select id="selectUsersAndOrdersAndItems" resultMap="usersAndOrdersAndItems"> 
    select * 
    from users as u,orders as o, orders_items as oi ,items as i 
    where u.userid = o.user_id and o.orderid = oi.order_id 
    and oi.item_id = i.itemid and u.userid = #{userid} 
</select>

2. 开启延迟加载

<resultMap id="usersAndOrdersMapperLazy" type="com.bjsxt.pojo.Users">
    <id property="userid" column="userid"/>
    <result property="username" column="username"/>
    <result property="usersex" column="usersex"/>
    // fetchType开启延迟加载
    // 通过column属性指定将封装查询结果对象的属性的值作为下一个查询的查询条件
    <collection property="orders" ofType="com.bjsxt.pojo.Orders"
                fetchType="lazy" select="selectOrdersByUserId" column="userid"/>
</resultMap>
 
<!--根据用户 ID 查询用户-->
<select id="selectUsersByIdLazy" resultMap="usersAndOrdersMapperLazy">
    select * from users where userid = #{userid}
</select>
 
<!--根据用户 ID 查询订单-->
<select id="selectOrdersByUserId" resultType="com.bjsxt.pojo.Orders">
    select * from orders where user_id = #{userid}
</select>