2017电商网站建设背景,怎么制作平台,四川建设厅网站首页,wordpress主题改错1.1 模拟500w数据导出 需求#xff1a;使用EasyExcel完成500w数据的导出。
500w数据的导出解决思路#xff1a;
首先在查询数据库层面#xff0c;需要分批进行查询#xff08;比如每次查询20w#xff09; 每查询一次结束#xff0c;就使用EasyExcel工具将这些数据写入一…1.1 模拟500w数据导出 需求使用EasyExcel完成500w数据的导出。
500w数据的导出解决思路
首先在查询数据库层面需要分批进行查询比如每次查询20w 每查询一次结束就使用EasyExcel工具将这些数据写入一次 当一个Sheet写满了100w条数据开始将查询的数据写入到另一个Sheet中 如此循环直到数据全部导出到Excel完毕。 我们需要计算Sheet个数以及循环写入次数。特别是最后一个Sheet的写入次数
其实查询数据库多少次就是写入多少次
准备工作 1.基于maven搭建springboot工程引入easyexcel依赖这里我用的是3.0版本
dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.0.5/version
/dependency2.创建海量数据的sql脚本CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT ,
loc VARCHAR(13) NOT NULL DEFAULT
) ;#创建表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
);#测试数据
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 $$#创建一个函数名字 rand_string可以随机返回我指定的个数字符串
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#定义了一个变量 chars_str 类型 varchar(100)
#默认给 chars_str 初始值 abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZdeclare chars_str varchar(100) defaultabcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ; declare return_str varchar(255) default ;declare i int default 0; while i n do# concat 函数 : 连接函数mysql函数set return_str concat(return_str,substring(chars_str,floor(1rand()*52),1));set i i 1;end while;return return_str;end $$#这里我们又自定了一个函数,返回一个随机的部门号
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i floor(10rand()*500);
return i;
end $$#创建一个存储过程 可以添加雇员
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit 0 把autocommit设置成0#autocommit 0 含义: 不要自动提交set autocommit 0; #默认不提交sql语句repeatset i i 1;#通过前面写的函数随机产生字符串和部门编号然后加入到emp表insert into emp values ((starti) ,rand_string(6),SALESMAN,0001,curdate(),2000,400,rand_num());until i max_numend repeat;#commit整体提交所有sql语句提高效率commit;end $$#添加8000000数据
call insert_emp(100001,8000000)$$#命令结束符再重新设置为;
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;}导出核心代码
Resource
private EmpService empService;
/*** 分批次导出*/
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数据public static final Integer PER_SHEET_ROW_COUNT 1000000;//每次查询20w数据每次写入20w数据public static final Integer PER_WRITE_ROW_COUNT 200000;
}Override
public void export() throws IOException {OutputStream outputStream null;try {//记录总数:实际中需要根据查询条件进行统计即可//LambdaQueryWrapperEmp lambdaQueryWrapper new QueryWrapperEmp().lambda().eq(Emp::getEmpno, 1000001);Integer totalCount empMapper.selectCount(null);//每一个Sheet存放100w条数据Integer sheetDataRows ExcelConstants.PER_SHEET_ROW_COUNT;//每次写入的数据量20w,每页查询20WInteger 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));ServletRequestAttributes requestAttributes (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();HttpServletResponse response requestAttributes.getResponse();outputStream response.getOutputStream();//必须放到循环外否则会刷新流ExcelWriter excelWriter EasyExcel.write(outputStream).build();//开始分批查询分次写入for (int i 0; i sheetNum; i) {//创建SheetWriteSheet sheet new WriteSheet();sheet.setSheetName(测试Sheet1i);sheet.setSheetNo(i);//循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCountfor (int j 0; j (i ! sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j) {//分页查询一次20wPageEmp page empMapper.selectPage(new Page(j 1 oneSheetWriteCount * i, writeDataRows), null);ListEmp empList page.getRecords();ListEmpVo 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();//写数据excelWriter.write(empVoList, writeSheet);}}// 下载EXCELresponse.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);// 这里URLEncoder.encode可以防止浏览器端导出excel文件名中文乱码 当然和easyexcel没有关系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();}}
}导出500w数据共计耗时可以看到差不多400s左右。 看下导出效果脚本插入了500w数据100w一个sheet因此正好五个 1.2模拟500w数据导入 500W数据的导入解决思路
1、首先是分批读取Excel中的500w数据这一点EasyExcel有自己的解决方案我们可以参考Demo即可只需要把它分批的参数5000调大即可。
2、其次就是往DB里插入怎么去插入这20w条数据当然不能一条一条的循环应该批量插入这20w条数据同样也不能使用Mybatis的批量插入因为效率也低。
3、使用JDBC事务的批量操作将数据插入到数据库。分批读取JDBC分批插入手动事务控制
代码实现
controller层测试接口
// 事件监听
public class EasyExceGeneralDatalListener extends AnalysisEventListenerMapInteger, String {/*** 处理业务逻辑的Service,也可以是Mapper*/private EmpService empService;/*** 用于存储读取的数据*/private ListMapInteger, String dataList new ArrayListMapInteger, String();public EasyExceGeneralDatalListener() {}public EasyExceGeneralDatalListener(EmpService empService) {this.empService empService;}Overridepublic void invoke(MapInteger, 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() {empService.importData(dataList);dataList.clear();}/*** Excel中所有数据解析完毕会调用此方法** param: context* MethodName: doAfterAllAnalysed* return: void*/Overridepublic void doAfterAllAnalysed(AnalysisContext context) {saveData();dataList.clear();}
}核心业务代码
public interface EmpService {void export() throws IOException;void importData(ListMapInteger, String dataList);}/** 测试用Excel导入超过10w条数据,经过测试发现,使用Mybatis的批量插入速度非常慢,所以这里可以使用 数据分批JDBC分批插入事务来继续插入速度会非常快*/Overridepublic void importData(ListMapInteger, String dataList) {//结果集中数据为0时,结束方法.进行下一次调用if (dataList.size() 0) {return;}//JDBC分批插入事务操作完成对20w数据的插入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 emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values;sql (?,?,?,?,?,?,?,?);ps conn.prepareStatement(sql);//循环结果集:这里循环不支持lambda表达式for (int i 0; i dataList.size(); i) {MapInteger, 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));//将一组参数添加到此 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);} catch (Exception e) {e.printStackTrace();} finally {//关连接JDBCDruidUtils.close(conn, ps);}}}jdbc工具类
//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配置文件
这里我将文件创建在类路径下需要注意的是连接mysql数据库时需要指定rewriteBatchedStatementstrue批处理才会生效否则还是逐条插入效率较低allowMultiQueriestrue表示可以使sql语句中有多个insert或者update语句语句之间携带分号这里可以忽略。
# druid.properties配置
driverClassNamecom.mysql.jdbc.Driver
urljdbc:mysql://localhost:3306/llp?autoReconnecttrueuseUnicodetrueuseSSLfalseserverTimezoneGMT%2B8allowMultiQueriestruerewriteBatchedStatementstrue
usernameroot
passwordroot
initialSize10
maxActive50
maxWait60000测试结果
------开始读取Excel的Sheet时间(包括导入数据过程):1674181403555ms------
200000条,开始导入到数据库时间:1674181409740ms
2023-01-20 10:23:29.943 INFO 18580 --- [nio-8888-exec-1] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
200000条,结束导入到数据库时间:1674181413252ms
200000条,导入用时:3512ms
200000条,开始导入到数据库时间:1674181418422ms
200000条,结束导入到数据库时间:1674181420999ms
200000条,导入用时:2577ms
.....
200000条,开始导入到数据库时间:1674181607405ms
200000条,结束导入到数据库时间:1674181610154ms
200000条,导入用时:2749ms
------结束读取Excel的Sheet时间(包括导入数据过程):1674181610155ms------
------读取Excel的Sheet时间(包括导入数据)共计耗时:206600ms------总结
1.如此大批量数据的导出和导入操作会占用大量的内存实际开发中还应限制操作人数。
2.在做大批量的数据导入时可以使用jdbc手动开启事务批量提交。
