關於mybatis中的resultType與resultMap用法及誤區

1.resultType與resultMap
resultType：指定返回資料的型別 如果為List 此處應填對應的java Object 注：select的條件最好與Object裡的欄位一一對應，避免引發其他錯誤（大小寫敏感）
resultMap：指定返回的資料為定義好的XXXMap，其中的欄位必須一一對應（大小寫敏感）
2.foreach傳參
傳入是單一引數List ——foreach裡的collection為list
傳入是多個引數，需要用Map傳參 ——foreach裡的collection為map裡的key值
3.batch insert
insert on duplicate key and batch

<insert id="batchInsertOnDuplicateDate" parameterType="com.test.op.interfaces.vo.StatisticsDataForFwyyVO" >
    <foreach collection="list" item="dataForFwyyVOs" index="index" open="" close="" separator=";">
      insert into tb_statistics (create_time,data_time,
     ...)
      values
        (
        #{dataForFwyyVOs.createTime,jdbcType=VARCHAR},
        #{dataForFwyyVOs.dataTime,jdbcType=DATE},
       ...        )
      on duplicate key update
      create_time = #{dataForFwyyVOs.createTime,jdbcType=VARCHAR},
     ...
  </foreach>
  /*StatisticsDataFwyy batchInsertOnDuplicateDate*/
</insert>

4.batch update
批量執行多條update語句

<update id="updateBySnIds" parameterType="java.util.Map">
   <foreach collection="snIds" item="item" index="index" open="" close="" separator=";">
      update sn_card
      set is_saled = 1, order_num = #{orderNum,jdbcType=VARCHAR}, 
     ...
      where id = #{item.snId} and version = #{item.version}
   </foreach>
   /*SNCard updateBySnIds*/
</update>

執行單條update語句

<update id="deleteByIdsIn">
   update service_sku
    set is_deleted = 1
   <if test="#{0} != null and #{0}.size > 0">
    where id in
    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">  
      #{item}  
   </foreach> 
</if>

5.case when

 <update id="updateBatch" parameterType="java.util.List" >
   update tb_address
    <trim prefix="set" suffixOverrides=",">
                <trim prefix="businessId =case" suffix="end,">
                          <foreach collection="list" item="item" index="index">
                                   when id=${item.id} then #{item.businessId}
                          </foreach>
                   </trim>
                   <trim prefix="area = case" suffix="end,">
                          <foreach collection="list" item="item" index="index">
                                   when id=${item.id} then ${item.area}
                          </foreach>
                   </trim>
                   <trim prefix=" province =case" suffix="end,">
                          <foreach collection="list" item="item" index="index">
                                   when id=${item.id} then ${item.province}
                          </foreach>
                   </trim>
                    <trim prefix=" city =case" suffix="end,">
                          <foreach collection="list" item="item" index="index">
                                   when id=${item.id} then ${item.city}
                          </foreach>
                   </trim>
                   <trim prefix=" county =case" suffix="end,">
                          <foreach collection="list" item="item" index="index">
                                   when id=${item.id} then ${item.county}
                          </foreach>
                   </trim>
                  ...
                    updateTime=now()
      </trim>
           where id in 
           <foreach collection="list" open="(" close=")" separator="," item="item" index="index" >
                     ${item.id}
         </foreach>
/*address updateBatch*/
 </update>

6.otherwise
組合多個查詢條件 不建議使用

<select id="countTotalAmountForFwyy" parameterType="java.util.Map" resultType="java.math.BigDecimal">
   select sum(actual_price) from tb_order
   where
      <choose>
         <when test="classifyId != 0">
            classify_id = #{classifyId,jdbcType=INTEGER}
         </when>
         <otherwise>
            classify_id in (1,2,4)
         </otherwise>
      </choose>
      <choose>
         <when test="startDate != null">
            and pay_time &gt;= #{startDate,jdbcType=TIMESTAMP} and pay_time &lt;= #{endDate,jdbcType=TIMESTAMP}
         </when>
         <otherwise>
            and pay_time &lt;= #{endDate}
         </otherwise>
      </choose>
      and actual_price > 0 and be_del=false       
   /*order countTotalAmountForFwyy*/
</select>