欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

基于EasyExcel實(shí)現(xiàn)百萬(wàn)級(jí)數(shù)據(jù)導(dǎo)入導(dǎo)出詳解

 更新時(shí)間:2023年01月21日 10:25:28   作者:llp1110  
大數(shù)據(jù)的導(dǎo)入和導(dǎo)出,相信大家在日常的開(kāi)發(fā)、面試中都會(huì)遇到。本文將為大家詳細(xì)介紹一下如何利用EasyExcel實(shí)現(xiàn)百萬(wàn)級(jí)數(shù)據(jù)導(dǎo)入導(dǎo)出,需要的可以參考一下

在項(xiàng)目開(kāi)發(fā)中往往需要使用到數(shù)據(jù)的導(dǎo)入和導(dǎo)出,導(dǎo)入就是從Excel中導(dǎo)入到DB中,而導(dǎo)出就是從DB中查詢數(shù)據(jù)然后使用POI寫(xiě)到Excel上。

大數(shù)據(jù)的導(dǎo)入和導(dǎo)出,相信大家在日常的開(kāi)發(fā)、面試中都會(huì)遇到。

很多問(wèn)題只要這一次解決了,總給復(fù)盤(pán)記錄,后期遇到同樣的問(wèn)題就好解決了。好啦,廢話不多說(shuō)開(kāi)始正文!

1.傳統(tǒng)POI的的版本優(yōu)缺點(diǎn)比較

其實(shí)想到數(shù)據(jù)的導(dǎo)入導(dǎo)出,理所當(dāng)然的會(huì)想到apache的poi技術(shù),以及Excel的版本問(wèn)題。

HSSFWorkbook

這個(gè)實(shí)現(xiàn)類是我們?cè)缙谑褂米疃嗟膶?duì)象,它可以操作Excel2003以前(包含2003)的所有Excel版本。在2003以前Excel的版本后綴還是.xls

XSSFWorkbook

這個(gè)實(shí)現(xiàn)類現(xiàn)在在很多公司都可以發(fā)現(xiàn)還在使用,它是操作的Excel2003–Excel2007之間的版本,Excel的擴(kuò)展名是.xlsx

SXSSFWorkbook

這個(gè)實(shí)現(xiàn)類是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,擴(kuò)展名是.xlsx

HSSFWorkbook

它是POI版本中最常用的方式,不過(guò):

  • 它的缺點(diǎn)是 最多只能導(dǎo)出 65535行,也就是導(dǎo)出的數(shù)據(jù)函數(shù)超過(guò)這個(gè)數(shù)據(jù)就會(huì)報(bào)錯(cuò);
  • 它的優(yōu)點(diǎn)是 不會(huì)報(bào)內(nèi)存溢出。(因?yàn)閿?shù)據(jù)量還不到7w所以內(nèi)存一般都?jí)蛴茫紫饶愕妹鞔_知道這種方式是將數(shù)據(jù)先讀取到內(nèi)存中,然后再操作)

XSSFWorkbook

  • 優(yōu)點(diǎn):這種形式的出現(xiàn)是為了突破HSSFWorkbook的65535行局限,是為了針對(duì)Excel2007版本的1048576行,16384列,最多可以導(dǎo)出104w條數(shù)據(jù);
  • 缺點(diǎn):伴隨的問(wèn)題來(lái)了,雖然導(dǎo)出數(shù)據(jù)行數(shù)增加了好多倍,但是隨之而來(lái)的內(nèi)存溢出問(wèn)題也成了噩夢(mèng)。因?yàn)槟闼鶆?chuàng)建的book,Sheet,row,cell等在寫(xiě)入到Excel之前,都是存放在內(nèi)存中的(這還沒(méi)有算Excel的一些樣式格式等等),可想而知,內(nèi)存不溢出就有點(diǎn)不科學(xué)了!?。?/li>

SXSSFWorkbook

從POI 3.8版本開(kāi)始,提供了一種基于XSSF的低內(nèi)存占用的SXSSF方式:

優(yōu)點(diǎn):

  • 這種方式不會(huì)一般不會(huì)出現(xiàn)內(nèi)存溢出(它使用了硬盤(pán)來(lái)?yè)Q取內(nèi)存空間,
  • 也就是當(dāng)內(nèi)存中數(shù)據(jù)達(dá)到一定程度這些數(shù)據(jù)會(huì)被持久化到硬盤(pán)中存儲(chǔ)起來(lái),而內(nèi)存中存的都是最新的數(shù)據(jù)),
  • 并且支持大型Excel文件的創(chuàng)建(存儲(chǔ)百萬(wàn)條數(shù)據(jù)綽綽有余)。

缺點(diǎn):

  • 既然一部分?jǐn)?shù)據(jù)持久化到了硬盤(pán)中,且不能被查看和訪問(wèn)那么就會(huì)導(dǎo)致,
  • 在同一時(shí)間點(diǎn)我們只能訪問(wèn)一定數(shù)量的數(shù)據(jù),也就是內(nèi)存中存儲(chǔ)的數(shù)據(jù);
  • sheet.clone()方法將不再支持,還是因?yàn)槌志没脑?
  • 不再支持對(duì)公式的求值,還是因?yàn)槌志没脑?,在硬盤(pán)中的數(shù)據(jù)沒(méi)法讀取到內(nèi)存中進(jìn)行計(jì)算;
  • 在使用模板方式下載數(shù)據(jù)的時(shí)候,不能改動(dòng)表頭,還是因?yàn)槌志没膯?wèn)題,寫(xiě)到了硬盤(pán)里就不能改變了;

2.使用方式哪種看情況

經(jīng)過(guò)了解也知道了這三種Workbook的優(yōu)點(diǎn)和缺點(diǎn),那么具體使用哪種方式還是需要看情況的:

我一般會(huì)根據(jù)這樣幾種情況做分析選擇:

1、當(dāng)我們經(jīng)常導(dǎo)入導(dǎo)出的數(shù)據(jù)不超過(guò)7w的情況下,可以使用 HSSFWorkbook 或者 XSSFWorkbook都行;

2、當(dāng)數(shù)據(jù)量查過(guò)7w并且導(dǎo)出的Excel中不牽扯對(duì)Excel的樣式,公式,格式等操作的情況下,推薦使用SXSSFWorkbook;

3、當(dāng)數(shù)據(jù)量查過(guò)7w,并且我們需要操做Excel中的表頭,樣式,公式等,這時(shí)候我們可以使用 XSSFWorkbook 配合進(jìn)行分批查詢,分批寫(xiě)入Excel的方式來(lái)做;

3.百萬(wàn)數(shù)據(jù)導(dǎo)入導(dǎo)出

想要解決問(wèn)題我們首先要明白自己遇到的問(wèn)題是什么?

1、 我遇到的數(shù)據(jù)量超級(jí)大,使用傳統(tǒng)的POI方式來(lái)完成導(dǎo)入導(dǎo)出很明顯會(huì)內(nèi)存溢出,并且效率會(huì)非常低;

2、 數(shù)據(jù)量大直接使用select * from tableName肯定不行,一下子查出來(lái)300w條數(shù)據(jù)肯定會(huì)很慢;

3、 300w 數(shù)據(jù)導(dǎo)出到Excel時(shí)肯定不能都寫(xiě)在一個(gè)Sheet中,這樣效率會(huì)非常低;估計(jì)打開(kāi)都得幾分鐘;

4、 300w數(shù)據(jù)導(dǎo)出到Excel中肯定不能一行一行的導(dǎo)出到Excel中。頻繁IO操作絕對(duì)不行;

5、 導(dǎo)入時(shí)300萬(wàn)數(shù)據(jù)存儲(chǔ)到DB如果循環(huán)一條條插入也肯定不行;

6、導(dǎo)入時(shí)300w數(shù)據(jù)如果使用Mybatis的批量插入肯定不行,因?yàn)镸ybatis的批量插入其實(shí)就是SQL的循環(huán);一樣很慢。

解決思路:

針對(duì)1 :

其實(shí)問(wèn)題所在就是內(nèi)存溢出,我們只要使用對(duì)上面介紹的POI方式即可,主要問(wèn)題就是原生的POI解決起來(lái)相當(dāng)麻煩。

經(jīng)過(guò)查閱資料翻看到阿里的一款POI封裝工具EasyExcel,上面問(wèn)題等到解決;

針對(duì)2:

不能一次性查詢出全部數(shù)據(jù),我們可以分批進(jìn)行查詢,只不過(guò)時(shí)多查詢幾次的問(wèn)題,況且市面上分頁(yè)插件很多。此問(wèn)題好解決。

針對(duì)3:

可以將300w條數(shù)據(jù)寫(xiě)到不同的Sheet中,每一個(gè)Sheet寫(xiě)一百萬(wàn)即可。

針對(duì)4:

不能一行一行的寫(xiě)入到Excel上,我們可以將分批查詢的數(shù)據(jù)分批寫(xiě)入到Excel中。

針對(duì)5:

導(dǎo)入到DB時(shí)我們可以將Excel中讀取的數(shù)據(jù)存儲(chǔ)到集合中,到了一定數(shù)量,直接批量插入到DB中。

針對(duì)6:

不能使用Mybatis的批量插入,我們可以使用JDBC的批量插入,配合事務(wù)來(lái)完成批量插入到DB。即 Excel讀取分批+JDBC分批插入+事務(wù)。

3.1 模擬500w數(shù)據(jù)導(dǎo)出

需求:使用EasyExcel完成500w數(shù)據(jù)的導(dǎo)出。

500w數(shù)據(jù)的導(dǎo)出解決思路:

  • 首先在查詢數(shù)據(jù)庫(kù)層面,需要分批進(jìn)行查詢(比如每次查詢20w)
  • 每查詢一次結(jié)束,就使用EasyExcel工具將這些數(shù)據(jù)寫(xiě)入一次;
  • 當(dāng)一個(gè)Sheet寫(xiě)滿了100w條數(shù)據(jù),開(kāi)始將查詢的數(shù)據(jù)寫(xiě)入到另一個(gè)Sheet中;
  • 如此循環(huán)直到數(shù)據(jù)全部導(dǎo)出到Excel完畢。

ps:我們需要計(jì)算Sheet個(gè)數(shù),以及循環(huán)寫(xiě)入次數(shù)。特別是最后一個(gè)Sheet的寫(xiě)入次數(shù)

因?yàn)槟悴恢雷詈笠粋€(gè)Sheet會(huì)寫(xiě)入多少數(shù)據(jù),可能是100w,也可能是25w因?yàn)槲覀冞@里的500w只是模擬數(shù)據(jù),有可能導(dǎo)出的數(shù)據(jù)比500w多也可能少

ps:我們需要計(jì)算寫(xiě)入次數(shù),因?yàn)槲覀兪褂玫姆猪?yè)查詢,所以需要注意寫(xiě)入的次數(shù)。

其實(shí)查詢數(shù)據(jù)庫(kù)多少次就是寫(xiě)入多少次

準(zhǔn)備工作

1.基于maven搭建springboot工程,引入easyexcel依賴,這里我是用的時(shí)3.0版本

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>3.0.5</version>
</dependency>

2.創(chuàng)建海量數(shù)據(jù)的sql腳本

CREATE TABLE dept( /*部門(mén)表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#創(chuàng)建表EMP雇員
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*編號(hào)*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級(jí)編號(hào)*/
hiredate DATE NOT NULL,/*入職時(shí)間*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*紅利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門(mén)編號(hào)*/
) ;

#工資級(jí)別表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#測(cè)試數(shù)據(jù)
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $$

#創(chuàng)建一個(gè)函數(shù),名字 rand_string,可以隨機(jī)返回我指定的個(gè)數(shù)字符串
create function rand_string(n INT)
returns varchar(255) #該函數(shù)會(huì)返回一個(gè)字符串
begin
#定義了一個(gè)變量 chars_str, 類型  varchar(100)
#默認(rèn)給 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 declare return_str varchar(255) default '';
 declare i int default 0; 
 while i < n do
    # concat 函數(shù) : 連接函數(shù)mysql函數(shù)
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end $$


 #這里我們又自定了一個(gè)函數(shù),返回一個(gè)隨機(jī)的部門(mén)號(hào)
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$

 #創(chuàng)建一個(gè)存儲(chǔ)過(guò)程, 可以添加雇員
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把a(bǔ)utocommit設(shè)置成0
 #autocommit = 0 含義: 不要自動(dòng)提交
 set autocommit = 0; #默認(rèn)不提交sql語(yǔ)句
 repeat
 set i = i + 1;
 #通過(guò)前面寫(xiě)的函數(shù)隨機(jī)產(chǎn)生字符串和部門(mén)編號(hào),然后加入到emp表
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
 #commit整體提交所有sql語(yǔ)句,提高效率
   commit;
 end $$

 #添加8000000數(shù)據(jù)
call insert_emp(100001,8000000)$$

#命令結(jié)束符,再重新設(shè)置為;
delimiter ;

3.實(shí)體類

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp implements Serializable {
    @ExcelProperty(value = "員工編號(hào)")
    private Integer empno;

    @ExcelProperty(value = "員工名稱")
    private String ename;

    @ExcelProperty(value = "工作")
    private String job;

    @ExcelProperty(value = "主管編號(hào)")
    private Integer mgr;

    @ExcelProperty(value = "入職日期")
    private Date hiredate;

    @ExcelProperty(value = "薪資")
    private BigDecimal sal;

    @ExcelProperty(value = "獎(jiǎng)金")
    private BigDecimal comm;

    @ExcelProperty(value = "所屬部門(mén)")
    private Integer deptno;

}

4.vo類

@Data
public class EmpVo {

    @ExcelProperty(value = "員工編號(hào)")
    private Integer empno;

    @ExcelProperty(value = "員工名稱")
    private String ename;

    @ExcelProperty(value = "工作")
    private String job;

    @ExcelProperty(value = "主管編號(hào)")
    private Integer mgr;

    @ExcelProperty(value = "入職日期")
    private Date hiredate;

    @ExcelProperty(value = "薪資")
    private BigDecimal sal;

    @ExcelProperty(value = "獎(jiǎng)金")
    private BigDecimal comm;

    @ExcelProperty(value = "所屬部門(mén)")
    private Integer deptno;

}

導(dǎo)出核心代碼

@Resource
private EmpService empService;
/**
 * 分批次導(dǎo)出
 */
@GetMapping("/export")
public void export() throws IOException {
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    empService.export();
    stopWatch.stop();
    System.out.println("共計(jì)耗時(shí): " + stopWatch.getTotalTimeSeconds()+"S");
}
public class ExcelConstants {
    //一個(gè)sheet裝100w數(shù)據(jù)
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;
    //每次查詢20w數(shù)據(jù),每次寫(xiě)入20w數(shù)據(jù)
    public static final Integer PER_WRITE_ROW_COUNT = 200000;
}
@Override
public void export() throws IOException {
    OutputStream outputStream =null;
    try {
        //記錄總數(shù):實(shí)際中需要根據(jù)查詢條件進(jìn)行統(tǒng)計(jì)即可
        //LambdaQueryWrapper<Emp> lambdaQueryWrapper = new QueryWrapper<Emp>().lambda().eq(Emp::getEmpno, 1000001);
        Integer totalCount = empMapper.selectCount(null);
        //每一個(gè)Sheet存放100w條數(shù)據(jù)
        Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
        //每次寫(xiě)入的數(shù)據(jù)量20w,每頁(yè)查詢20W
        Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
        //計(jì)算需要的Sheet數(shù)量
        Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
        //計(jì)算一般情況下每一個(gè)Sheet需要寫(xiě)入的次數(shù)(一般情況不包含最后一個(gè)sheet,因?yàn)樽詈笠粋€(gè)sheet不確定會(huì)寫(xiě)入多少條數(shù)據(jù))
        Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
        //計(jì)算最后一個(gè)sheet需要寫(xiě)入的次數(shù)
        Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        outputStream = response.getOutputStream();
        //必須放到循環(huán)外,否則會(huì)刷新流
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();

        //開(kāi)始分批查詢分次寫(xiě)入
        for (int i = 0; i < sheetNum; i++) {
            //創(chuàng)建Sheet
            WriteSheet sheet = new WriteSheet();
            sheet.setSheetName("測(cè)試Sheet1"+i);
            sheet.setSheetNo(i);
            //循環(huán)寫(xiě)入次數(shù): j的自增條件是當(dāng)不是最后一個(gè)Sheet的時(shí)候?qū)懭氪螖?shù)為正常的每個(gè)Sheet寫(xiě)入的次數(shù),如果是最后一個(gè)就需要使用計(jì)算的次數(shù)lastSheetWriteCount
            for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                //分頁(yè)查詢一次20w
                Page<Emp> page = empMapper.selectPage(new Page(j + 1 + oneSheetWriteCount * i, writeDataRows), null);
                List<Emp> empList = page.getRecords();
                List<EmpVo> empVoList = new ArrayList<>();
                for (Emp emp : empList) {
                    EmpVo empVo = new EmpVo();
                    BeanUtils.copyProperties(emp, empVo);
                    empVoList.add(empVo);
                }
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "員工信息" + (i + 1)).head(EmpVo.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
                //寫(xiě)數(shù)據(jù)
                excelWriter.write(empVoList, writeSheet);
            }
        }
        // 下載EXCEL
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 這里URLEncoder.encode可以防止瀏覽器端導(dǎo)出excel文件名中文亂碼 當(dāng)然和easyexcel沒(méi)有關(guān)系
        String fileName = URLEncoder.encode("員工信息", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        excelWriter.finish();
        outputStream.flush();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (BeansException e) {
        e.printStackTrace();
    }finally {
        if (outputStream != null) {
            outputStream.close();
        }
    }
}

這是我電腦測(cè)試時(shí)內(nèi)存占用和CPU使用情況,當(dāng)然開(kāi)了其他一些應(yīng)用。

導(dǎo)出500w數(shù)據(jù)共計(jì)耗時(shí),可以看到差不多400s左右,當(dāng)然還要考慮業(yè)務(wù)復(fù)雜度已經(jīng)電腦配置,我這里只是一個(gè)導(dǎo)出的demo并不涉及其他業(yè)務(wù)邏輯,在實(shí)際開(kāi)發(fā)中可能時(shí)間會(huì)比這個(gè)更長(zhǎng)一些

看下導(dǎo)出效果,我上面的腳本向插入了500w數(shù)據(jù),100w一個(gè)sheet因此正好五個(gè)

3.2模擬500w數(shù)據(jù)導(dǎo)入

500W數(shù)據(jù)的導(dǎo)入解決思路

1、首先是分批讀取讀取Excel中的500w數(shù)據(jù),這一點(diǎn)EasyExcel有自己的解決方案,我們可以參考Demo即可,只需要把它分批的參數(shù)5000調(diào)大即可。

2、其次就是往DB里插入,怎么去插入這20w條數(shù)據(jù),當(dāng)然不能一條一條的循環(huán),應(yīng)該批量插入這20w條數(shù)據(jù),同樣也不能使用Mybatis的批量插入語(yǔ),因?yàn)樾室驳汀?/p>

3、使用JDBC+事務(wù)的批量操作將數(shù)據(jù)插入到數(shù)據(jù)庫(kù)。(分批讀取+JDBC分批插入+手動(dòng)事務(wù)控制)

代碼實(shí)現(xiàn)

controller層測(cè)試接口

@Resource
private EmpService empService;

@GetMapping("/importData")
public void importData() {
    String fileName = "C:\\Users\\asus\\Desktop\\員工信息.xlsx";
    //記錄開(kāi)始讀取Excel時(shí)間,也是導(dǎo)入程序開(kāi)始時(shí)間
    long startReadTime = System.currentTimeMillis();
    System.out.println("------開(kāi)始讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):" + startReadTime + "ms------");
    //讀取所有Sheet的數(shù)據(jù).每次讀完一個(gè)Sheet就會(huì)調(diào)用這個(gè)方法
    EasyExcel.read(fileName, new EasyExceGeneralDatalListener(empService)).doReadAll();
    long endReadTime = System.currentTimeMillis();
    System.out.println("------結(jié)束讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):" + endReadTime + "ms------");
    System.out.println("------讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù))共計(jì)耗時(shí):" + (endReadTime-startReadTime) + "ms------");
}

Excel導(dǎo)入事件監(jiān)聽(tīng)

// 事件監(jiān)聽(tīng)
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
    /**
     * 處理業(yè)務(wù)邏輯的Service,也可以是Mapper
     */
    private EmpService empService;

    /**
     * 用于存儲(chǔ)讀取的數(shù)據(jù)
     */
    private List<Map<Integer, String>> dataList = new ArrayList<Map<Integer, String>>();

    public EasyExceGeneralDatalListener() {
    }

    public EasyExceGeneralDatalListener(EmpService empService) {
        this.empService = empService;
    }

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        //數(shù)據(jù)add進(jìn)入集合
        dataList.add(data);
        //size是否為100000條:這里其實(shí)就是分批.當(dāng)數(shù)據(jù)等于10w的時(shí)候執(zhí)行一次插入
        if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS) {
            //存入數(shù)據(jù)庫(kù):數(shù)據(jù)小于1w條使用Mybatis的批量插入即可;
            saveData();
            //清理集合便于GC回收
            dataList.clear();
        }
    }

    /**
     * 保存數(shù)據(jù)到DB
     *
     * @param
     * @MethodName: saveData
     * @return: void
     */
    private void saveData() {
        empService.importData(dataList);
        dataList.clear();
    }

    /**
     * Excel中所有數(shù)據(jù)解析完畢會(huì)調(diào)用此方法
     *
     * @param: context
     * @MethodName: doAfterAllAnalysed
     * @return: void
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        dataList.clear();
    }
}

核心業(yè)務(wù)代碼

public interface EmpService {
    void export() throws IOException;

    void importData(List<Map<Integer, String>> dataList);

}
    /*
     * 測(cè)試用Excel導(dǎo)入超過(guò)10w條數(shù)據(jù),經(jīng)過(guò)測(cè)試發(fā)現(xiàn),使用Mybatis的批量插入速度非常慢,所以這里可以使用 數(shù)據(jù)分批+JDBC分批插入+事務(wù)來(lái)繼續(xù)插入速度會(huì)非常快
    */
    @Override
    public void importData(List<Map<Integer, String>> dataList) {
        //結(jié)果集中數(shù)據(jù)為0時(shí),結(jié)束方法.進(jìn)行下一次調(diào)用
        if (dataList.size() == 0) {
            return;
        }
        //JDBC分批插入+事務(wù)操作完成對(duì)20w數(shù)據(jù)的插入
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long startTime = System.currentTimeMillis();
            System.out.println(dataList.size() + "條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:" + startTime + "ms");
            conn = JDBCDruidUtils.getConnection();
            //控制事務(wù):默認(rèn)不提交
            conn.setAutoCommit(false);
            String sql = "insert into emp (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values";
            sql += "(?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            //循環(huán)結(jié)果集:這里循環(huán)不支持lambda表達(dá)式
            for (int i = 0; i < dataList.size(); i++) {
                Map<Integer, String> item = dataList.get(i);
                ps.setString(1, item.get(0));
                ps.setString(2, item.get(1));
                ps.setString(3, item.get(2));
                ps.setString(4, item.get(3));
                ps.setString(5, item.get(4));
                ps.setString(6, item.get(5));
                ps.setString(7, item.get(6));
                ps.setString(8, item.get(7));
                //將一組參數(shù)添加到此 PreparedStatement 對(duì)象的批處理命令中。
                ps.addBatch();
            }
            //執(zhí)行批處理
            ps.executeBatch();
            //手動(dòng)提交事務(wù)
            conn.commit();
            long endTime = System.currentTimeMillis();
            System.out.println(dataList.size() + "條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:" + endTime + "ms");
            System.out.println(dataList.size() + "條,導(dǎo)入用時(shí):" + (endTime - startTime) + "ms");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //關(guān)連接
            JDBCDruidUtils.close(conn, ps);
        }
    }

???????}

jdbc工具類

//JDBC工具類
public class JDBCDruidUtils {
    private static DataSource dataSource;

    /*
   創(chuàng)建數(shù)據(jù)Properties集合對(duì)象加載加載配置文件
    */
    static {
        Properties pro = new Properties();
        //加載數(shù)據(jù)庫(kù)連接池對(duì)象
        try {
            //獲取數(shù)據(jù)庫(kù)連接池對(duì)象
            pro.load(JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /*
    獲取連接
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }


    /**
     * 關(guān)閉conn,和 statement獨(dú)對(duì)象資源
     *
     * @param connection
     * @param statement
     * @MethodName: close
     * @return: void
     */
    public static void close(Connection connection, Statement statement) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 關(guān)閉 conn , statement 和resultset三個(gè)對(duì)象資源
     *
     * @param connection
     * @param statement
     * @param resultSet
     * @MethodName: close
     * @return: void
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        close(connection, statement);
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /*
    獲取連接池對(duì)象
     */
    public static DataSource getDataSource() {
        return dataSource;
    }

}

druid.properties配置文件

這里我將文件創(chuàng)建在類路徑下,需要注意的是連接mysql數(shù)據(jù)庫(kù)時(shí)需要指定rewriteBatchedStatements=true批處理才會(huì)生效,否則還是逐條插入效率較低,allowMultiQueries=true表示可以使sql語(yǔ)句中有多個(gè)insert或者update語(yǔ)句(語(yǔ)句之間攜帶分號(hào)),這里可以忽略。

# druid.properties配置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/llp?autoReconnect=true&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true
username=root
password=root
initialSize=10
maxActive=50
maxWait=60000

測(cè)試結(jié)果

------開(kāi)始讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):1674181403555ms------
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1674181409740ms
2023-01-20 10:23:29.943  INFO 18580 --- [nio-8888-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1674181413252ms
200000條,導(dǎo)入用時(shí):3512ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1674181418422ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1674181420999ms
200000條,導(dǎo)入用時(shí):2577ms
.....
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1674181607405ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1674181610154ms
200000條,導(dǎo)入用時(shí):2749ms
------結(jié)束讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):1674181610155ms------
------讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù))共計(jì)耗時(shí):206600ms------

這里我刪除里部分日志,從打印結(jié)果可以看出,在我的電腦上導(dǎo)入500w數(shù)據(jù)差不多需要20多秒的時(shí)間,還是很快的。當(dāng)然公司的業(yè)務(wù)邏輯很復(fù)雜,數(shù)據(jù)量也比較多,表的字段也比較多,導(dǎo)入和導(dǎo)出的速度會(huì)比現(xiàn)在測(cè)試的要慢一點(diǎn)。

4.總結(jié)

1.如此大批量數(shù)據(jù)的導(dǎo)出和導(dǎo)入操作,會(huì)占用大量的內(nèi)存實(shí)際開(kāi)發(fā)中還應(yīng)限制操作人數(shù)。

2.在做大批量的數(shù)據(jù)導(dǎo)入時(shí),可以使用jdbc手動(dòng)開(kāi)啟事務(wù),批量提交。

以上就是基于EasyExcel實(shí)現(xiàn)百萬(wàn)級(jí)數(shù)據(jù)導(dǎo)入導(dǎo)出詳解的詳細(xì)內(nèi)容,更多關(guān)于EasyExcel數(shù)據(jù)導(dǎo)入導(dǎo)出的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!

相關(guān)文章

  • springboot項(xiàng)目中使用Swagger的簡(jiǎn)單示例

    springboot項(xiàng)目中使用Swagger的簡(jiǎn)單示例

    大趨勢(shì)下目前很多的項(xiàng)目都采用了前后端分離的方式進(jìn)行開(kāi)發(fā),最近我接觸到的項(xiàng)目大多數(shù)都是采用了前后端分離的方式進(jìn)行開(kāi)發(fā),下面這篇文章主要給大家介紹了關(guān)于springboot項(xiàng)目中使用Swagger的簡(jiǎn)單示例,需要的朋友可以參考下
    2023-04-04
  • Java實(shí)現(xiàn)字符串和輸入流的相互轉(zhuǎn)換

    Java實(shí)現(xiàn)字符串和輸入流的相互轉(zhuǎn)換

    這篇文章主要介紹了Java實(shí)現(xiàn)字符串和輸入流的相互轉(zhuǎn)換方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2022-08-08
  • java8新特性之stream流中reduce()求和知識(shí)總結(jié)

    java8新特性之stream流中reduce()求和知識(shí)總結(jié)

    今天帶大家回顧Java8的新特性,文中對(duì)stream流中reduce()求和的相關(guān)知識(shí)作了詳細(xì)的介紹,對(duì)正在學(xué)習(xí)java的小伙伴們有很好地幫助,需要的朋友可以參考下
    2021-05-05
  • Java內(nèi)存泄漏問(wèn)題排查與解決

    Java內(nèi)存泄漏問(wèn)題排查與解決

    大家好,本篇文章主要講的是Java內(nèi)存泄漏問(wèn)題排查與解決,感興趣的同學(xué)趕快來(lái)看一看吧,對(duì)你有幫助的話記得收藏一下
    2022-01-01
  • java使用鏈表實(shí)現(xiàn)約瑟夫環(huán)

    java使用鏈表實(shí)現(xiàn)約瑟夫環(huán)

    這篇文章主要為大家詳細(xì)介紹了java使用鏈表實(shí)現(xiàn)約瑟夫環(huán),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2019-05-05
  • Java線程池框架核心代碼解析

    Java線程池框架核心代碼解析

    這篇文章主要針對(duì)Java線程池框架核心代碼進(jìn)行詳細(xì)解析,分析Java線程池框架的實(shí)現(xiàn)ThreadPoolExecutor,感興趣的小伙伴們可以參考一下
    2016-07-07
  • java虛擬機(jī)多線程進(jìn)階篇總結(jié)

    java虛擬機(jī)多線程進(jìn)階篇總結(jié)

    在本篇內(nèi)容里小編給大家整理了關(guān)于java虛擬機(jī)多線程進(jìn)階篇的相關(guān)知識(shí)點(diǎn)內(nèi)容,有興趣的朋友們跟著參考下。
    2019-06-06
  • java單鏈表使用總結(jié)

    java單鏈表使用總結(jié)

    這篇文章主要為大家詳細(xì)介紹了java單鏈表使用總結(jié),文中示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2022-01-01
  • Java生成驗(yàn)證碼功能實(shí)例代碼

    Java生成驗(yàn)證碼功能實(shí)例代碼

    頁(yè)面上輸入驗(yàn)證碼是比較常見(jiàn)的一個(gè)功能,實(shí)現(xiàn)起來(lái)也很簡(jiǎn)單.給大家寫(xiě)一個(gè)簡(jiǎn)單的生成驗(yàn)證碼的示例程序,需要的朋友可以借鑒一下
    2017-05-05
  • java入門(mén)概念個(gè)人理解之package與import淺析

    java入門(mén)概念個(gè)人理解之package與import淺析

    下面小編就為大家?guī)?lái)一篇java入門(mén)概念個(gè)人理解之package與import淺析。小編覺(jué)得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧
    2016-08-08

最新評(píng)論