复合查询的层叠映射在Mybaitis中的应用举例

复合查询的层叠映射在Mybatis中的应用举例

1.代码

<mapper namespace="com.aishang.shop.dao.OrderDao">

<resultMap id="orderMap" type="OrderExt">
        <id property="oId" column="o_id"></id>
        <result property="total" column="total"></result>
        <result property="orderTime" column="order_time"></result>
        <result property="state" column="state"></result>
        <result property="name" column="name"></result>
        <result property="phone" column="phone"></result>
        <result property="addr" column="addr"></result>
        <result property="uId" column="u_id"></result>
<!--        一对多-->
        <collection property="orderItemExtList" ofType="OrderItemExt">
            <id property="itemId" column="item_id"></id>
            <result property="count" column="count"></result>
            <result property="subTotal" column="sub_total"></result>
            <result property="pId" column="p_Id"></result>
            <result property="oId" column="o_Id"></result>

            <!--        一对一-->
                    <association property="product" javaType="Product">
                        <id property="pId" column="p_id"></id>
                        <result property="pName" column="p_name"></result>
                        <result property="marketPrice" column="market_price"></result>
                        <result property="shopPrice" column="shop_price"></result>
                        <result property="image" column="image"></result>
                        <result property="pDesc" column="p_desc"></result>
                        <result property="isHot" column="is_hot"></result>
                        <result property="pDate" column="p_date"></result>
                        <result property="csId" column="cs_id"></result>
                    </association>

        </collection>

    </resultMap>
</mapper>


<select id="getOrderDetail" parameterType="Integer" resultMap="orderMap">

        select *
        from `order`
                 INNER JOIN order_item
                            on `order`.o_id = order_item.o_id
                 INNER JOIN product
                            on product.p_id = order_item.p_id
        where `order`.o_id = #{oId}

    </select>

2.我的理解

首先，这明显是一个层叠映射因为出现了resultMap，而不是resultType,而且sql语句中极有可能使用了联合查询

• 结构梳理

<mapper namespace="接口的权限定位名">
	<resultMap id="标识resultMap" type="返回值类型">
        <!--   这里的id是主键-->
        <id property="类对应" column="表对应id字段"></id>
        <result property="类对应" column="表对应"></result>
        <result property="类对应" column="表对应"></result>
        <result property="类对应" column="表对应"></result>
        
         <!--   有集合List 一对多-->
        <collection  property="类名" ofType="泛型大写">
             <!--   这里的id是主键-->
        	<id property="类对应" column="表对应id字段"></id>
            <result property="类对应" column="表对应"></result>
        	<result property="类对应" column="表对应"></result>
        	<result property="类对应" column="表对应"></result>
        	         <!--   有po包其他实体类 一对一-->
				<association property="类名" javaType="类大写">
            		<!--   这里的id是主键-->
        			<id property="类对应" column="表对应id字段"></id>
                    <result property="类对应" column="表对应"></result>
        			<result property="类对应" column="表对应"></result>
        			<result property="类对应" column="表对应"></result>
                    
            	</association>
            
        
        </collection>
        
    
    </resultMap>

</mapper>

<select id="方法名" parameterType="输入类型" resultMap="标识resultMap">
	sql语句
</select>

• 总结

  显然当出现复杂的sql语句时，使用resulType平铺映射（一对一）就不合适了，这时候就要使用resultMapper,无论一对一还是一对多，第一行必须是id,且id和result都是双标签

  <mapper namespace="类的全限定位名">
      <resultMap id="bs"  property="返回值类型(一般是扩展类)最大的类写在最外边">
          <id property="类对应" column="表对应"></id>
          <result property="类对应" column="表对应"></result>
          <result property="类对应" column="表对应"></result>
          <!-- 一对多List-->
          <column proprty="类名" ofType="泛型大写">
              <id property="类对应" column="表对应"></id>
              <result property="类对应" column="表对应"></result>
              <result property="类对应" column="表对应"></result>
  
              <!--一对一 要写在column里-->
              <association property="类名" javaType="类大写">
                  <id property="类对应" column="表对应"></id>
                  <result property="类对应" column="表对应"></result>
                  <result property="类对应" column="表对应"></result>
              </association>
  
          </column>
  
  
      </resultMap>
  </mapper>
  
  <!--SQL-->
  <select id="方法名" parameterType="输入类型" resultMap="bs" >
  sql语句
  </select>
  

详解请看这篇文章 Mybatis开发要点：resultType和resultMap的区别？ - 掘金 (juejin.cn)