Mybatis 执行批量插入 10W 4.8s

来源：互联网发布：如何用微博推广淘宝编辑：程序博客网时间：2024/05/22 18:23

本数据查询插入是从2个不同数据源处理

model：

public class AlarmLog implements Serializable{    private static final  long  serialVersionUID = 1L;    private  int id;    private int devId;    private  int cvs;    private Date createDate;    public AlarmLog(){        super();    }    public void setId(int id) {        this.id = id;    }    public int getId() {        return id;    }    public void setCreateDate(Date createDate) {        this.createDate = createDate;    }    public Date getCreateDate() {        return createDate;    }    public void setCvs(int cvs) {        this.cvs = cvs;    }    public int getCvs() {        return cvs;    }    public int getDevId() {        return devId;    }    public void setDevId(int devId) {        this.devId = devId;    }}

mapper.xml

mapper namespace="com.ding.dao.one.AlarmDao">    <!--结果集-->    <resultMap id="alarmMap" type="com.ding.pojo.AlarmLog">        <id property="id" column="id"/>        <result property="devId" column="devId"/>        <result property="cvs" column="cvs"/>        <result property="createDate" javaType="java.util.Date" column="createDate"/>    </resultMap>    <insert id="addBatch" parameterType="com.ding.pojo.AlarmLog"  >        insert into db_system.t_alarm_log(devId,cvs,createDate) values        <foreach collection="list" item="alarm" index="index" separator=",">            (#{alarm.devId},#{alarm.cvs}, curdate())        </foreach>    </insert></mapper>

dao interface:

public interface AlarmDao {  int add(AlarmLog alarmLog);  int addBatch(List list);}

service

@Service("alarmLogService")public class AlarmLogService {    @Autowired    private AlarmLogDao alarmLogDao;    public List<AlarmLog> getAlarm() {        return this.alarmLogDao.getAlarm();    }    public List<AlarmLog> getAlarmData(int day_limit, int less) {        Map map = new HashMap<>();        map.put("day_limit", day_limit);        map.put("less", less);        return this.alarmLogDao.getAlarmData(map);    }    @Autowired    private AlarmDao alarmDao;    public  int addBatch(List list){        return  alarmDao.addBatch(list);    }}

test

@Test    public  void addBatch(){        long time = System.currentTimeMillis();        System.out.println(" 启动时间："+time);        Map map = this.warningService.getWarning();        System.out.println(" 查询一条数据时间："+String.valueOf(time-System.currentTimeMillis()));        int day_limit = (int)map.get("day_limit");        int less = (int)map.get("less");        System.out.println(day_limit+","+less);        long time2 = System.currentTimeMillis();        System.out.println(" 启动时间："+time2);        List<AlarmLog> list =this.alarmLogService.getAlarmData(day_limit,less);        System.out.println(" 查询"+list.size()+"数据时间："+String.valueOf(time2-System.currentTimeMillis()));        System.out.println("list =="+list.size());        long time3 = System.currentTimeMillis();        System.out.println(" 启动时间："+time3);        int size = alarmLogService.addBatch(list);        System.out.println(" 批量插入"+list.size()+"数据时间："+String.valueOf(System.currentTimeMillis()-time3));        System.out.println("size=="+size);    }

后台打印：

启动时间：1468226066491 查询一条数据时间：-11530,500 启动时间：1468226066606 查询92414数据时间：-747list ==92414 启动时间：1468226067353 批量插入92414数据时间：-4822size==92414

0 1