基于EasyExcel實現(xiàn)百萬級數(shù)據(jù)導(dǎo)入導(dǎo)出詳解
在項目開發(fā)中往往需要使用到數(shù)據(jù)的導(dǎo)入和導(dǎo)出,導(dǎo)入就是從Excel中導(dǎo)入到DB中,而導(dǎo)出就是從DB中查詢數(shù)據(jù)然后使用POI寫到Excel上。
大數(shù)據(jù)的導(dǎo)入和導(dǎo)出,相信大家在日常的開發(fā)、面試中都會遇到。
很多問題只要這一次解決了,總給復(fù)盤記錄,后期遇到同樣的問題就好解決了。好啦,廢話不多說開始正文!
1.傳統(tǒng)POI的的版本優(yōu)缺點比較
其實想到數(shù)據(jù)的導(dǎo)入導(dǎo)出,理所當(dāng)然的會想到apache的poi技術(shù),以及Excel的版本問題。
HSSFWorkbook
這個實現(xiàn)類是我們早期使用最多的對象,它可以操作Excel2003以前(包含2003)的所有Excel版本。在2003以前Excel的版本后綴還是.xls
XSSFWorkbook
這個實現(xiàn)類現(xiàn)在在很多公司都可以發(fā)現(xiàn)還在使用,它是操作的Excel2003–Excel2007之間的版本,Excel的擴(kuò)展名是.xlsx
SXSSFWorkbook
這個實現(xiàn)類是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,擴(kuò)展名是.xlsx
HSSFWorkbook
它是POI版本中最常用的方式,不過:
- 它的缺點是 最多只能導(dǎo)出 65535行,也就是導(dǎo)出的數(shù)據(jù)函數(shù)超過這個數(shù)據(jù)就會報錯;
- 它的優(yōu)點是 不會報內(nèi)存溢出。(因為數(shù)據(jù)量還不到7w所以內(nèi)存一般都夠用,首先你得明確知道這種方式是將數(shù)據(jù)先讀取到內(nèi)存中,然后再操作)
XSSFWorkbook
- 優(yōu)點:這種形式的出現(xiàn)是為了突破HSSFWorkbook的65535行局限,是為了針對Excel2007版本的1048576行,16384列,最多可以導(dǎo)出104w條數(shù)據(jù);
- 缺點:伴隨的問題來了,雖然導(dǎo)出數(shù)據(jù)行數(shù)增加了好多倍,但是隨之而來的內(nèi)存溢出問題也成了噩夢。因為你所創(chuàng)建的book,Sheet,row,cell等在寫入到Excel之前,都是存放在內(nèi)存中的(這還沒有算Excel的一些樣式格式等等),可想而知,內(nèi)存不溢出就有點不科學(xué)了?。?!
SXSSFWorkbook
從POI 3.8版本開始,提供了一種基于XSSF的低內(nèi)存占用的SXSSF方式:
優(yōu)點:
- 這種方式不會一般不會出現(xiàn)內(nèi)存溢出(它使用了硬盤來換取內(nèi)存空間,
- 也就是當(dāng)內(nèi)存中數(shù)據(jù)達(dá)到一定程度這些數(shù)據(jù)會被持久化到硬盤中存儲起來,而內(nèi)存中存的都是最新的數(shù)據(jù)),
- 并且支持大型Excel文件的創(chuàng)建(存儲百萬條數(shù)據(jù)綽綽有余)。
缺點:
- 既然一部分?jǐn)?shù)據(jù)持久化到了硬盤中,且不能被查看和訪問那么就會導(dǎo)致,
- 在同一時間點我們只能訪問一定數(shù)量的數(shù)據(jù),也就是內(nèi)存中存儲的數(shù)據(jù);
- sheet.clone()方法將不再支持,還是因為持久化的原因;
- 不再支持對公式的求值,還是因為持久化的原因,在硬盤中的數(shù)據(jù)沒法讀取到內(nèi)存中進(jìn)行計算;
- 在使用模板方式下載數(shù)據(jù)的時候,不能改動表頭,還是因為持久化的問題,寫到了硬盤里就不能改變了;
2.使用方式哪種看情況
經(jīng)過了解也知道了這三種Workbook的優(yōu)點和缺點,那么具體使用哪種方式還是需要看情況的:
我一般會根據(jù)這樣幾種情況做分析選擇:
1、當(dāng)我們經(jīng)常導(dǎo)入導(dǎo)出的數(shù)據(jù)不超過7w的情況下,可以使用 HSSFWorkbook 或者 XSSFWorkbook都行;
2、當(dāng)數(shù)據(jù)量查過7w并且導(dǎo)出的Excel中不牽扯對Excel的樣式,公式,格式等操作的情況下,推薦使用SXSSFWorkbook;
3、當(dāng)數(shù)據(jù)量查過7w,并且我們需要操做Excel中的表頭,樣式,公式等,這時候我們可以使用 XSSFWorkbook 配合進(jìn)行分批查詢,分批寫入Excel的方式來做;
3.百萬數(shù)據(jù)導(dǎo)入導(dǎo)出
想要解決問題我們首先要明白自己遇到的問題是什么?
1、 我遇到的數(shù)據(jù)量超級大,使用傳統(tǒng)的POI方式來完成導(dǎo)入導(dǎo)出很明顯會內(nèi)存溢出,并且效率會非常低;
2、 數(shù)據(jù)量大直接使用select * from tableName肯定不行,一下子查出來300w條數(shù)據(jù)肯定會很慢;
3、 300w 數(shù)據(jù)導(dǎo)出到Excel時肯定不能都寫在一個Sheet中,這樣效率會非常低;估計打開都得幾分鐘;
4、 300w數(shù)據(jù)導(dǎo)出到Excel中肯定不能一行一行的導(dǎo)出到Excel中。頻繁IO操作絕對不行;
5、 導(dǎo)入時300萬數(shù)據(jù)存儲到DB如果循環(huán)一條條插入也肯定不行;
6、導(dǎo)入時300w數(shù)據(jù)如果使用Mybatis的批量插入肯定不行,因為Mybatis的批量插入其實就是SQL的循環(huán);一樣很慢。
解決思路:
針對1 :
其實問題所在就是內(nèi)存溢出,我們只要使用對上面介紹的POI方式即可,主要問題就是原生的POI解決起來相當(dāng)麻煩。
經(jīng)過查閱資料翻看到阿里的一款POI封裝工具EasyExcel,上面問題等到解決;
針對2:
不能一次性查詢出全部數(shù)據(jù),我們可以分批進(jìn)行查詢,只不過時多查詢幾次的問題,況且市面上分頁插件很多。此問題好解決。
針對3:
可以將300w條數(shù)據(jù)寫到不同的Sheet中,每一個Sheet寫一百萬即可。
針對4:
不能一行一行的寫入到Excel上,我們可以將分批查詢的數(shù)據(jù)分批寫入到Excel中。
針對5:
導(dǎo)入到DB時我們可以將Excel中讀取的數(shù)據(jù)存儲到集合中,到了一定數(shù)量,直接批量插入到DB中。
針對6:
不能使用Mybatis的批量插入,我們可以使用JDBC的批量插入,配合事務(wù)來完成批量插入到DB。即 Excel讀取分批+JDBC分批插入+事務(wù)。
3.1 模擬500w數(shù)據(jù)導(dǎo)出
需求:使用EasyExcel完成500w數(shù)據(jù)的導(dǎo)出。
500w數(shù)據(jù)的導(dǎo)出解決思路:
- 首先在查詢數(shù)據(jù)庫層面,需要分批進(jìn)行查詢(比如每次查詢20w)
- 每查詢一次結(jié)束,就使用EasyExcel工具將這些數(shù)據(jù)寫入一次;
- 當(dāng)一個Sheet寫滿了100w條數(shù)據(jù),開始將查詢的數(shù)據(jù)寫入到另一個Sheet中;
- 如此循環(huán)直到數(shù)據(jù)全部導(dǎo)出到Excel完畢。
ps:我們需要計算Sheet個數(shù),以及循環(huán)寫入次數(shù)。特別是最后一個Sheet的寫入次數(shù)
因為你不知道最后一個Sheet會寫入多少數(shù)據(jù),可能是100w,也可能是25w因為我們這里的500w只是模擬數(shù)據(jù),有可能導(dǎo)出的數(shù)據(jù)比500w多也可能少
ps:我們需要計算寫入次數(shù),因為我們使用的分頁查詢,所以需要注意寫入的次數(shù)。
其實查詢數(shù)據(jù)庫多少次就是寫入多少次
準(zhǔn)備工作
1.基于maven搭建springboot工程,引入easyexcel依賴,這里我是用的時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( /*部門表*/ 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, /*編號*/ ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/ job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/ mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級編號*/ hiredate DATE NOT NULL,/*入職時間*/ sal DECIMAL(7,2) NOT NULL,/*薪水*/ comm DECIMAL(7,2) NOT NULL,/*紅利*/ deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號*/ ) ; #工資級別表 CREATE TABLE salgrade ( grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, losal DECIMAL(17,2) NOT NULL, hisal DECIMAL(17,2) NOT NULL ); #測試數(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)建一個函數(shù),名字 rand_string,可以隨機(jī)返回我指定的個數(shù)字符串 create function rand_string(n INT) returns varchar(255) #該函數(shù)會返回一個字符串 begin #定義了一個變量 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 $$ #這里我們又自定了一個函數(shù),返回一個隨機(jī)的部門號 create function rand_num( ) returns int(5) begin declare i int default 0; set i = floor(10+rand()*500); return i; end $$ #創(chuàng)建一個存儲過程, 可以添加雇員 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 含義: 不要自動提交 set autocommit = 0; #默認(rèn)不提交sql語句 repeat set i = i + 1; #通過前面寫的函數(shù)隨機(jī)產(chǎn)生字符串和部門編號,然后加入到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語句,提高效率 commit; end $$ #添加8000000數(shù)據(jù) call insert_emp(100001,8000000)$$ #命令結(jié)束符,再重新設(shè)置為; delimiter ;
3.實體類
@Data @NoArgsConstructor @AllArgsConstructor public class Emp implements Serializable { @ExcelProperty(value = "員工編號") private Integer empno; @ExcelProperty(value = "員工名稱") private String ename; @ExcelProperty(value = "工作") private String job; @ExcelProperty(value = "主管編號") private Integer mgr; @ExcelProperty(value = "入職日期") private Date hiredate; @ExcelProperty(value = "薪資") private BigDecimal sal; @ExcelProperty(value = "獎金") private BigDecimal comm; @ExcelProperty(value = "所屬部門") private Integer deptno; }
4.vo類
@Data public class EmpVo { @ExcelProperty(value = "員工編號") private Integer empno; @ExcelProperty(value = "員工名稱") private String ename; @ExcelProperty(value = "工作") private String job; @ExcelProperty(value = "主管編號") private Integer mgr; @ExcelProperty(value = "入職日期") private Date hiredate; @ExcelProperty(value = "薪資") private BigDecimal sal; @ExcelProperty(value = "獎金") private BigDecimal comm; @ExcelProperty(value = "所屬部門") 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("共計耗時: " + stopWatch.getTotalTimeSeconds()+"S"); }
public class ExcelConstants { //一個sheet裝100w數(shù)據(jù) public static final Integer PER_SHEET_ROW_COUNT = 1000000; //每次查詢20w數(shù)據(jù),每次寫入20w數(shù)據(jù) public static final Integer PER_WRITE_ROW_COUNT = 200000; }
@Override public void export() throws IOException { OutputStream outputStream =null; try { //記錄總數(shù):實際中需要根據(jù)查詢條件進(jìn)行統(tǒng)計即可 //LambdaQueryWrapper<Emp> lambdaQueryWrapper = new QueryWrapper<Emp>().lambda().eq(Emp::getEmpno, 1000001); Integer totalCount = empMapper.selectCount(null); //每一個Sheet存放100w條數(shù)據(jù) Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT; //每次寫入的數(shù)據(jù)量20w,每頁查詢20W Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT; //計算需要的Sheet數(shù)量 Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1); //計算一般情況下每一個Sheet需要寫入的次數(shù)(一般情況不包含最后一個sheet,因為最后一個sheet不確定會寫入多少條數(shù)據(jù)) Integer oneSheetWriteCount = sheetDataRows / writeDataRows; //計算最后一個sheet需要寫入的次數(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)外,否則會刷新流 ExcelWriter excelWriter = EasyExcel.write(outputStream).build(); //開始分批查詢分次寫入 for (int i = 0; i < sheetNum; i++) { //創(chuàng)建Sheet WriteSheet sheet = new WriteSheet(); sheet.setSheetName("測試Sheet1"+i); sheet.setSheetNo(i); //循環(huán)寫入次數(shù): j的自增條件是當(dāng)不是最后一個Sheet的時候?qū)懭氪螖?shù)為正常的每個Sheet寫入的次數(shù),如果是最后一個就需要使用計算的次數(shù)lastSheetWriteCount for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) { //分頁查詢一次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(); //寫數(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沒有關(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(); } } }
這是我電腦測試時內(nèi)存占用和CPU使用情況,當(dāng)然開了其他一些應(yīng)用。
導(dǎo)出500w數(shù)據(jù)共計耗時,可以看到差不多400s左右,當(dāng)然還要考慮業(yè)務(wù)復(fù)雜度已經(jīng)電腦配置,我這里只是一個導(dǎo)出的demo并不涉及其他業(yè)務(wù)邏輯,在實際開發(fā)中可能時間會比這個更長一些
看下導(dǎo)出效果,我上面的腳本向插入了500w數(shù)據(jù),100w一個sheet因此正好五個
3.2模擬500w數(shù)據(jù)導(dǎo)入
500W數(shù)據(jù)的導(dǎo)入解決思路
1、首先是分批讀取讀取Excel中的500w數(shù)據(jù),這一點EasyExcel有自己的解決方案,我們可以參考Demo即可,只需要把它分批的參數(shù)5000調(diào)大即可。
2、其次就是往DB里插入,怎么去插入這20w條數(shù)據(jù),當(dāng)然不能一條一條的循環(huán),應(yīng)該批量插入這20w條數(shù)據(jù),同樣也不能使用Mybatis的批量插入語,因為效率也低。
3、使用JDBC+事務(wù)的批量操作將數(shù)據(jù)插入到數(shù)據(jù)庫。(分批讀取+JDBC分批插入+手動事務(wù)控制)
代碼實現(xiàn)
controller層測試接口
@Resource private EmpService empService; @GetMapping("/importData") public void importData() { String fileName = "C:\\Users\\asus\\Desktop\\員工信息.xlsx"; //記錄開始讀取Excel時間,也是導(dǎo)入程序開始時間 long startReadTime = System.currentTimeMillis(); System.out.println("------開始讀取Excel的Sheet時間(包括導(dǎo)入數(shù)據(jù)過程):" + startReadTime + "ms------"); //讀取所有Sheet的數(shù)據(jù).每次讀完一個Sheet就會調(diào)用這個方法 EasyExcel.read(fileName, new EasyExceGeneralDatalListener(empService)).doReadAll(); long endReadTime = System.currentTimeMillis(); System.out.println("------結(jié)束讀取Excel的Sheet時間(包括導(dǎo)入數(shù)據(jù)過程):" + endReadTime + "ms------"); System.out.println("------讀取Excel的Sheet時間(包括導(dǎo)入數(shù)據(jù))共計耗時:" + (endReadTime-startReadTime) + "ms------"); }
Excel導(dǎo)入事件監(jiān)聽
// 事件監(jiān)聽 public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> { /** * 處理業(yè)務(wù)邏輯的Service,也可以是Mapper */ private EmpService empService; /** * 用于存儲讀取的數(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條:這里其實就是分批.當(dāng)數(shù)據(jù)等于10w的時候執(zhí)行一次插入 if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS) { //存入數(shù)據(jù)庫:數(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ù)解析完畢會調(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); }
/* * 測試用Excel導(dǎo)入超過10w條數(shù)據(jù),經(jīng)過測試發(fā)現(xiàn),使用Mybatis的批量插入速度非常慢,所以這里可以使用 數(shù)據(jù)分批+JDBC分批插入+事務(wù)來繼續(xù)插入速度會非??? */ @Override public void importData(List<Map<Integer, String>> dataList) { //結(jié)果集中數(shù)據(jù)為0時,結(jié)束方法.進(jìn)行下一次調(diào)用 if (dataList.size() == 0) { return; } //JDBC分批插入+事務(wù)操作完成對20w數(shù)據(jù)的插入 Connection conn = null; PreparedStatement ps = null; try { long startTime = System.currentTimeMillis(); System.out.println(dataList.size() + "條,開始導(dǎo)入到數(shù)據(jù)庫時間:" + 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 對象的批處理命令中。 ps.addBatch(); } //執(zhí)行批處理 ps.executeBatch(); //手動提交事務(wù) conn.commit(); long endTime = System.currentTimeMillis(); System.out.println(dataList.size() + "條,結(jié)束導(dǎo)入到數(shù)據(jù)庫時間:" + endTime + "ms"); System.out.println(dataList.size() + "條,導(dǎo)入用時:" + (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集合對象加載加載配置文件 */ static { Properties pro = new Properties(); //加載數(shù)據(jù)庫連接池對象 try { //獲取數(shù)據(jù)庫連接池對象 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獨對象資源 * * @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三個對象資源 * * @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(); } } } /* 獲取連接池對象 */ public static DataSource getDataSource() { return dataSource; } }
druid.properties配置文件
這里我將文件創(chuàng)建在類路徑下,需要注意的是連接mysql數(shù)據(jù)庫時需要指定rewriteBatchedStatements=true批處理才會生效,否則還是逐條插入效率較低,allowMultiQueries=true表示可以使sql語句中有多個insert或者update語句(語句之間攜帶分號),這里可以忽略。
# 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
測試結(jié)果
------開始讀取Excel的Sheet時間(包括導(dǎo)入數(shù)據(jù)過程):1674181403555ms------
200000條,開始導(dǎo)入到數(shù)據(jù)庫時間: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ù)庫時間:1674181413252ms
200000條,導(dǎo)入用時:3512ms
200000條,開始導(dǎo)入到數(shù)據(jù)庫時間:1674181418422ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫時間:1674181420999ms
200000條,導(dǎo)入用時:2577ms
.....
200000條,開始導(dǎo)入到數(shù)據(jù)庫時間:1674181607405ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫時間:1674181610154ms
200000條,導(dǎo)入用時:2749ms
------結(jié)束讀取Excel的Sheet時間(包括導(dǎo)入數(shù)據(jù)過程):1674181610155ms------
------讀取Excel的Sheet時間(包括導(dǎo)入數(shù)據(jù))共計耗時:206600ms------
這里我刪除里部分日志,從打印結(jié)果可以看出,在我的電腦上導(dǎo)入500w數(shù)據(jù)差不多需要20多秒的時間,還是很快的。當(dāng)然公司的業(yè)務(wù)邏輯很復(fù)雜,數(shù)據(jù)量也比較多,表的字段也比較多,導(dǎo)入和導(dǎo)出的速度會比現(xiàn)在測試的要慢一點。
4.總結(jié)
1.如此大批量數(shù)據(jù)的導(dǎo)出和導(dǎo)入操作,會占用大量的內(nèi)存實際開發(fā)中還應(yīng)限制操作人數(shù)。
2.在做大批量的數(shù)據(jù)導(dǎo)入時,可以使用jdbc手動開啟事務(wù),批量提交。
以上就是基于EasyExcel實現(xiàn)百萬級數(shù)據(jù)導(dǎo)入導(dǎo)出詳解的詳細(xì)內(nèi)容,更多關(guān)于EasyExcel數(shù)據(jù)導(dǎo)入導(dǎo)出的資料請關(guān)注腳本之家其它相關(guān)文章!
- SpringBoot中EasyExcel實現(xiàn)Excel文件的導(dǎo)入導(dǎo)出
- 使用SpringBoot+EasyExcel+Vue實現(xiàn)excel表格的導(dǎo)入和導(dǎo)出詳解
- SpringBoot整合EasyExcel實現(xiàn)文件導(dǎo)入導(dǎo)出
- Java使用EasyExcel實現(xiàn)Excel的導(dǎo)入導(dǎo)出
- Java+EasyExcel實現(xiàn)文件的導(dǎo)入導(dǎo)出
- java使用EasyExcel導(dǎo)入導(dǎo)出excel
- 教您如何3分鐘快速搞定EasyExcel導(dǎo)入與導(dǎo)出功能
相關(guān)文章
Java實現(xiàn)字符串和輸入流的相互轉(zhuǎn)換
這篇文章主要介紹了Java實現(xiàn)字符串和輸入流的相互轉(zhuǎn)換方式,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2022-08-08java8新特性之stream流中reduce()求和知識總結(jié)
今天帶大家回顧Java8的新特性,文中對stream流中reduce()求和的相關(guān)知識作了詳細(xì)的介紹,對正在學(xué)習(xí)java的小伙伴們有很好地幫助,需要的朋友可以參考下2021-05-05