springboot 注解方式批量插入数据的实现

一.使用场景

一次请求需要往数据库插入多条数据时，可以节省大量时间，mysql操作在连接和断开时的开销超过本次操作总开销的40%。

二.实现方法

1.mysql表结构

2.domain

package com.cxstar.order.domain;

import java.util.Date;

@lombok.Data
public class Data {
  private int id;
  private String ruid;
  private String title;
  private String author;
  private String coverUrl;
  private String detialUrl;
  private String code;
  private int type;
  private String publisher;
  private int groupId;
  private String groupName;
  private int pageNo;
  private String searchKey;
  private Date createTime;
}

3.mapper

package com.cxstar.order.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cxstar.order.domain.Data;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Mapper
public interface DataMapper extends BaseMapper<Data> {

  @InsertProvider(type = Provider.class, method = "batchInsert")
  int batchInsert(List<Data> data);

  class Provider {
      /* 批量插入 */
      public String batchInsert(Map map) {
          List<Data> data = (List<Data>) map.get("list");
          StringBuilder sb = new StringBuilder();
          sb.append("INSERT INTO data (" +
                      "ruid," +
                      "title," +
                      "author," +
                      "code," +
                      "type," +
                      "publisher," +
                      "group_id," +
                      "group_name," +
                      "page_no," +
                      "search_key," +
                      "create_time," +
                      "cover_url," +
                      "detial_url" +
                  ") VALUES ");
          MessageFormat mf = new MessageFormat(
                  "(" +
                      "#'{'list[{0}].ruid}, " +
                      "#'{'list[{0}].title}, " +
                      "#'{'list[{0}].author}, " +
                      "#'{'list[{0}].code}, " +
                      "#'{'list[{0}].type}, " +
                      "#'{'list[{0}].publisher}, " +
                      "#'{'list[{0}].groupId}, " +
                      "#'{'list[{0}].groupName}, " +
                      "#'{'list[{0}].pageNo}, " +
                      "#'{'list[{0}].searchKey}, " +
                      "#'{'list[{0}].createTime}, " +
                      "#'{'list[{0}].coverUrl}, " +
                      "#'{'list[{0}].detialUrl}" +
                  ")"
          );

          for (int i = 0; i < data.size(); i++) {
              sb.append(mf.format(new Object[] {i}));
              if (i < data.size() - 1)
                  sb.append(",");
          }
          return sb.toString();
      }
      
  }

}

4.测试类

package com.cxstar.order;

import com.alibaba.fastjson.JSONObject;
import com.cxstar.order.domain.Data;
import com.cxstar.order.mapper.DataMapper;
//import com.cxstar.order.service.OrderService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.Date;
import java.util.UUID;

@SpringBootTest
class OrderApplicationTests {

  @Autowired
  DataMapper dataMapper;

  @Test
  void data_batch_insert() {

      ArrayList<Data> batchInsertList = new ArrayList<>();

      Data data = new Data();
      data.setTitle("历史上的今天");
      data.setAuthor("郭漫");
      data.setCoverUrl("http://image31.bookschina.com/2011/20110520/s5143135.jpg");
      data.setDetialUrl("http://www.bookschina.com/5143135.htm");
      data.setGroupId(1);
      data.setGroupName("中国图书网");
      data.setRuid(UUID.randomUUID().toString().replaceAll("-",""));
      data.setType(1);
      data.setCode(null);
      data.setPublisher(null);
      data.setPageNo(1);
      data.setSearchKey("历史上的今天");
      data.setCreateTime(new Date());

      batchInsertList.add(data);

      System.out.println(dataMapper.batchInsert(batchInsertList));
  }

}

5.测试结果

三.插入效率对比

1.批量插入

2.一条一条插入

 到此这篇关于springboot 注解方式批量插入数据的文章就介绍到这了,更多相关springboot 注解方式批量插入数据内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/weixin_43721000/article/details/123751975