关于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>