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

本系列示例sql均在Mysql中验证过 更多CSDN该博主精彩请访问 http://blog.csdn.net/zixiao217

Mybatis批量插入、批量修改

批量插入

step1:创建DB表

代码语言：javascript复制

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';

step2:编写mapper.xml文件

代码语言：javascript复制

 <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>

step3:java代码简单示例

代码语言：javascript复制

 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);

批量修改

step1: 延用学生信息表，写sql

代码语言：javascript复制

<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>

修改的语句相当于多次执行

代码语言：javascript复制

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

step2:java示例code

代码语言：javascript复制

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);

sql mybatis list record 配置

0 人点赞