Recently I have been working on the export function of Excel files with large amounts of data. Utilize database cursor query and POI’s sxssf technology to achieve smooth conversion of database data into streams and return to the front end. The front end saves the stream bit by bit into the memory through fetch, and downloads it using the a tag. However, a special exception handling problem was encountered during the development process. I’ve been troubled for a long time, so I’ll record it here. If there are students who encounter the same problem, please refer to it.
Direct code without verbosity
1.controller layer
@PostMapping("/user/getPage")
ResponseEntity<StreamingResponseBody> getPage(@RequestBody User user, HttpServletResponse response) throws FileNotFoundException {
StreamingResponseBody streamingResponseBody = new StreamingResponseBody() {
@Override
public void writeTo(OutputStream outputStream) throws IOException {
userService.queryStream(user,outputStream);
outputStream.close();
}
};
return ResponseEntity.ok()
.header(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=FileName.xlsx")
.contentType(MediaType.APPLICATION_OCTET_STREAM)
.body(streamingResponseBody);
}
2.service implementation class
@Override
public void queryStream(User user, OutputStream outputStream) throws IOException{
//Prevent front-end timeout from reading too large amount of data
outputStream.write("=start=".getBytes());
outputStream.flush();
File file = new File("/poi/temp");
if(!file.exists()){
file.mkdirs();
}
TempFile.setTempFileCreationStrategy(new DefaultTempFileCreationStrategy(file));
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
SXSSFSheet sheet = sxssfWorkbook.createSheet();
SXSSFRow row0 = sheet.createRow(0);
String[] props = new String[]{"id","name","age","mail","tel"};
for (int i = 0; i < 5; i + + ) {
String pname = props[i];
SXSSFCell cell = row0.createCell(i);
cell.setCellValue(pname);
}
Long aLong = Long.valueOf(user.getAge());
Map<String,Integer> map = new ConcurrentHashMap<>();
//Handle exceptions in cursor queries
map.put("isError", 0);
userMapper.dynamicSelectLargeData(aLong, new ResultHandler<User>() {
Integer size = 1;
@Override
public void handleResult(ResultContext<? extends User> resultContext) {
try {
User userObj = resultContext.getResultObject();
SXSSFRow row = sheet.createRow(size);
for (int i = 0; i < 5; i + + ) {
SXSSFCell cell = row.createCell(i);
if(i==0){
cell.setCellValue(userObj.getId());
}else if (i ==1){
cell.setCellValue(userObj.getName());
}else if (i ==2){
cell.setCellValue(userObj.getAge());
}else if (i ==3){
cell.setCellValue(userObj.getMail());
}else if (i ==4){
cell.setCellValue(userObj.getTel());
}
}
if (size % 100 == 0){
outputStream.write(1);
outputStream.flush();
// boolean committed = response.isCommitted();
// System.out.println("Is response closed: " + committed);
}
size + + ;
System.out.println(" + + + + + + + + + + + + + Current number of items: " + size + " + + + + + + + + + + + + + + + + + + ") ;
map.put("size", size);
}catch (Exception e){
System.out.println("Exit stream query abnormally");
resultContext.stop();
e.printStackTrace();
map.put("isError", 1);
}
}
});
try {
if(map.get("isError") != 1){
outputStream.write(("=end=;size=" + map.get("size")).getBytes());
outputStream.flush();
Thread.sleep(300);
sxssfWorkbook.write(outputStream);
}
} catch (IOException | InterruptedException e) {
Thread.currentThread().interrupt();
System.out.println("Abnormal download process");
e.printStackTrace();
return;
}finally {
try {
//Delete the temporary files generated by sxssf
File[] files = file.listFiles();
for (File fileTemp : files) {
fileTemp.delete();
}
sxssfWorkbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
Here comes the problem:
1. Timeout problem
Although the StreamingResponseBody is returned in chunks, the cursor query reads the data in the database in a loop. If the amount of data is large, the request will still time out when returning the data. The console will print Async request timeout. As a result, the request data is abnormally written to the output stream, and the code will be abnormal when it goes to outputstream.flush().
The solution is to configure the timeout of asynchronous requests by implementing the WebMvcConfigurer class.
@Override
public void configureAsyncSupport(AsyncSupportConfigurer configurer) {
//-1 means no timeout. It is recommended to set the millisecond value yourself according to the project
//springboot default is 30 seconds
configurer.setDefaultTimeout(-1);
}
2. Browser disconnection problem.
If the browser is suddenly closed during the download process, the connection will be disconnected. When the code reaches outputstream.flush(), an exception will occur again. The more serious problem is that the next time you request to check it in the browser, 500 will be reported directly. Very unfriendly to the server. (Guess the reason is that springboot closed the stream, but did not notify the servlet to close this request)
The solution is to throw an IOException for all exceptions caused by outputstream.flush() in the queryStream method to the StreamingResponseBody and rewrite the writeto method. Because this method will also throw an io exception. When springboot recognizes an io exception, it will notify the servlet to close this request and respond. problem solved
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java Skill TreeHomepageOverview 138895 people are learning the system