面試官：百萬(wàn)數(shù)據(jù)的導(dǎo)入導(dǎo)出解決方案，怎么設(shè)計(jì)？
點(diǎn)擊關(guān)注公眾號(hào)，Java干貨及時(shí)送達(dá)??
來(lái)源：blog.csdn.net/weixin_44848900/article/details/117701981
前景 1 傳統(tǒng)POI的的版本優(yōu)缺點(diǎn)比較 2 使用方式哪種看情況 3 百萬(wàn)數(shù)據(jù)導(dǎo)入導(dǎo)出（正菜） 4 總結(jié)
前景
在項(xiàng)目開(kāi)發(fā)中往往需要使用到數(shù)據(jù)的導(dǎo)入和導(dǎo)出，導(dǎo)入就是從Excel中導(dǎo)入到DB中,而導(dǎo)出就是從DB中查詢(xún)數(shù)據(jù)然后使用POI寫(xiě)到Excel上。
寫(xiě)本文的背景是因?yàn)樵诠ぷ髦杏龅搅舜髷?shù)據(jù)的導(dǎo)入和導(dǎo)出，問(wèn)題既然來(lái)了逃跑不如干掉它！！！
只要這一次解決了，后期遇到同樣的問(wèn)題就好解決了。
廢話(huà)不多說(shuō)，開(kāi)始擼起來(lá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)題。
既然要做導(dǎo)入導(dǎo)出，那么我們就先來(lái)大致看一下傳統(tǒng)poi技術(shù)的版本以及優(yōu)缺點(diǎn)對(duì)比吧！
首先我們知道POI中我們最熟悉的莫過(guò)于WorkBook這樣一個(gè)接口，我們的POI版本也在更新的同時(shí)對(duì)這個(gè)幾口的實(shí)現(xiàn)類(lèi)做了更新：
HSSFWorkbook :
這個(gè)實(shí)現(xiàn)類(lèi)是我們?cè)缙谑褂米疃嗟膶?duì)象，它可以操作Excel2003以前（包含2003）的所有Excel版本。在2003以前Excel的版本后綴還是.xls
XSSFWorkbook :
這個(gè)實(shí)現(xiàn)類(lèi)現(xiàn)在在很多公司都可以發(fā)現(xiàn)還在使用，它是操作的Excel2003--Excel2007之間的版本，Excel的擴(kuò)展名是.xlsx
SXSSFWorkbook :
這個(gè)實(shí)現(xiàn)類(lèi)是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,擴(kuò)展名是.xlsx
大致知道了我們?cè)趯?dǎo)入導(dǎo)出操作的時(shí)候會(huì)用到這樣三個(gè)實(shí)現(xiàn)類(lèi)以及他們可以操作的Excel版本和后綴之后,我們就要從優(yōu)缺點(diǎn)分析他們了
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所以?xún)?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é)了！！！
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(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)行分批查詢(xún)，分批寫(xiě)入Excel的方式來(lái)做；
3 百萬(wàn)數(shù)據(jù)導(dǎo)入導(dǎo)出（正菜）
鋪墊也做了不少，那么現(xiàn)在開(kāi)始講講我在工作中遇到的超百萬(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：
不能一次性查詢(xún)出全部數(shù)據(jù)，我們可以分批進(jìn)行查詢(xún)，只不過(guò)時(shí)多查詢(xún)幾次的問(wèn)題，況且市面上分頁(yè)插件很多。此問(wèn)題好解決。
針對(duì)3：
可以將300w條數(shù)據(jù)寫(xiě)到不同的Sheet中，每一個(gè)Sheet寫(xiě)一百萬(wàn)即可。
針對(duì)4：
不能一行一行的寫(xiě)入到Excel上，我們可以將分批查詢(xún)的數(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 EasyExcel 簡(jiǎn)介
附上GitHub地址：https://github.com/alibaba/easyexcel
GitHub地址上教程和說(shuō)明很詳細(xì)，并且附帶有讀和寫(xiě)的demo代碼，這里對(duì)它的介紹我就不再詳細(xì)說(shuō)了。
至于EasyExcel底層怎么實(shí)現(xiàn)的這個(gè)還有待研究。
3.2 300w數(shù)據(jù)導(dǎo)出
EasyExcel完成300w數(shù)據(jù)的導(dǎo)出。技術(shù)難點(diǎn)已經(jīng)知道了，接下來(lái)就是針對(duì)這一難點(diǎn)提供自己的解決思路即可。
300w數(shù)據(jù)的導(dǎo)出解決思路：
首先在查詢(xún)數(shù)據(jù)庫(kù)層面，需要分批進(jìn)行查詢(xún)（我使用的是每次查詢(xún)20w） 每查詢(xún)一次結(jié)束，就使用EasyExcel工具將這些數(shù)據(jù)寫(xiě)入一次； 當(dāng)一個(gè)Sheet寫(xiě)滿(mǎn)了100w條數(shù)據(jù)，開(kāi)始將查詢(xún)的數(shù)據(jù)寫(xiě)入到另一個(gè)Sheet中； 如此循環(huán)直到數(shù)據(jù)全部導(dǎo)出到Excel完畢。
注意：
1、我們需要計(jì)算Sheet個(gè)數(shù)，以及循環(huán)寫(xiě)入次數(shù)。特別是最后一個(gè)Sheet的寫(xiě)入次數(shù)
因?yàn)槟悴恢雷詈笠粋€(gè)Sheet選喲寫(xiě)入多少數(shù)據(jù)，可能是100w，也可能是25w因?yàn)槲覀冞@里的300w只是模擬數(shù)據(jù)，有可能導(dǎo)出的數(shù)據(jù)比300w多也可能少
2、我們需要計(jì)算寫(xiě)入次數(shù)，因?yàn)槲覀兪褂玫姆猪?yè)查詢(xún)，所以需要注意寫(xiě)入的次數(shù)。
其實(shí)查詢(xún)數(shù)據(jù)庫(kù)多少次就是寫(xiě)入多少次
//導(dǎo)出邏輯代碼
public void dataExport300w(HttpServletResponse response) {
{
OutputStream outputStream = null;
try {
long startTime = System.currentTimeMillis();
System.out.println("導(dǎo)出開(kāi)始時(shí)間:" + startTime);
outputStream = response.getOutputStream();
ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
String fileName = new String(("excel100w").getBytes(), "UTF-8");
//title
Table table = new Table(1);
List<List<String>> titles = new ArrayList<List<String>>();
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);
//模擬統(tǒng)計(jì)查詢(xún)的數(shù)據(jù)數(shù)量這里模擬100w
int count = 3000001;
//記錄總數(shù):實(shí)際中需要根據(jù)查詢(xún)條件進(jìn)行統(tǒng)計(jì)即可
Integer totalCount = actResultLogMapper.findActResultLogByCondations(count);
//每一個(gè)Sheet存放100w條數(shù)據(jù)
Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
//每次寫(xiě)入的數(shù)據(jù)量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));
//開(kāi)始分批查詢(xún)分次寫(xiě)入
//注意這次的循環(huán)就需要進(jìn)行嵌套循環(huán)了,外層循環(huán)是Sheet數(shù)目,內(nèi)層循環(huán)是寫(xiě)入次數(shù)
List<List<String>> dataList = new ArrayList<>();
for (int i = 0; i < sheetNum; i++) {
//創(chuàng)建Sheet
Sheet sheet = new Sheet(i, 0);
sheet.setSheetName("測(cè)試Sheet1" + 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++) {
//集合復(fù)用,便于GC清理
dataList.clear();
//分頁(yè)查詢(xún)一次20w
PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
List<ActResultLog> reslultList = 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()));
});
}
//寫(xiě)數(shù)據(jù)
writer.write0(dataList, sheet, table);
}
}
// 下載EXCEL
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
writer.finish();
outputStream.flush();
//導(dǎo)出時(shí)間結(jié)束
long endTime = System.currentTimeMillis();
System.out.println("導(dǎo)出結(jié)束時(shí)間:" + endTime + "ms");
System.out.println("導(dǎo)出所用時(shí)間:" + (endTime - startTime) / 1000 + "秒");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
3.2.1 測(cè)試機(jī)狀態(tài)
下面是測(cè)試機(jī)配置
3.2.2 使用數(shù)據(jù)庫(kù)版本
數(shù)據(jù)庫(kù)我使用的是Oracle19C在網(wǎng)上查閱其實(shí)在數(shù)據(jù)量不超過(guò)1億的情況下，Mysql和Oracle的性能其實(shí)相差不大,超過(guò)1億,Oracle的各方面優(yōu)勢(shì)才會(huì)明顯。
所以這里可以忽略使用數(shù)據(jù)庫(kù)對(duì)時(shí)間造成的影響，使用mysql一樣可以完成測(cè)試，不需要單獨(dú)安裝Oracle。
這次測(cè)試在查詢(xún)方面我使用的是rownum進(jìn)行的模擬查詢(xún)300w條數(shù)據(jù)，這種查詢(xún)效率其實(shí)并不高，實(shí)際還有很多優(yōu)化空間來(lái)加快查詢(xún)速度，
如：明確查詢(xún)具體字段，不要用星號(hào)，經(jīng)常查詢(xún)字段增加索引等盡可能提高查詢(xún)效率，用時(shí)可能會(huì)更短。
<select id="findByPage300w" resultType="show.mrkay.pojo.ActResultLog">
select *
from ACT_RESULT_LOG
where rownum <![CDATA[<]]> 3000001
</select>
-- 建表語(yǔ)句：可以參考一下
-- Create table
create table ACT_RESULT_LOG
(
onlineseqid VARCHAR2(32),
businessid VARCHAR2(32),
becifno VARCHAR2(32),
ivisresult VARCHAR2(32),
createdby VARCHAR2(32),
createddate DATE,
updateby VARCHAR2(32),
updateddate DATE,
risklevel VARCHAR2(32)
)
tablespace STUDY_KAY
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
3.2.3 測(cè)試結(jié)果
下面是300w數(shù)據(jù)從DB導(dǎo)出到Excel所用時(shí)間
從上面結(jié)果可以看出，300w的數(shù)據(jù)導(dǎo)出時(shí)間用時(shí)2分15秒，并且這是在不適用實(shí)體作為映射的情況下，如果使用實(shí)體映射不適用循環(huán)封裝的話(huà)速度會(huì)更快(當(dāng)然這也是在沒(méi)有設(shè)置表頭等其他表格樣式的情況下)
綜合來(lái)說(shuō)速度還算可以。
在網(wǎng)上查了很多資料有一個(gè)博主測(cè)試使用EasyExcel導(dǎo)出102w數(shù)據(jù)用時(shí)105秒，具體可以看一下鏈接：
https://blog.csdn.net/u014299266/article/details/107790561
看一下導(dǎo)出效果：文件還是挺大的163M
3.2.4 導(dǎo)出小結(jié)
經(jīng)過(guò)測(cè)試EasyExcel還是挺快的，并且使用起來(lái)相當(dāng)方便，作者還專(zhuān)門(mén)提供了關(guān)流方法，不需要我們手動(dòng)去關(guān)流了，也避免了我們經(jīng)常忘記關(guān)流導(dǎo)致的一系列問(wèn)題。
導(dǎo)出測(cè)試就到這里，對(duì)于數(shù)據(jù)量小于300W的數(shù)據(jù)可以使用在一個(gè)Sheet中進(jìn)行導(dǎo)出。這里就不再演示。
3.3 300w數(shù)據(jù)導(dǎo)入
代碼不重要首先還是思路
300W數(shù)據(jù)的導(dǎo)入解決思路
1、首先是分批讀取讀取Excel中的300w數(shù)據(jù)，這一點(diǎn)EasyExcel有自己的解決方案，我們可以參考Demo即可，只需要把它分批的參數(shù)3000調(diào)大即可。我是用的20w；（一會(huì)兒代碼一看就能明白）
2、其次就是往DB里插入，怎么去插入這20w條數(shù)據(jù)，當(dāng)然不能一條一條的循環(huán)，應(yīng)該批量插入這20w條數(shù)據(jù)，同樣也不能使用Mybatis的批量插入語(yǔ)，因?yàn)樾室驳汀？梢詤⒖枷旅骀溄印綧yabtis批量插入和JDBC批量插入性能對(duì)比】
3、使用JDBC+事務(wù)的批量操作將數(shù)據(jù)插入到數(shù)據(jù)庫(kù)。（分批讀取+JDBC分批插入+手動(dòng)事務(wù)控制）
https://www.cnblogs.com/wxw7blog/p/8706797.html
3.3.1 數(shù)據(jù)庫(kù)數(shù)據(jù)(導(dǎo)入前)
如圖
3.3.2 核心業(yè)務(wù)代碼
// EasyExcel的讀取Excel數(shù)據(jù)的API
@Test
public void import2DBFromExcel10wTest() {
String fileName = "D:\\StudyWorkspace\\JavaWorkspace\\java_project_workspace\\idea_projects\\SpringBootProjects\\easyexcel\\exportFile\\excel300w.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(actResultLogService2)).doReadAll();
long endReadTime = System.currentTimeMillis();
System.out.println("------結(jié)束讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):" + endReadTime + "ms------");
}
// 事件監(jiān)聽(tīng)
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
/**
* 處理業(yè)務(wù)邏輯的Service,也可以是Mapper
*/
private ActResultLogService2 actResultLogService2;
/**
* 用于存儲(chǔ)讀取的數(shù)據(jù)
*/
private List<Map<Integer, String>> dataList = new ArrayList<Map<Integer, String>>();
public EasyExceGeneralDatalListener() {
}
public EasyExceGeneralDatalListener(ActResultLogService2 actResultLogService2) {
this.actResultLogService2 = actResultLogService2;
}
@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() {
actResultLogService2.import2DBFromExcel10w(dataList);
dataList.clear();
}
/**
* Excel中所有數(shù)據(jù)解析完畢會(huì)調(diào)用此方法
*
* @param: context
* @MethodName: doAfterAllAnalysed
* @return: void
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
dataList.clear();
}
}
//JDBC工具類(lèi)
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配置
driverClassName=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:ORCL
username=mrkay
password=******
initialSize=10
maxActive=50
maxWait=60000
// Service中具體業(yè)務(wù)邏輯
/**
* 測(cè)試用Excel導(dǎo)入超過(guò)10w條數(shù)據(jù),經(jīng)過(guò)測(cè)試發(fā)現(xiàn),使用Mybatis的批量插入速度非常慢,所以這里可以使用 數(shù)據(jù)分批+JDBC分批插入+事務(wù)來(lái)繼續(xù)插入速度會(huì)非常快
*
* @param
* @MethodName: import2DBFromExcel10w
* @return: java.util.Map<java.lang.String, java.lang.Object>
*/
@Override
public Map<String, Object> import2DBFromExcel10w(List<Map<Integer, String>> dataList) {
HashMap<String, Object> result = new HashMap<>();
//結(jié)果集中數(shù)據(jù)為0時(shí),結(jié)束方法.進(jìn)行下一次調(diào)用
if (dataList.size() == 0) {
result.put("empty", "0000");
return result;
}
//JDBC分批插入+事務(wù)操作完成對(duì)10w數(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 ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel) values";
sql += "(?,?,?,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
//循環(huán)結(jié)果集:這里循環(huán)不支持"爛布袋"表達(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.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
ps.setString(7, item.get(6));
ps.setTimestamp(8, new Timestamp(System.currentTimeMillis()));
ps.setString(9, item.get(8));
//將一組參數(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");
result.put("success", "1111");
} catch (Exception e) {
result.put("exception", "0000");
e.printStackTrace();
} finally {
//關(guān)連接
JDBCDruidUtils.close(conn, ps);
}
return result;
}
3.3.3 測(cè)試結(jié)果
下面是300w數(shù)據(jù)邊讀邊寫(xiě)用時(shí)間:
大致計(jì)算一下:
從開(kāi)始讀取到中間分批導(dǎo)入再到程序結(jié)束總共用時(shí): (1623127964725-1623127873630)/1000=91.095秒
300w數(shù)據(jù)正好是分15次插入綜合用時(shí):8209毫秒 也就是 8.209秒
計(jì)算可得300w數(shù)據(jù)讀取時(shí)間為:91.095-8.209=82.886秒
結(jié)果顯而易見(jiàn):
EasyExcel分批讀取300W數(shù)據(jù)只用了 82.886秒
使用JDBC分批+事務(wù)操作插入300w條數(shù)據(jù)綜合只用時(shí) 8.209秒
------開(kāi)始讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):1623127873630ms------
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127880632ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127881513ms
200000條,導(dǎo)入用時(shí):881ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127886945ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127887429ms
200000條,導(dǎo)入用時(shí):484ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127892894ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127893397ms
200000條,導(dǎo)入用時(shí):503ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127898607ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127899066ms
200000條,導(dǎo)入用時(shí):459ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127904379ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127904855ms
200000條,導(dǎo)入用時(shí):476ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127910495ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127910939ms
200000條,導(dǎo)入用時(shí):444ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127916271ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127916744ms
200000條,導(dǎo)入用時(shí):473ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127922465ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127922947ms
200000條,導(dǎo)入用時(shí):482ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127928260ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127928727ms
200000條,導(dǎo)入用時(shí):467ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127934374ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127934891ms
200000條,導(dǎo)入用時(shí):517ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127940189ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127940677ms
200000條,導(dǎo)入用時(shí):488ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127946402ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127946925ms
200000條,導(dǎo)入用時(shí):523ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127952158ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127952639ms
200000條,導(dǎo)入用時(shí):481ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127957880ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127958925ms
200000條,導(dǎo)入用時(shí):1045ms
200000條,開(kāi)始導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127964239ms
200000條,結(jié)束導(dǎo)入到數(shù)據(jù)庫(kù)時(shí)間:1623127964725ms
200000條,導(dǎo)入用時(shí):486ms
------結(jié)束讀取Excel的Sheet時(shí)間(包括導(dǎo)入數(shù)據(jù)過(guò)程):1623127964725ms------
看一下數(shù)據(jù)庫(kù)的數(shù)據(jù)是不是真的存進(jìn)去了300w
可以看到數(shù)據(jù)比導(dǎo)入前多了300W，測(cè)試很成功
3.3.4 導(dǎo)入小結(jié)
具體我沒(méi)有看網(wǎng)上其他人的測(cè)試情況，這東西一般也很少有人愿意測(cè)試，不過(guò)這個(gè)速度對(duì)于我當(dāng)時(shí)解決公司大數(shù)據(jù)的導(dǎo)入和導(dǎo)出已經(jīng)足夠，當(dāng)然公司的業(yè)務(wù)邏輯很復(fù)雜，數(shù)據(jù)量也比較多，表的字段也比較多，導(dǎo)入和導(dǎo)出的速度會(huì)比現(xiàn)在測(cè)試的要慢一點(diǎn)，但是也在人類(lèi)能接受的范圍之內(nèi)。
4 總結(jié)
這次工作中遇到的問(wèn)題也給我留下了深刻印象,同時(shí)也是我職業(yè)生涯添彩的一筆。
最起碼簡(jiǎn)歷上可以寫(xiě)上你處理過(guò)上百萬(wàn)條數(shù)據(jù)的導(dǎo)入導(dǎo)出。
最后說(shuō)一下公司之前怎么做的，公司之前做法是
限制了用戶(hù)的下載數(shù)量每次最多只能有四個(gè)人同時(shí)下載，并且控制每個(gè)用戶(hù)最大的導(dǎo)出數(shù)據(jù)最多只能是20w，與此同時(shí)他們也是使用的JDBC分批導(dǎo)入，但是并沒(méi)有手動(dòng)控制事務(wù)。
控制同時(shí)下載人數(shù)我可以理解，但是控制下載數(shù)據(jù)最多為20w就顯得有點(diǎn)雞肋了。
這也是我后期要解決的問(wèn)題。
好了到此結(jié)束，相信大神有比我做的更好的，對(duì)于EasyExcel內(nèi)部到底是怎么實(shí)現(xiàn)的還有待考究（有空我再研究研究）。
（完）
碼農(nóng)突圍資料鏈接
1、臥槽！字節(jié)跳動(dòng)《算法中文手冊(cè)》火了，完整版 PDF 開(kāi)放下載！
2、計(jì)算機(jī)基礎(chǔ)知識(shí)總結(jié)與操作系統(tǒng) PDF 下載
3、艾瑪，終于來(lái)了！《LeetCode Java版題解》.PDF
4、Github 10K+，《LeetCode刷題C/C++版答案》出爐.PDF歡迎添加魚(yú)哥個(gè)人微信：smartfish2020，進(jìn)粉絲群或圍觀朋友圈。