mybatis如何实现批量更新和插入新增实例详解(附SQL以及mapper配置)

CREATE TABLE `student_info` (
  `STUDENT_ID` BIGINT(20) NOT NULL AUTO_INCREMENT UNIQUE COMMENT '学生id',
  `STUDENT_NAME` VARCHAR(30) NOT NULL COMMENT '学生姓名',
  `STUDENT_AGE` INTEGER(11) DEFAULT 1 COMMENT '学生年龄',
  `CREATE_TIME` TIMESTAMP(6) NULL DEFAULT now() COMMENT '创建时间',
  `UPDATE_TIME` TIMESTAMP(6) NULL DEFAULT now() COMMENT '修改时间',
  PRIMARY KEY (`STUDENT_ID`)
) ENGINE=InnoDB
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

 <insert id="batchInsert" useGeneratedKeys="true" parameterType="java.util.List">
  	<selectKey resultType="long" keyProperty="studentId" order="AFTER">  
        SELECT  
        LAST_INSERT_ID()  
    </selectKey>  
  		INSERT INTO student_info(STUDENT_NAME, STUDENT_AGE, CREATE_TIME, UPDATE_TIME) 
  		VALUES
  		<foreach collection="list" item="data" separator="," index = "index">
   			(#{data.studentName},#{data.studentAge},#{data.createTime},#{data.updateTime})
 		</foreach>
  		 
  	</insert>

 List<StudentInfo> list = new ArrayList<StudentInfo>();	
 StudentInfo info = new StudentInfo();
 info.setStudentName("张三丰");
 info.setStudentAge(149);
 info.setCreateTime(new Date());
 info.setUpdateTime(new Date());
 list.add(info);
 StudentInfo info2 = new StudentInfo();
 info.setStudentName("孙中山");
 info.setStudentAge(59);
 info.setCreateTime(new Date());
 info.setUpdateTime(new Date());
 list.add(info2);
 StudentInfo info3 = new StudentInfo();
 info.setStudentName("王才力");
 info.setStudentAge(33);
 info.setCreateTime(new Date());
 info.setUpdateTime(new Date());
 list.add(info3);
 
 int batchExec = studentInfoMapper.batchInsert(list);

<update id="batchUpdate" parameterType="java.util.List">
		update student_info
		<trim prefix="set" suffixOverrides=",">
			<trim prefix="STUDENT_NAME =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.studentName!=null">
						when STUDENT_ID=#{i.studentId}  then
						#{i.studentName}
					</if>
				</foreach>
			</trim>
			<trim prefix=" STUDENT_AGE =case" suffix="end,">
				<foreach collection="list" item="i" index="index">
					<if test="i.studentAge!=null">
						when STUDENT_ID=#{i.studentId} then
						#{i.studentAge}
					</if>
				</foreach>
			</trim>
		</trim>
	</update>

<!--mybatis中批量执行uodate操作目前只能如此，若读者有新发现可留言告知-->
update student_info set STUDENT_NAME = case when STUDENT_ID=#{i.studentId}  then #{i.studentName}

List<StudentInfo> handlerList = new ArrayList<StudentInfo>();
	for( StudentInfo innerInfo : list  ){
		StudentInfo record = new StudentInfo();
		record.setStudentId(innerInfo.getStudentId());
		if(innerInfo.getStudentId() == 1){
			record.setStudentName(innerInfo.getStudentName() + "十年前");
			record.setStudentAge(innerInfo.getStudentAge() - 10);
		}else{
			record.setStudentName(innerInfo.getStudentName() + "五年前");
			record.setStudentAge(innerInfo.getStudentAge() - 5);
		}
		handlerList.add(record);
	}
	int exec = studentInfoMapper.batchUpdate(handlerList);