Mybatis 大数据量的批量update解决方案

自定义update table set (…) from values (xxx,xxx,…)

Mapper.xml

  <update id="updateBatch" parameterType="java.util.List">
            update t_risk_tolerance_answer trta
            <set>
                question=place.question,
                answer=place.answer,
                update_dt=place.updateDt:: timestamp with time zone,
                update_user_id=place.updateUserId
            </set>
            from (
              values
               <foreach collection="list" item="item" index="index" open="" close="" separator=","> 
            	<trim prefix ="(" suffix=")">
            		#{item.proposedCd},
            		#{item.seq},
            		#{item.question},
            		<choose >
                         <when test ="item.answer != null">
                              <trim prefix ="ARRAY[" suffix= "]">
                                   <foreach collection="item.answer" item ="answer" separator=","> #{answer}
                                   </foreach>
                              </trim>
                             ,
                         </when>
                         <otherwise >
                             null,
                         </otherwise>
                    </choose>
					<choose >
                         <when test ="item.updateDt != null">#{item.updateDt},</when>
                         <otherwise >null, </otherwise>
                    </choose>
                    <choose >
                         <when test ="item.updateUserId != null">#{item.updateUserId}</when>
                         <otherwise >null </otherwise>
                    </choose>            	
            	
            	</trim>
               </foreach>
            
            ) as place(
            	proposedCd,
            	seq,
            	question,
            	answer,
            	updateDt,
            	updateUserId
            ) 
            where
                trta.proposed_cd = place.proposedCd
            and trta.seq= place.seq
  </update>

Service.java

@Transactional
public void updateBatch(List<TRiskToleranceAnswer> tRiskToleranceAnswerList) {
    	int groupSize = 300;
    	int groupNo = tRiskToleranceAnswerList.size() / groupSize;
    	
    	SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
    	TRiskToleranceAnswerMapper mapper = sqlSession.getMapper(TRiskToleranceAnswerMapper.class);
    	
    	if (tRiskToleranceAnswerList.size() <= groupSize) {
    		mapper.updateBatch(tRiskToleranceAnswerList);
    	} else {
    		List<TRiskToleranceAnswer> subList=null;
    		for (int i = 0; i < groupNo; i++) {
    			subList = tRiskToleranceAnswerList.subList(0, groupSize);
    			mapper.updateBatch(subList);
    			tRiskToleranceAnswerList.subList(0, groupSize).clear();
    		}
    		if (tRiskToleranceAnswerList.size() > 0) {
    			mapper.updateBatch(tRiskToleranceAnswerList);
    		}
    	}

    	sqlSession.flushStatements();
    }

100000条记录 16秒 4个字段 springboot2.0.7 mybatis3.4.6 postgresql9.4

网上其他处理方法的限制

网上还有一种处理方法是： 通过循环，依次执行update语句 。如下

  <update id="updateBatch" parameterType="java.util.List">
  <foreach collection="list" item="item" index="index" open="" close="" separator=";"> 
            update t_risk_tolerance_answer
            <set>
                question=#{item.question},
                update_dt=#{item.updateDt},
                update_user_id=#{item.updateUserId}
            </set>
            where
                proposed_cd = #{item.proposedCd}
            and seq= #{item.seq}
           
        </foreach>
  </update>

这种方法1、2万件数据的时候还是可以执行的。但是数据量更大，比如10万件处理的话，就会报错。