mybatis的批量添加的高效方法
1.批量插入保存的方法:
<insert id="addTrainRecordBatch" useGeneratedKeys="true" parameterType="java.util.List"> <selectKey resultType="long" keyProperty="id" order="AFTER"> SELECT LAST_INSERT_ID() </selectKey> insert into regist_code (phone, code, createTime,status) values <foreach collection="list" item="item" index="index" separator="," > (#{item.phone}, #{item.code},now(), 0) </foreach> </insert>
数据库表结构是:
我的mode类是:
package com.hyhl.model; import java.sql.Timestamp; /** * 注冊时候验证码 * @author hyhl_wyf * */ public class RegistCodeModel { private int id; private String phone; private String code; private int status;//状态0有效、1无效 private Timestamp createTime; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getCode() { return code; } public void setCode(String code) { this.code = code; } public int getStatus() { return status; } public void setStatus(int status) { this.status = status; } public Timestamp getCreateTime() { return createTime; } public void setCreateTime(Timestamp createTime) { this.createTime = createTime; } }
测试的controller:
@RequestMapping(value = "/getRegistPhoneCodeBatch") public @ResponseBody String getBatchRegistPhoneCode(int num, HttpServletRequest request) { List<RegistCodeModel> list=new ArrayList<RegistCodeModel>(); String startTi=DateTimeUtil.getFormatDateTime( new java.util.Date()); System.out.println("开始时间:"+startTi); for(int i=0;i<num;i++){ RegistCodeModel model = new RegistCodeModel(); model.setCode(RandomUtil.getRandString(6)); model.setPhone("18037022633"); list.add(model); } String startTi1=DateTimeUtil.getFormatDateTime( new java.util.Date()); System.out.println(num+"条封装完List时间:"+startTi1); registCodeMapper.addTrainRecordBatch(list); String startTi2=DateTimeUtil.getFormatDateTime( new java.util.Date()); System.out.println(num+"条插入结束时间:"+startTi2); return "true"; }
测试的结果是:
-------------------10000条插入----------
开始时间:2018-04-27 17:46:08
10000条封装完List时间:2018-04-27 17:46:08
10000条插入结束时间:2018-04-27 17:46:08
------------50000条插入------------------------
开始时间:2018-04-27 17:46:33
50000条封装完List时间:2018-04-27 17:46:33
50000条插入结束时间:2018-04-27 17:46:34
------------------------------------
可以看出,插入5条也只是用了1s的时间。
比一条一条的插入快多了。