关于mybatis中的resultType与resultMap用法及误区

来源：互联网发布：java实现分段函数编辑：程序博客网时间：2024/06/04 20:27

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>

阅读全文

0 0