java.sql.BatchUpdateException: Communications link failure

java.sql.BatchUpdateException: Communications link failure

 

由于业务需求,需要使用mybatis对一个表进行批量update操作,mybatis的Mapper文件大致如下:

<update id="batchUpdate" parameterType="java.util.List">
        update user
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="name =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.name !=null and item.name != ''">
                        when ID=#{item.id} then #{item.name}
                    </if>
                    <if test="item.name ==null or item.name == ''">
                        when ID=#{item.id} then user.name
                    </if>
                </foreach>
            </trim>
            <trim prefix="birthday=case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.birthday !=null">
                        when ID=#{item.id} then #{item.birthday}
                    </if>
                    <if test="item.birthday ==null">
                        when ID=#{item.id} then user.BIRTHDAY
                    </if>
                </foreach>
            </trim>
        </trim>
        ,UPDATE_TIME = NOW(),UPDATOR=#{item.updator}
        where ID in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id,jdbcType=VARCHAR}
        </foreach>
    </update>

当数据量小的时候,这段程序运行没有问题,但是当一次更新超过5000条时(根据mysql的配置决定)就会抛出如下异常:

java.sqlBatchUpdateException.Communicationslinkfailure 
The last packet successfully received from the server was 10 milliseconds ago.
The last packet sent successfully to the server was 10 milliseconds ago:SQL[];

 

想象一下如果用上述批量更新一次性更新5000条数据的化,整个update语句是非常长的,因为它采用的是如下形式:

 

update user set name= case when .. then ..
                           when..then 
			   ......
			   end,
	     birthday= case when .. then ..
                           when..then 
			   ......
			   end
	     where id in(..,..,.......) 

后来经过实验一次更新1000条不会报错,最后为保障程序运行最后采用分批提交的方式来完成此项功能.将原有需更新的list拆分,分批提交更新.

//update分批提交,每次最多500条.防止数据库报
int batch = (list.size() < 500) ? 1 : ((list.size() / 500) + (((list.size() % 500) == 0) ? 0 : 1));
logger.info("共更新:" + list.size() + "条记录,分" + batch + "批提交");
List<List<Dweller>> splitList = ListUtil.split(list, batch);
for (List<User> tempList : splitList) {
        userMapper.batchUpdate(tempList);
}

//list分割函数
public static <T> List<List<T>> split(List<T> source, int n) {
    List<List<T>> result = new ArrayList<List<T>>();
    //(先计算出余数)
    int remaider = source.size() % n;
    //然后是商
    int number = source.size() / n;
    //偏移量
    int offset = 0;
    for (int i = 0; i < n; i++) {
        List<T> value = null;
        if (remaider > 0) {
            value = source.subList(i * number + offset, (i + 1) * number + offset + 1);
            remaider--;
            offset++;
        } else {
            value = source.subList(i * number + offset, (i + 1) * number + offset);
        }
        result.add(value);
    }
    return result;
}