MyBatis - 批量INSERT

XML文件

<insert id ="insertBatch" parameterType="java.util.List">
    <selectKey resultType ="java.lang.Integer" keyProperty= "id"
               order= "AFTER">
      SELECT LAST_INSERT_ID()
    </selectKey >
    INSERT INTO message_user
    (user_id, message_id, create_time)
    values
    <foreach collection ="list" item="model" index= "index" separator =",">
      (
      #{model.userId}, #{model.messageId}, #{model.createTime}
      )
    </foreach >
  </insert >

JAVA代码

// 部分代码
messageService.insert(message);
int messageId = message.getId();
int size = userIdList.size();
int startIndex = 0;
// MAX_BATCH_REQUEST: 最大批处理请求数
int endIndex = MAX_BATCH_REQUEST;
while (startIndex <= size) {
    if (endIndex > size) {
        endIndex = size;
    }
    List<String> subItems = userIdList.subList(startIndex, endIndex);
    List<MessageUser> modelList = new ArrayList<>();
    for (String obj : subItems) {
        if (StringUtils.isEmpty(obj)) continue;
        MessageUser mu = new MessageUser();
        mu.setUserId(new BigDecimal(obj).intValue());
        mu.setMessageId(messageId);
        mu.setCreateTime(now);
        modelList.add(mu);
    }
    // 调用Service方法
    messageUserService.insertBatch(modelList);
    startIndex += MAX_BATCH_REQUEST;
    endIndex += MAX_BATCH_REQUEST;
}

TIPS

1. 批处理时，应该打点，那样系统中断后可以续存
2. 如果是对响应有要求的系统，可以开多个线程，或者另起一个job执行批处理
3. 最大请求数不能太大，否则会给DB很大压力
4. 关于事务问题