mybatis批量更新两种写法执行性能的对比

1. 多个update语句

<update id="updateStudentUnreadLabelForEach">
		<foreach collection="subjectiveSharedLabelCountDTOList" item="item" separator=";">
			update
			h_homework_student_copy
			set
			unread_subjective_label_count = #{item.unreadNum}
			where
			homework_id = #{homeworkId}
			and deleted = 'N'
			and student_id =#{item.studentId}
		</foreach>
	</update>

2. 更新字段时用case when

<update id="updateStudentUnreadLabel">
		update
		h_homework_student_copy
		set
		unread_subjective_label_count =
		<foreach collection="subjectiveSharedLabelCountDTOList" item="item" index="index" separator=" " open="case" close="end">
			when student_id=#{item.studentId} then #{item.unreadNum}
		</foreach>
		,update_date = now()
		where
		homework_id = #{homeworkId}
		and deleted = 'N'
		and student_id in
		<foreach collection="subjectiveSharedLabelCountDTOList" item="item" index="index" separator="," open="(" close=")">
			#{item.studentId}
		</foreach>
	</update>

3. 测试类

@RunWith(SpringJUnit4ClassRunner.class)
@Configuration()
@ContextConfiguration( {"classpath:spring/test-dao.xml", "classpath:spring/test-config.xml"} )
public class HomeworkStudentMapperTest extends AbstractJUnit4SpringContextTests {

    @Autowired
    private HomeworkStudentMapper homeworkStudentMapper;

    @Test
    public void updateStudentUnreadLabel(){
        Long homeworkId = 111839L;
        List<SubjectiveSharedLabelCountDTO> subjectiveSharedLabelCountDTOList = new ArrayList<>();

       String studentIds =  "11655\n" + "12269\n" + "14691\n" + "19184\n" + "22355\n" + "23482\n" + "24758\n" + "24831\n" + "3000737\n"
                + "3000873\n" + "3002125\n" + "3004527\n" + "3007690\n" + "3009113\n" + "3012058\n" + "3012069\n"
                + "3012339\n" + "3012638\n" + "3013302\n" + "3029384\n" + "3070755\n" + "3070759\n" + "3070761\n"
                + "3070765\n" + "3070770\n" + "3070783\n" + "3070786\n" + "3070788\n" + "3070789\n" + "3070801\n"
                + "3070802\n" + "3070876\n" + "3070930\n" + "3070931\n" + "3070932\n" + "3070937\n" + "3070954\n"
                + "3070964\n" + "3070967\n" + "3070994\n" + "3071026\n" + "3071034\n" + "3071078\n" + "3071082\n"
                + "3071237\n" + "3071242\n" + "3071249\n" + "3071253\n" + "3071255\n" + "3071260\n" + "3071276\n"
                + "3071280\n" + "3071283\n" + "3071286\n" + "3071309\n" + "3071312\n" + "3071317\n" + "3071335\n"
                + "3071341\n" + "3071345\n" + "3071349\n" + "3071388\n" + "3097992\n" + "3130566\n" + "3130583\n"
                + "3130666\n" + "3130669\n" + "3130670\n" + "3130673\n" + "3130692\n" + "3130765\n" + "3130860\n"
                + "3130884\n" + "3131000\n" + "3131001\n" + "3131002\n" + "3131050\n" + "3131051\n" + "3131069\n"
                + "3131111\n" + "3131138\n" + "3131139\n" + "3131140\n" + "3131142\n" + "3131143\n" + "3131144\n"
                + "3131149\n" + "3131152\n" + "3131153\n" + "3131158\n" + "3131161\n" + "3131162\n" + "3131233\n"
                + "3131246\n" + "3131276\n" + "3131295\n" + "3131296\n" + "3131297\n" + "3131363\n" + "3131400\n"
                + "3603252\n" + "5606928\n" + "89001341\n" + "89001554";

        String[] studentIdArray = studentIds.split("\n");
        System.out.println(studentIdArray);

        for (int i=0; i<studentIdArray.length; i++){
            SubjectiveSharedLabelCountDTO subjectiveSharedLabelCountDTO = new SubjectiveSharedLabelCountDTO();
            subjectiveSharedLabelCountDTO.setStudentId(Long.valueOf(studentIdArray[i]));
            subjectiveSharedLabelCountDTO.setUnreadNum(1);

            subjectiveSharedLabelCountDTOList.add(subjectiveSharedLabelCountDTO);
        }


        Long start1 = System.currentTimeMillis();
        homeworkStudentMapper.updateStudentUnreadLabel(homeworkId, subjectiveSharedLabelCountDTOList);
        System.out.println("case when update 用时：" + (System.currentTimeMillis() - start1));

        Long start2 = System.currentTimeMillis();
        homeworkStudentMapper.updateStudentUnreadLabelForEach(homeworkId,subjectiveSharedLabelCountDTOList);
        System.out.println("for each update 用时" + (System.currentTimeMillis() - start2));
    }
}

4. 测试结果

case when update 用时：216
for each update 用时41

结果很明显，通过fore each拼接成多个update语句效率更高