快捷導(dǎo)航

springboot?注解方式批量插入數(shù)據(jù)的實(shí)現(xiàn)

更新時(shí)間：2022年03月28日 11:19:26 作者：什么都干的派森

一次請(qǐng)求需要往數(shù)據(jù)庫(kù)插入多條數(shù)據(jù)時(shí)，可以節(jié)省大量時(shí)間，本文主要介紹了springboot?注解方式批量插入數(shù)據(jù)，文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

一.使用場(chǎng)景

一次請(qǐng)求需要往數(shù)據(jù)庫(kù)插入多條數(shù)據(jù)時(shí)，可以節(jié)省大量時(shí)間，mysql操作在連接和斷開(kāi)時(shí)的開(kāi)銷(xiāo)超過(guò)本次操作總開(kāi)銷(xiāo)的40%。

二.實(shí)現(xiàn)方法

1.mysql表結(jié)構(gòu)

在這里插入圖片描述

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.測(cè)試類(lèi)

在這里插入圖片描述

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("中國(guó)圖書(shū)網(wǎng)");
        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.測(cè)試結(jié)果

在這里插入圖片描述

三.插入效率對(duì)比

1.批量插入

在這里插入圖片描述

2.一條一條插入

在這里插入圖片描述

到此這篇關(guān)于springboot 注解方式批量插入數(shù)據(jù)的文章就介紹到這了,更多相關(guān)springboot 注解方式批量插入數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: