批量插入
<!--批量增加测试-->
<insert id="insertList" parameterType="java.util.List">
insert into t_enterprise_water_ele (<include refid="Base_Column_List"/>)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.waterEleId,jdbcType=VARCHAR},
#{item.enterpriseId,jdbcType=VARCHAR},
#{item.enterpriseUscc,jdbcType=VARCHAR},
#{item.enterpriseName,jdbcType=VARCHAR},
#{item.yearmonth,jdbcType=VARCHAR},
#{item.waterSize,jdbcType=DECIMAL},
#{item.waterAmount,jdbcType=VARCHAR},
#{item.eleSize,jdbcType=DOUBLE},
#{item.eleAmount,jdbcType=VARCHAR},
#{item.status,jdbcType=INTEGER},
#{item.operator,jdbcType=VARCHAR},
#{item.operateTime,jdbcType=TIMESTAMP}
)
</foreach>
</insert>
对于foreach标签的解释参考了网上的资料，具体如下：
foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。
foreach元素的属性主要有 item，index，collection，open，separator，close。
item表示集合中每一个元素进行迭代时的别名
index指定一个名字，用于表示在迭代过程中，每次迭代到的位置
open表示该语句以什么开始
separator表示在每次进行迭代之间以什么符号作为分隔 符
close表示以什么结束
在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况 下，该属性的值是不一样的，主要有一下3种情况：
1.如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
2.如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
3.如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map
批量更新
更新单个字段
根据goodsId查询出的多条记录，只更新 NODE_ID 字段
<!-- 方式一 -->
<update id="updateByBatch" parameterType="java.util.List">
update t_goods
set NODE_ID=
<foreach collection="list" item="item" index="index"
separator=" " open="case" close="end">
when GOODS_ID=#{item.goodsId} then #{item.nodeId}
</foreach>
where GOODS_ID in
<foreach collection="list" index="index" item="item"
separator="," open="(" close=")">
#{item.goodsId,jdbcType=BIGINT}
</foreach>
</update>
<!-- 方式二 -->
<update id="updateByBatch" parameterType="java.util.List">
UPDATE
t_goods
SET NODE_ID = CASE
<foreach collection="list" item="item" index="index">
WHEN GOODS_ID = #{item.goodsId} THEN #{item.nodeId}
</foreach>
END
WHERE GOODS_ID IN
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item.goodsId}
</foreach>
</update>
更新多个字段
根据userId 查询出的多条记录，更新 STATUS ,OPERATE_TIME , OPERATOR 字段
<update id="updateBatch" parameterType="java.util.List">
update t_user
<trim prefix="set" suffixOverrides=",">
<trim prefix="STATUS =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.status!=null">
when USER_ID=#{i.userId} then #{i.status}
</if>
</foreach>
</trim>
<trim prefix=" OPERATE_TIME =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.operateTime!=null">
when USER_ID=#{i.userId} then #{i.operateTime}
</if>
</foreach>
</trim>
<trim prefix="OPERATOR =case" suffix="end," >
<foreach collection="list" item="i" index="index">
<if test="i.operator!=null">
when USER_ID=#{i.userId} then #{i.operator}
</if>
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="i" index="index" >
USER_ID=#{i.userId}
</foreach>
</update>
性能对比
注意第一种方法要想成功，需要在db链接url后面带一个参数 &allowMultiQueries=true
即： jdbc:mysql://localhost:3306/mysqlTest?characterEncoding=utf-8&allowMultiQueries=true
<!-- 批量更新第一种方法，通过接收传进来的参数list进行循环着组装sql -->
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" separator=";" item="cus">
update t_customer set
c_name = #{cus.name},
c_age = #{cus.age},
c_sex = #{cus.sex},
c_ceroNo = #{cus.ceroNo},
c_ceroType = #{cus.ceroType}
where id = #{cus.id}
</foreach>
</update>
<!-- 批量更新第二种方法，通过 case when语句变相的进行批量更新 -->
<update id="batchUpdateCaseWhen" parameterType="java.util.List">
update t_customer
<trim prefix="set" suffixOverrides=",">
<trim prefix="c_name =case" suffix="end,">
<foreach collection="list" item="cus">
<if test="cus.name!=null">
when id=#{cus.id} then #{cus.name}
</if>
</foreach>
</trim>
<trim prefix="c_age =case" suffix="end,">
<foreach collection="list" item="cus">
<if test="cus.age!=null">
when id=#{cus.id} then #{cus.age}
</if>
</foreach>
</trim>
<trim prefix="c_sex =case" suffix="end,">
<foreach collection="list" item="cus">
<if test="cus.sex!=null">
when id=#{cus.id} then #{cus.sex}
</if>
</foreach>
</trim>
<trim prefix="c_ceroNo =case" suffix="end,">
<foreach collection="list" item="cus">
<if test="cus.ceroNo!=null">
when id=#{cus.id} then #{cus.ceroNo}
</if>
</foreach>
</trim>
<trim prefix="c_ceroType =case" suffix="end,">
<foreach collection="list" item="cus">
<if test="cus.ceroType!=null">
when id=#{cus.id} then #{cus.ceroType}
</if>
</foreach>
</trim>
</trim>
<where>
<foreach collection="list" separator="or" item="cus">
id = #{cus.id}
</foreach>
</where>
</update>
更新10条
第一种方式耗时
image.png
第二种方式耗时
image.png
更新100条
第一种
image.png
第二种
image.png
更新1000条
第一种
image.png
第二种
image.png
更新10000条
第一种
image.png
第二种
image.png
结果可以看出，两种方式进行批量更新，效率已经不在一个数量级了。case when明显的慢的多。
第一种效率其实相当高的，因为它仅仅有一个循环体，只不过最后update语句比较多，量大了就有可能造成sql阻塞。
第二种虽然最后只会有一条更新语句，但是xml中的循环体有点多，每一个case when 都要循环一遍list集合，所以大批量拼sql的时候会比较慢，所以效率问题严重。使用的时候建议分批插入。
根据效率，安全方面综合考虑，选择适合的很重要。
网友评论