导出
铺垫也做了不少，那么现在开始讲讲我在工作中遇到的超百万数据的导入导出解决方案：
想要解决问题我们首先要明白自己遇到的问题是什么？
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分批插入+事务。
1.EasyExcel 简介
附上GitHub地址：https://github.com/alibaba/easyexcel
2. 300w数据导出
EasyExcel完成300w数据的导出。技术难点已经知道了，接下来就是针对这一难点提供自己的解决思路即可。
300w数据的导出解决思路：
1、 首先在查询数据库层面，需要分批进行查询（我使用的是每次查询20w）
2、 每查询一次结束，就使用EasyExcel工具将这些数据写入一次；
3、 当一个Sheet写满了100w条数据，开始将查询的数据写入到另一个Sheet中；
4、 如此循环直到数据全部导出到Excel完毕。
注意：
1.我们需要计算Sheet个数，以及循环写入次数。特别是最后一个Sheet的写入次数
因为你不知道最后一个Sheet选哟写入多少数据，可能是100w，也可能是25w因为我们这里的300w只是模拟数据，有可能导出的数据比300w多也可能少
2 我们需要计算写入次数，因为我们使用的分页查询，所以需要注意写入的次数。
（其实查询数据库多少次就是写入多少次）
//导出逻辑代码
public void dataExport300w(HttpServletResponse response) {
{
OutputStream outputStream = null;
try {
long startTime = System.currentTimeMillis();
System.out.println("导出开始时间:" + 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);
//模拟统计查询的数据数量这里模拟100w
int count = 3000001;
//记录总数:实际中需要根据查询条件进行统计即可
Integer totalCount = actResultLogMapper.findActResultLogByCondations(count);
//每一个Sheet存放100w条数据
Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
//每次写入的数据量20w
Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
//计算需要的Sheet数量
Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
//计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
//计算最后一个sheet需要写入的次数
Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));
//开始分批查询分次写入
//注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数
List<List<String>> dataList = new ArrayList<>();
for (int i = 0; i < sheetNum; i++) {
//创建Sheet
Sheet sheet = new Sheet(i, 0);
sheet.setSheetName("测试Sheet1" + i);
//循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
//集合复用,便于GC清理
dataList.clear();
//分页查询一次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()));
});
}
//写数据
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();
//导出时间结束
long endTime = System.currentTimeMillis();
System.out.println("导出结束时间:" + endTime + "ms");
System.out.println("导出所用时间:" + (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();
}
}
}
}
}2.3 测试结果
下面是300w数据从DB导出到Excel所用时间
从上面结果可以看出,300w的数据导出时间用时2分15秒,并且这是在不适用实体作为映射的情况下,如果使用实体映射不适用循环封装的话速度会更快(当然这也是在没有设置表头等其他表格样式的情况下)
综合来说速度还算可以.
2.4 导出小结
经过测试EasyExcel还是挺快的,并且使用起来相当方便,作者还专门提供了关流方法,不需要我们手动去关流了,也避免了我们经常忘记关流导致的一系列问题。
导出测试就到这里，对于数据量小于300W的数据可以使用在一个Sheet中进行导出。这里就不再演示。
导入
1、首先是分批读取读取Excel中的300w数据，这一点EasyExcel有自己的解决方案，我们可以参考Demo即可，只需要把它分批的参数3000调大即可。我是用的20w；（一会儿代码一看就能明白）
2、其次就是往DB里插入，怎么去插入这20w条数据，当然不能一条一条的循环，应该批量插入这20w条数据，同样也不能使用Mybatis的批量插入语，因为效率也低。
3、使用JDBC+事务的批量操作将数据插入到数据库。（分批读取+JDBC分批插入+手动事务控制）
https://www.cnblogs.com/wxw7blog/p/8706797.html
// EasyExcel的读取Excel数据的API
@Test
public void import2DBFromExcel10wTest() {
String fileName = "D:\\StudyWorkspace\\JavaWorkspace\\java_project_workspace\\idea_projects\\SpringBootProjects\\easyexcel\\exportFile\\excel300w.xlsx";
//记录开始读取Excel时间,也是导入程序开始时间
long startReadTime = System.currentTimeMillis();
System.out.println("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + "ms------");
//读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法
EasyExcel.read(fileName, new EasyExceGeneralDatalListener(actResultLogService2)).doReadAll();
long endReadTime = System.currentTimeMillis();
System.out.println("------结束读取Excel的Sheet时间(包括导入数据过程):" + endReadTime + "ms------");
}
// 事件监听
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
/**
* 处理业务逻辑的Service,也可以是Mapper
*/
private ActResultLogService2 actResultLogService2;
/**
* 用于存储读取的数据
*/
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) {
//数据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
*/
private void saveData() {
actResultLogService2.import2DBFromExcel10w(dataList);
dataList.clear();
}
/**
* Excel中所有数据解析完毕会调用此方法
*
* @param: context
* @MethodName: doAfterAllAnalysed
* @return: void
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
dataList.clear();
}
}//JDBC工具类
public class JDBCDruidUtils {
private static DataSource dataSource;
/*
创建数据Properties集合对象加载加载配置文件
*/
static {
Properties pro = new Properties();
//加载数据库连接池对象
try {
//获取数据库连接池对象
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();
}
/**
* 关闭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();
}
}
}
/**
* 关闭 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配置
driverClassName=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:ORCL
username=mrkay
password=******
initialSize=10
maxActive=50
maxWait=60000// Service中具体业务逻辑
/**
* 测试用Excel导入超过10w条数据,经过测试发现,使用Mybatis的批量插入速度非常慢,所以这里可以使用 数据分批+JDBC分批插入+事务来继续插入速度会非常快
*
* @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<>();
//结果集中数据为0时,结束方法.进行下一次调用
if (dataList.size() == 0) {
result.put("empty", "0000");
return result;
}
//JDBC分批插入+事务操作完成对10w数据的插入
Connection conn = null;
PreparedStatement ps = null;
try {
long startTime = System.currentTimeMillis();
System.out.println(dataList.size() + "条,开始导入到数据库时间:" + startTime + "ms");
conn = JDBCDruidUtils.getConnection();
//控制事务:默认不提交
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);
//循环结果集:这里循环不支持"烂布袋"表达式
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));
//将一组参数添加到此 PreparedStatement 对象的批处理命令中。
ps.addBatch();
}
//执行批处理
ps.executeBatch();
//手动提交事务
conn.commit();
long endTime = System.currentTimeMillis();
System.out.println(dataList.size() + "条,结束导入到数据库时间:" + endTime + "ms");
System.out.println(dataList.size() + "条,导入用时:" + (endTime - startTime) + "ms");
result.put("success", "1111");
} catch (Exception e) {
result.put("exception", "0000");
e.printStackTrace();
} finally {
//关连接
JDBCDruidUtils.close(conn, ps);
}
return result;
}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秒
1121
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
