亚洲成A人片在线观看,免费人成在线观看,国产午夜福利在线播放,吃避孕药的危害,菠萝蜜视频在线观看,亚洲成AV人综合在线观看,丰满双乳秘书被老板狂揉捏,丰满少妇张开大白腿摄影图片

前景

1 傳統POI的的版本優缺點比較

2 使用方式哪種看情況

3 百萬數據導入導出（正菜）

4 總結

前景

在項目開發中往往需要使用到數據的導入和導出，導入就是從Excel中導入到DB中,而導出就是從DB中查詢數據然后使用POI寫到Excel上。

寫本文的背景是因為在工作中遇到了大數據的導入和導出，問題既然來了逃跑不如干掉它！！！

只要這一次解決了，后期遇到同樣的問題就好解決了。

廢話不多說，開始擼起來！！！

1 傳統POI的的版本優缺點比較

其實想到數據的導入導出，理所當然的會想到apache的poi技術，以及Excel的版本問題。

既然要做導入導出，那么我們就先來大致看一下傳統poi技術的版本以及優缺點對比吧！

首先我們知道POI中我們最熟悉的莫過于WorkBook這樣一個接口，我們的POI版本也在更新的同時對這個幾口的實現類做了更新：

HSSFWorkbook :

這個實現類是我們早期使用最多的對象，它可以操作Excel2003以前（包含2003）的所有Excel版本。在2003以前Excel的版本后綴還是.xls

XSSFWorkbook :

這個實現類現在在很多公司都可以發現還在使用，它是操作的Excel2003--Excel2007之間的版本，Excel的擴展名是.xlsx

SXSSFWorkbook :

這個實現類是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,擴展名是.xlsx

大致知道了我們在導入導出操作的時候會用到這樣三個實現類以及他們可以操作的Excel版本和后綴之后,我們就要從優缺點分析他們了

HSSFWorkbook

它是POI版本中最常用的方式，不過:

它的缺點是最多只能導出 65535行，也就是導出的數據函數超過這個數據就會報錯;

它的優點是不會報內存溢出。（因為數據量還不到7w所以內存一般都夠用，首先你得明確知道這種方式是將數據先讀取到內存中，然后再操作）

XSSFWorkbook

優點：這種形式的出現是為了突破HSSFWorkbook的65535行局限，是為了針對Excel2007版本的1048576行，16384列，最多可以導出104w條數據；

缺點：伴隨的問題來了，雖然導出數據行數增加了好多倍，但是隨之而來的內存溢出問題也成了噩夢。因為你所創建的book，Sheet，row，cell等在寫入到Excel之前，都是存放在內存中的（這還沒有算Excel的一些樣式格式等等），可想而知，內存不溢出就有點不科學了！！！

SXSSFWorkbook

從POI 3.8版本開始，提供了一種基于XSSF的低內存占用的SXSSF方式：

優點：

這種方式不會一般不會出現內存溢出（它使用了硬盤來換取內存空間，

也就是當內存中數據達到一定程度這些數據會被持久化到硬盤中存儲起來，而內存中存的都是最新的數據），

并且支持大型Excel文件的創建（存儲百萬條數據綽綽有余）。

缺點：

既然一部分數據持久化到了硬盤中，且不能被查看和訪問那么就會導致，

在同一時間點我們只能訪問一定數量的數據，也就是內存中存儲的數據;

sheet.clone()方法將不再支持,還是因為持久化的原因;

不再支持對公式的求值，還是因為持久化的原因，在硬盤中的數據沒法讀取到內存中進行計算；

在使用模板方式下載數據的時候，不能改動表頭，還是因為持久化的問題，寫到了硬盤里就不能改變了；

2 使用方式哪種看情況

經過了解也知道了這三種Workbook的優點和缺點，那么具體使用哪種方式還是需要看情況的：

我一般會根據這樣幾種情況做分析選擇：

1、當我們經常導入導出的數據不超過7w的情況下，可以使用 HSSFWorkbook 或者 XSSFWorkbook都行；

2、當數據量查過7w并且導出的Excel中不牽扯對Excel的樣式，公式，格式等操作的情況下，推薦使用SXSSFWorkbook;

3、當數據量查過7w，并且我們需要操做Excel中的表頭，樣式，公式等，這時候我們可以使用 XSSFWorkbook 配合進行分批查詢，分批寫入Excel的方式來做；

3 百萬數據導入導出（正菜）

鋪墊也做了不少，那么現在開始講講我在工作中遇到的超百萬數據的導入導出解決方案：

想要解決問題我們首先要明白自己遇到的問題是什么？

1、我遇到的數據量超級大，使用傳統的POI方式來完成導入導出很明顯會內存溢出，并且效率會非常低；

2、數據量大直接使用select * from tableName肯定不行，一下子查出來300w條數據肯定會很慢；

3、 300w 數據導出到Excel時肯定不能都寫在一個Sheet中，這樣效率會非常低；估計打開都得幾分鐘；

4、 300w數據導出到Excel中肯定不能一行一行的導出到Excel中。頻繁IO操作絕對不行；

5、導入時300萬數據存儲到DB如果循環一條條插入也肯定不行；

6、導入時300w數據如果使用Mybatis的批量插入肯定不行，因為Mybatis的批量插入其實就是SQL的循環；一樣很慢。

解決思路：

針對1 ：

其實問題所在就是內存溢出，我們只要使用對上面介紹的POI方式即可，主要問題就是原生的POI解決起來相當麻煩。

經過查閱資料翻看到阿里的一款POI封裝工具EasyExcel，上面問題等到解決；

針對2：

不能一次性查詢出全部數據，我們可以分批進行查詢，只不過時多查詢幾次的問題，況且市面上分頁插件很多。此問題好解決。

針對3：

可以將300w條數據寫到不同的Sheet中，每一個Sheet寫一百萬即可。

針對4：

不能一行一行的寫入到Excel上，我們可以將分批查詢的數據分批寫入到Excel中。

針對5：

導入到DB時我們可以將Excel中讀取的數據存儲到集合中，到了一定數量，直接批量插入到DB中。

針對6：

不能使用Mybatis的批量插入，我們可以使用JDBC的批量插入，配合事務來完成批量插入到DB。即 Excel讀取分批+JDBC分批插入+事務。

3.1 EasyExcel 簡介

附上GitHub地址：https://github.com/alibaba/easyexcel

GitHub地址上教程和說明很詳細，并且附帶有讀和寫的demo代碼，這里對它的介紹我就不再詳細說了。

至于EasyExcel底層怎么實現的這個還有待研究。

3.2 300w數據導出

EasyExcel完成300w數據的導出。技術難點已經知道了，接下來就是針對這一難點提供自己的解決思路即可。

300w數據的導出解決思路：

首先在查詢數據庫層面，需要分批進行查詢（我使用的是每次查詢20w）

每查詢一次結束，就使用EasyExcel工具將這些數據寫入一次；

當一個Sheet寫滿了100w條數據，開始將查詢的數據寫入到另一個Sheet中；

如此循環直到數據全部導出到Excel完畢。

注意：

1、我們需要計算Sheet個數，以及循環寫入次數。特別是最后一個Sheet的寫入次數

因為你不知道最后一個Sheet選喲寫入多少數據，可能是100w，也可能是25w因為我們這里的300w只是模擬數據，有可能導出的數據比300w多也可能少

2、我們需要計算寫入次數，因為我們使用的分頁查詢，所以需要注意寫入的次數。

其實查詢數據庫多少次就是寫入多少次

//導出邏輯代碼
publicvoiddataExport300w(HttpServletResponseresponse){
{
OutputStreamoutputStream=null;
try{
longstartTime=System.currentTimeMillis();
System.out.println("導出開始時間:"+startTime);

outputStream=response.getOutputStream();
ExcelWriterwriter=newExcelWriter(outputStream,ExcelTypeEnum.XLSX);
StringfileName=newString(("excel100w").getBytes(),"UTF-8");

//title
Tabletable=newTable(1);
List>titles=newArrayList>();
titles.add(Arrays.asList("onlineseqid"));
titles.add(Arrays.asList("businessid"));
titles.add(Arrays.asList("becifno"));
titles.add(Arrays.asList("ivisresult"));
titles.add(Arrays.asList("createdby"));
titles.add(Arrays.asList("createddate"));
titles.add(Arrays.asList("updateby"));
titles.add(Arrays.asList("updateddate"));
titles.add(Arrays.asList("risklevel"));
table.setHead(titles);

//模擬統計查詢的數據數量這里模擬100w
intcount=3000001;
//記錄總數:實際中需要根據查詢條件進行統計即可
IntegertotalCount=actResultLogMapper.findActResultLogByCondations(count);
//每一個Sheet存放100w條數據
IntegersheetDataRows=ExcelConstants.PER_SHEET_ROW_COUNT;
//每次寫入的數據量20w
IntegerwriteDataRows=ExcelConstants.PER_WRITE_ROW_COUNT;
//計算需要的Sheet數量
IntegersheetNum=totalCount%sheetDataRows==0?(totalCount/sheetDataRows):(totalCount/sheetDataRows+1);
//計算一般情況下每一個Sheet需要寫入的次數(一般情況不包含最后一個sheet,因為最后一個sheet不確定會寫入多少條數據)
IntegeroneSheetWriteCount=sheetDataRows/writeDataRows;
//計算最后一個sheet需要寫入的次數
IntegerlastSheetWriteCount=totalCount%sheetDataRows==0?oneSheetWriteCount:(totalCount%sheetDataRows%writeDataRows==0?(totalCount/sheetDataRows/writeDataRows):(totalCount/sheetDataRows/writeDataRows+1));

//開始分批查詢分次寫入
//注意這次的循環就需要進行嵌套循環了,外層循環是Sheet數目,內層循環是寫入次數
List>dataList=newArrayList<>();
for(inti=0;ireslultList=actResultLogMapper.findByPage100w();
if(!CollectionUtils.isEmpty(reslultList)){
reslultList.forEach(item->{
dataList.add(Arrays.asList(item.getOnlineseqid(),item.getBusinessid(),item.getBecifno(),item.getIvisresult(),item.getCreatedby(),Calendar.getInstance().getTime().toString(),item.getUpdateby(),Calendar.getInstance().getTime().toString(),item.getRisklevel()));
});
}
//寫數據
writer.write0(dataList,sheet,table);
}
}

//下載EXCEL
response.setHeader("Content-Disposition","attachment;filename="+newString((fileName).getBytes("gb2312"),"ISO-8859-1")+".xlsx");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
writer.finish();
outputStream.flush();
//導出時間結束
longendTime=System.currentTimeMillis();
System.out.println("導出結束時間:"+endTime+"ms");
System.out.println("導出所用時間:"+(endTime-startTime)/1000+"秒");
}catch(FileNotFoundExceptione){
e.printStackTrace();
}catch(IOExceptione){
e.printStackTrace();
}finally{
if(outputStream!=null){
try{
outputStream.close();
}catch(Exceptione){
e.printStackTrace();
}
}
}
}
}

3.2.1 測試機狀態

下面是測試機配置

3.2.2 使用數據庫版本

數據庫我使用的是Oracle19C在網上查閱其實在數據量不超過1億的情況下，Mysql和Oracle的性能其實相差不大,超過1億,Oracle的各方面優勢才會明顯。

所以這里可以忽略使用數據庫對時間造成的影響，使用mysql一樣可以完成測試，不需要單獨安裝Oracle。

這次測試在查詢方面我使用的是rownum進行的模擬查詢300w條數據，這種查詢效率其實并不高，實際還有很多優化空間來加快查詢速度，

如：明確查詢具體字段，不要用星號，經常查詢字段增加索引等盡可能提高查詢效率，用時可能會更短。


select*
fromACT_RESULT_LOG
whererownum3000001

--建表語句：可以參考一下
--Createtable
createtableACT_RESULT_LOG
(
onlineseqidVARCHAR2(32),
businessidVARCHAR2(32),
becifnoVARCHAR2(32),
ivisresultVARCHAR2(32),
createdbyVARCHAR2(32),
createddateDATE,
updatebyVARCHAR2(32),
updateddateDATE,
risklevelVARCHAR2(32)
)
tablespaceSTUDY_KAY
pctfree10
initrans1
maxtrans255
storage
(
initial64K
next1M
minextents1
maxextentsunlimited
);

3.2.3 測試結果

下面是300w數據從DB導出到Excel所用時間

從上面結果可以看出，300w的數據導出時間用時2分15秒，并且這是在不適用實體作為映射的情況下，如果使用實體映射不適用循環封裝的話速度會更快(當然這也是在沒有設置表頭等其他表格樣式的情況下)

綜合來說速度還算可以。

在網上查了很多資料有一個博主測試使用EasyExcel導出102w數據用時105秒。

看一下導出效果：文件還是挺大的163M

3.2.4 導出小結

經過測試EasyExcel還是挺快的，并且使用起來相當方便，作者還專門提供了關流方法，不需要我們手動去關流了，也避免了我們經常忘記關流導致的一系列問題。

導出測試就到這里，對于數據量小于300W的數據可以使用在一個Sheet中進行導出。這里就不再演示。

3.3 300w數據導入

代碼不重要首先還是思路

300W數據的導入解決思路

1、首先是分批讀取讀取Excel中的300w數據，這一點EasyExcel有自己的解決方案，我們可以參考Demo即可，只需要把它分批的參數3000調大即可。我是用的20w；（一會兒代碼一看就能明白）

2、其次就是往DB里插入，怎么去插入這20w條數據，當然不能一條一條的循環，應該批量插入這20w條數據，同樣也不能使用Mybatis的批量插入語，因為效率也低。可以參考下面鏈接【Myabtis批量插入和JDBC批量插入性能對比】

3、使用JDBC+事務的批量操作將數據插入到數據庫。（分批讀取+JDBC分批插入+手動事務控制）

3.3.1 數據庫數據(導入前)

如圖

3.3.2 核心業務代碼

//EasyExcel的讀取Excel數據的API
@Test
publicvoidimport2DBFromExcel10wTest(){
StringfileName="D:\StudyWorkspace\JavaWorkspace\java_project_workspace\idea_projects\SpringBootProjects\easyexcel\exportFile\excel300w.xlsx";
//記錄開始讀取Excel時間,也是導入程序開始時間
longstartReadTime=System.currentTimeMillis();
System.out.println("------開始讀取Excel的Sheet時間(包括導入數據過程):"+startReadTime+"ms------");
//讀取所有Sheet的數據.每次讀完一個Sheet就會調用這個方法
EasyExcel.read(fileName,newEasyExceGeneralDatalListener(actResultLogService2)).doReadAll();
longendReadTime=System.currentTimeMillis();
System.out.println("------結束讀取Excel的Sheet時間(包括導入數據過程):"+endReadTime+"ms------");
}
//事件監聽
publicclassEasyExceGeneralDatalListenerextendsAnalysisEventListener>{
/**
*處理業務邏輯的Service,也可以是Mapper
*/
privateActResultLogService2actResultLogService2;

/**
*用于存儲讀取的數據
*/
privateList>dataList=newArrayList>();

publicEasyExceGeneralDatalListener(){
}

publicEasyExceGeneralDatalListener(ActResultLogService2actResultLogService2){
this.actResultLogService2=actResultLogService2;
}

@Override
publicvoidinvoke(Mapdata,AnalysisContextcontext){
//數據add進入集合
dataList.add(data);
//size是否為100000條:這里其實就是分批.當數據等于10w的時候執行一次插入
if(dataList.size()>=ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS){
//存入數據庫:數據小于1w條使用Mybatis的批量插入即可;
saveData();
//清理集合便于GC回收
dataList.clear();
}
}

/**
*保存數據到DB
*
*@param
*@MethodName:saveData
*@return:void
*/
privatevoidsaveData(){
actResultLogService2.import2DBFromExcel10w(dataList);
dataList.clear();
}

/**
*Excel中所有數據解析完畢會調用此方法
*
*@param:context
*@MethodName:doAfterAllAnalysed
*@return:void
*/
@Override
publicvoiddoAfterAllAnalysed(AnalysisContextcontext){
saveData();
dataList.clear();
}
}
//JDBC工具類
publicclassJDBCDruidUtils{
privatestaticDataSourcedataSource;

/*
創建數據Properties集合對象加載加載配置文件
*/
static{
Propertiespro=newProperties();
//加載數據庫連接池對象
try{
//獲取數據庫連接池對象
pro.load(JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
dataSource=DruidDataSourceFactory.createDataSource(pro);
}catch(Exceptione){
e.printStackTrace();
}
}

/*
獲取連接
*/
publicstaticConnectiongetConnection()throwsSQLException{
returndataSource.getConnection();
}


/**
*關閉conn,和statement獨對象資源
*
*@paramconnection
*@paramstatement
*@MethodName:close
*@return:void
*/
publicstaticvoidclose(Connectionconnection,Statementstatement){
if(connection!=null){
try{
connection.close();
}catch(SQLExceptione){
e.printStackTrace();
}
}
if(statement!=null){
try{
statement.close();
}catch(SQLExceptione){
e.printStackTrace();
}
}
}

/**
*關閉conn,statement和resultset三個對象資源
*
*@paramconnection
*@paramstatement
*@paramresultSet
*@MethodName:close
*@return:void
*/
publicstaticvoidclose(Connectionconnection,Statementstatement,ResultSetresultSet){
close(connection,statement);
if(resultSet!=null){
try{
resultSet.close();
}catch(SQLExceptione){
e.printStackTrace();
}
}
}

/*
獲取連接池對象
*/
publicstaticDataSourcegetDataSource(){
returndataSource;
}

}
#druid.properties配置
driverClassName=oracle.jdbc.driver.OracleDriver
url=jdbcthin:@localhost:1521:ORCL
username=mrkay
password=******
initialSize=10
maxActive=50
maxWait=60000
//Service中具體業務邏輯

/**
*測試用Excel導入超過10w條數據,經過測試發現,使用Mybatis的批量插入速度非常慢,所以這里可以使用數據分批+JDBC分批插入+事務來繼續插入速度會非常快
*
*@param
*@MethodName:import2DBFromExcel10w
*@return:java.util.Map
*/
@Override
publicMapimport2DBFromExcel10w(List>dataList){
HashMapresult=newHashMap<>();
//結果集中數據為0時,結束方法.進行下一次調用
if(dataList.size()==0){
result.put("empty","0000");
returnresult;
}
//JDBC分批插入+事務操作完成對10w數據的插入
Connectionconn=null;
PreparedStatementps=null;
try{
longstartTime=System.currentTimeMillis();
System.out.println(dataList.size()+"條,開始導入到數據庫時間:"+startTime+"ms");
conn=JDBCDruidUtils.getConnection();
//控制事務:默認不提交
conn.setAutoCommit(false);
Stringsql="insertintoACT_RESULT_LOG(onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel)values";
sql+="(?,?,?,?,?,?,?,?,?)";
ps=conn.prepareStatement(sql);
//循環結果集:這里循環不支持"爛布袋"表達式
for(inti=0;iitem=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.setTimestamp(6,newTimestamp(System.currentTimeMillis()));
ps.setString(7,item.get(6));
ps.setTimestamp(8,newTimestamp(System.currentTimeMillis()));
ps.setString(9,item.get(8));
//將一組參數添加到此PreparedStatement對象的批處理命令中。
ps.addBatch();
}
//執行批處理
ps.executeBatch();
//手動提交事務
conn.commit();
longendTime=System.currentTimeMillis();
System.out.println(dataList.size()+"條,結束導入到數據庫時間:"+endTime+"ms");
System.out.println(dataList.size()+"條,導入用時:"+(endTime-startTime)+"ms");
result.put("success","1111");
}catch(Exceptione){
result.put("exception","0000");
e.printStackTrace();
}finally{
//關連接
JDBCDruidUtils.close(conn,ps);
}
returnresult;
}

3.3.3 測試結果

下面是300w數據邊讀邊寫用時間:

大致計算一下:

從開始讀取到中間分批導入再到程序結束總共用時: (1623127964725-1623127873630)/1000=91.095秒

300w數據正好是分15次插入綜合用時:8209毫秒也就是 8.209秒

計算可得300w數據讀取時間為:91.095-8.209=82.886秒

結果顯而易見:

EasyExcel分批讀取300W數據只用了 82.886秒

使用JDBC分批+事務操作插入300w條數據綜合只用時 8.209秒

------開始讀取Excel的Sheet時間(包括導入數據過程):1623127873630ms------
200000條,開始導入到數據庫時間:1623127880632ms
200000條,結束導入到數據庫時間:1623127881513ms
200000條,導入用時:881ms
200000條,開始導入到數據庫時間:1623127886945ms
200000條,結束導入到數據庫時間:1623127887429ms
200000條,導入用時:484ms
200000條,開始導入到數據庫時間:1623127892894ms
200000條,結束導入到數據庫時間:1623127893397ms
200000條,導入用時:503ms
200000條,開始導入到數據庫時間:1623127898607ms
200000條,結束導入到數據庫時間:1623127899066ms
200000條,導入用時:459ms
200000條,開始導入到數據庫時間:1623127904379ms
200000條,結束導入到數據庫時間:1623127904855ms
200000條,導入用時:476ms
200000條,開始導入到數據庫時間:1623127910495ms
200000條,結束導入到數據庫時間:1623127910939ms
200000條,導入用時:444ms
200000條,開始導入到數據庫時間:1623127916271ms
200000條,結束導入到數據庫時間:1623127916744ms
200000條,導入用時:473ms
200000條,開始導入到數據庫時間:1623127922465ms
200000條,結束導入到數據庫時間:1623127922947ms
200000條,導入用時:482ms
200000條,開始導入到數據庫時間:1623127928260ms
200000條,結束導入到數據庫時間:1623127928727ms
200000條,導入用時:467ms
200000條,開始導入到數據庫時間:1623127934374ms
200000條,結束導入到數據庫時間:1623127934891ms
200000條,導入用時:517ms
200000條,開始導入到數據庫時間:1623127940189ms
200000條,結束導入到數據庫時間:1623127940677ms
200000條,導入用時:488ms
200000條,開始導入到數據庫時間:1623127946402ms
200000條,結束導入到數據庫時間:1623127946925ms
200000條,導入用時:523ms
200000條,開始導入到數據庫時間:1623127952158ms
200000條,結束導入到數據庫時間:1623127952639ms
200000條,導入用時:481ms
200000條,開始導入到數據庫時間:1623127957880ms
200000條,結束導入到數據庫時間:1623127958925ms
200000條,導入用時:1045ms
200000條,開始導入到數據庫時間:1623127964239ms
200000條,結束導入到數據庫時間:1623127964725ms
200000條,導入用時:486ms
------結束讀取Excel的Sheet時間(包括導入數據過程):1623127964725ms------

看一下數據庫的數據是不是真的存進去了300w

可以看到數據比導入前多了300W，測試很成功