记一次Mybatis/Mysql批量修改

ps: 以下sql因为所有参数都不能为空,所以没有使用<if>标签判空,以下仅仅是自己处理问题的心路历程,记录自己碰到的问题,如果各位大佬有更好的办法,或者我下面的处理有什么问题还望指教

按需求通过ID集合批量修改数据库多条数据

<update id="updateBatch" parameterType="java.util.List">  
   UPDATE table SET column1=#{value1}, column2=#{value2}
   WHERE id IN  
  <foreach collection="list" item="item" index="index" separator=",">  
    (#{item.id})  
  </foreach>  
</update>

然后需求变更了,需要通过ID集合的下标指定每条数据的下标(集合的下标=当条数据的序号)

然后上面那种方式满足不了新的需求,一开始的想法肯定是在java代码里面使用for循环遍历然后一条一条新增,但是这种方式频繁与数据库交互比较耗时,然后想到的方式就是使用foreach标签拼接sql 的方法来实现

<update id="updateBatch" parameterType="java.util.List">  
    <foreach collection="list" item="item" index="index" separator=";">  
        UPDATE table SET column1=#{item.value1}, column2=#{item.value2} 
        WHERE id = #{item.id}
    </foreach>   
</update>

然后报错了....

Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 
    'update table 
            set column1 = X ,
            column2 = X,
   ' at line 12

一开始以为是自己大意,可能sql或者标签可能哪有错,检查了一下发现没有问题,然后开始在网上找问题,然后定位到是配置的问题,

在数据库连接配置上需要加上&allowMultiQueries=true才行

然后Sql正常执行了,但是新的问题也来了,通过这种方式批量修改返回结果只会返回最后的一条记录的返回结果(设置int为返回值类型,10条记录前9条成功了,但是最后一条失败了返回结果是0)

然后我需要的是能够返回实际成功了多少条(如10条记录成功修改了5条那就返回5),然后又开始找答案,看到一篇文章说数据库连接还需要加上useAffectedRows=true,但是我加上了并没有什么用(可能是哪配置少了反正我没弄好QAQ)

然后就放弃了批量拼接修改sql的方式,最后选择了遍历参数通过case when的方式来实现了

<update id="updateBatch" parameterType="java.util.List">
        update table
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="column1 =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    when id=#{item.id} then #{item.value1}
                </foreach>
            </trim>
            <trim prefix="column2 =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    when id=#{item.id} then #{item.value2}
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
            #{item.id}
        </foreach>
    </update>

然后成功返回影响行数