一切福田，不離方寸，從心而覓，感無不通。

通过Mybatis的流式查询ResultHandler解决超大Excel导出内存溢出问题

在项目开发过程导出Excel为常用功能，之前的一篇Java导出超大Excel文件，防止内存溢出已经解决了Excel写入层面时的内存问题，但数据库查询层面，仍存在由于默认的mybatis查询是将所有数据都查询到本地内存，因此仍有可能会导致内存溢出，因此本文再详细介绍记录通过mybatis的ResultHander进行流式查询读取来完全解决excel的大量数据导出内存溢出问题。

一.先批量插入测试数据

1.建表，包含2个字段username,age

1
2
3
4
5
6
7
CREATE TABLE `t_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_code_key`(`username`, `age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

 

2.UserMapper.xml

1
2
3
4
5
6
7
<insert id="batchInsert">
insert into t_user(username,age)
values
<foreach collection="list" item="item" separator=",">
(#{item.username},#{item.age})
</foreach>
</insert>

 

3.UserMapper.java

1
int batchInsert(List<User> list);

 

4.UserService.java & UserServiceImpl.java

1
int batchInsert(List<User> list);

 

在这里插入图片描述

5.UserController.java中编写测试代码,写入100万行数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
//1.插入1000101行测试数据
//http://localhost:8080/test/user/batchInsert
@RequestMapping(value="/batchInsert", method = RequestMethod.GET)
public String batchInsert(){
 
logger.info("method starting...");
long startTime = System.currentTimeMillis();
//每次批量插入2000条记录，提高插入效率
int batchSize = 2000;
 
List<User> list = new LinkedList<User>();
for(int i=0;i<1000101;i++){
User user = new User();
user.setUsername("name"+ i);
user.setAge(18);
list.add(user);
if(list.size()>0 && list.size() % batchSize == 0) {
userService.batchInsert(list);
logger.info("has batchInsert size: {}", i);
list.clear();//清除list
}
}
long endTime = System.currentTimeMillis();
 
logger.info("method finished,total spend time: {} ms.",(endTime-startTime));
 
return "batchInsert";
}

 

二、ResultHandler流式查询导出

ResultHandler接口可以用于进行流式查询(即一行一行从数据库中读取处理，因此不会占用本地内存),本文的核心就是通过调用mapper的方法，传入一个ResultHandler，然后在实现的方法中读取数据，然后一行一行处理。

1.在UserMapper.xml中配置

其中的resultSetType为FORWARD_ONLY，fetchSize为-2147483648

1
2
3
4
5
6
<sql id="listSql">
   select id,username,age from t_user
</sql>
<select id="export" parameterType="user" resultType="user" resultSetType="FORWARD_ONLY" fetchSize="-2147483648">
<include refid="listSql" />
</select>

 

2.UserMapper.java中编写方法

1
2
/**导出，mapper的方法需要是void返回，并且参数中含ResultHandler(流式查询遍历的条件)，这里我没加参数，可以加上你的条件参数*/
public void export(ResultHandler<User> resultHandler);

 

重要注意点：要满足流式查询，需要方法返回值为void,并且方法中有ResultHandler类型的参数。在mybatis源码中的MapperMethod.java中也能看到对应的代码判断如下： 在这里插入图片描述

3.自定义一个ExcelResultHandler,提供给所有导出的代码共用

此ResultHandler实现了excel的导出，并遍历mapper查询数据，一行一行写入excel，节省内存，可以在导出的业务代码进行使用，具有通用性，只需要new出对象然后调用相应的方法。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
package cn.gzsendi.modules.framework.utils;
 
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
 
import javax.servlet.http.HttpServletResponse;
 
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
 
import cn.gzsendi.modules.framework.reflect.Reflector;
import cn.gzsendi.modules.framework.reflect.reflectasm.MethodAccessor;
 
public abstract class ExcelResultHandler<T> implements ResultHandler<T>{
private final Logger logger = LoggerFactory.getLogger(this.getClass());
 
private AtomicInteger currentRowNumber = new AtomicInteger(0);//记录当前excel行号，从0开始
private Sheet sheet = null;
 
private List<String> headerArray ; //excel表头
private List<String> fieldArray ; //对应的字段
//定义totalCellNumber变量，
private int totalCellNumber;
//定义导出成zip格式的还是原始的xlsx格式
private boolean isExportZip = true;
 
private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
 
//定义要导出的excel文件名,不带xlsx后缀,默认为uuID,也可以通过构造函数传进来进行改变。
private String exportFileName = UUID.randomUUID().toString().replace("-", "");
public ExcelResultHandler(List<String> headerArray,List<String> fieldArray){
 
this.headerArray = headerArray;
this.fieldArray = fieldArray;
this.totalCellNumber = headerArray.size();
}
public ExcelResultHandler(List<String> headerArray,List<String> fieldArray,boolean isExportZip){
this(headerArray,fieldArray);
this.isExportZip = isExportZip;
 
}
public ExcelResultHandler(List<String> headerArray,List<String> fieldArray,String exportFileName){
this(headerArray,fieldArray);
this.exportFileName = exportFileName;
 
}
public ExcelResultHandler(List<String> headerArray,List<String> fieldArray,String exportFileName,boolean isExportZip){
this(headerArray,fieldArray,exportFileName);
this.isExportZip = isExportZip;
 
}
//出象方法，提供给子类进行实现，遍历写入数据到excel
public abstract void tryFetchDataAndWriteToExcel();
 
public void handleResult(ResultContext<? extends T> resultContext) {
//获取数据，并回调ExportExcelUtils中的方法进行数据写入到excel，固定写法即可，不需要修改
Object aRowData = resultContext.getResultObject();
callBackWriteRowdataToExcel(aRowData);
}
/**导出*/
public void startExportExcel() {
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
ZipOutputStream zos = null;
OutputStream os = null;
try {
logger.info("--------->>>>写入Excel开始.." );
//写入文件
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((exportFileName+".zip").replaceAll(" ", "").getBytes("utf-8"),"iso8859-1"));
os = new BufferedOutputStream(response.getOutputStream());
//如果设置成了导出成Zip，格式加上三行以下代码进行Zip的处理
if(isExportZip){
zos = new ZipOutputStream(os);
ZipEntry zipEntry = new ZipEntry(new String((exportFileName+".xlsx").replaceAll(" ", "")));
zos.putNextEntry(zipEntry);
}
SXSSFWorkbook wb = new SXSSFWorkbook();//默认100行，超100行将写入临时文件
wb.setCompressTempFiles(false); //是否压缩临时文件，否则写入速度更快，但更占磁盘，但程序最后是会将临时文件删掉的
            sheet = wb.createSheet("Sheet 1");
        //写入表头，Rows从0开始.
        Row row = sheet.createRow(0);
        for (int cellNumber = 0; cellNumber < totalCellNumber; cellNumber++) {
        
         Cell cell = row.createCell(cellNumber);
                cell.setCellValue(headerArray.get(cellNumber)); //写入表头数据
        
        }
        
        //写入数据
    /****************************/
        //调用具体的实现子类的代码，尝试获取数据进行遍历并写入excel
        tryFetchDataAndWriteToExcel();
 
        //最后打印一下最终写入的行数
logger.info("--------->>>> write to excel size now is {}", currentRowNumber.get() );
        
        //Write excel to a file
if(isExportZip){
wb.write(zos);
}else{
wb.write(os);
}
        
        
        if (wb != null) {
             wb.dispose();// 删除临时文件，很重要，否则磁盘可能会被写满
            }
        
        wb.close();
        
    /****************************/
  
   logger.info("--------->>>>全部数据写入Excel完成.." );
} catch (Exception e) {
logger.error("error",e);
} finally {
//关闭资源
if(isExportZip){
try {if(zos!=null) zos.close();} catch (IOException e1) {logger.error("error",e1); }
}else{
try {if(os!=null) os.close();} catch (IOException e1) {logger.error("error",e1); }
}
       
}
 
}
//写入一行数据到excel中,提供给ResultHandler中遍历时进行回调调用
@SuppressWarnings("rawtypes")
public void callBackWriteRowdataToExcel(Object aRowData) {
 
//反射获取值并设置到excel的中cell列中
MethodAccessor methodAccessor = Reflector.getMethodAccessor(aRowData.getClass());
 
//先将行号增加
currentRowNumber.incrementAndGet();
//创建excel中新的一行
Row row = sheet.createRow(currentRowNumber.get());
for (int cellNumber = 0; cellNumber < totalCellNumber; cellNumber++) {
 
//aRowData为map时，要特殊处理进行获取。不能通过methodAccessor反射调用.
Object value = null;
if(aRowData instanceof Map){
value = ((Map)aRowData).get(fieldArray.get(cellNumber));
}else {
value = methodAccessor.getFieldValue(aRowData, fieldArray.get(cellNumber));
}
Cell cell = row.createCell(cellNumber);
 
//date类型默认转换string格式化日期
            if (value!=null && value instanceof Date){
             cell.setCellValue(sdf.format(value));//
            }else {
             cell.setCellValue(value==null?"":value.toString());//写入数据
            }
 
}
 
//每写入5000条就打印一下
if(currentRowNumber.get() % 5000 == 0 ){
logger.info("--------->>>> write to excel size now is {}", currentRowNumber.get() );
}
}
 
}

 

4.在UserServiceImpl实现类中进行ExcelResultHandler的调用并写入excel，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/**导出*/
public void export() {
 
//定义导出的的表头，以及每个表头字段对应的对象变量名
List<String> headerArray = Arrays.asList("姓名","年龄");
List<String> fieldArray = Arrays.asList("username","age");
 
//定义要导出的excel的文件名，不带"xlsx"后缀。
String exportExcelFileName = "文件测试";
//每次导出new一个handler对象，将headerArray,fieldArray,exportExcelFileName传递进去。
ExcelResultHandler<User> handler = new ExcelResultHandler<User>(headerArray,fieldArray,exportExcelFileName) {
public void tryFetchDataAndWriteToExcel() {
//这里的this,指的就是ExcelResultHandler<User> handler这个对象，在这里写mapper调用获取数据的调用
userMapper.export(this);
}
};
//真正调用excel的导出开始，在方法中exportExcel会调用写excel表头，
//然后调用tryFetchDataAndWriteToExcel，进行驱动调用userMapper的方法，然后遍历结果集，一条一条写入excel,最后关闭盯应的流资源。
handler.startExportExcel();
 
}

 

说明： UserServiceImpl类的导出代码中，只需要new一个ExcelResultHandler,然后实现其抽象方法tryFetchDataAndWriteToExcel()，在tryFetchDataAndWriteToExcel方法中进行mapper的方法调用，传递一个ResultHander对象，如上面代码中的userMapper.export(this)， new完ExcelResultHandler对象时，导出还没有开始，执行handler.startExportExcel()才进行真正的导出功能,先拿到输出流，然后设置好excel的表头，并写入表头，然后调用tryFetchDataAndWriteToExcel方法，在tryFetchDataAndWriteToExcel方法中会调用mybatis的mapper的方法，在mybatis的mapper的方法中遍历所有的数据，然后一行一行写入excel，最终关闭资源流等，如下：`

1
2
3
4
5
6
7
public void handleResult(ResultContext<? extends T> resultContext) {
//获取数据，并回调ExportExcelUtils中的方法进行数据写入到excel，固定写法即可，不需要修改
Object aRowData = resultContext.getResultObject();
callBackWriteRowdataToExcel(aRowData);
}

 

4.测试结果截图如下：

输入http://localhost:8080/test/user/export进行导出测试 在这里插入图片描述 在这里插入图片描述

三、源码demo下载

详细源码附件如下：可直接下载进行测试

github: github.com/jxlhljh/spr… gitee: gitee.com/jxlhljh/spr…

 

作者：冰之杍
链接：https://juejin.cn/post/7012156305783652383
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

Copyright © 2024 Longsheng All rights reserved