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

Mybatis批量插入、批量修改

批量插入

step1:创建DB表

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

<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代码简单示例

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

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

step2:java示例code

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