Exporting Excel is a common function in the process of project development. In the previous article, exporting large Excel files in Java to prevent memory overflow has solved the memory problem when writing Excel. However, at the database query level, there are still problems because the default Mybatis query is to query all data into local memory. Therefore, it is still possible to cause memory overflow. Therefore, this paper introduces in detail that records can be read by streaming query in ResultHander of Mybatis to completely solve the memory overflow problem of excel’s massive data export.

First, batch insert test data

1. Create a table containing two columns username and age

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 'The name', `age` int(11) NOT NULL COMMENT 'age', 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;Copy the code

2.UserMapper.xml

	<insert id="batchInsert">
		insert into t_user(username,age)
		values
		<foreach collection="list" item="item" separator=",">
			(#{item.username},#{item.age})
		</foreach>
	</insert>
Copy the code

3.UserMapper.java

int batchInsert(List<User> list);
Copy the code

4.UserService.java & UserServiceImpl.java

int batchInsert(List<User> list);
Copy the code

5. Write test code in userController.java and write 1 million lines of data

	Insert 1000101 rows of test data
	//http://localhost:8080/test/user/batchInsert
	@RequestMapping(value="/batchInsert", method = RequestMethod.GET)
	public String batchInsert(a){

		logger.info("method starting...");
		long startTime = System.currentTimeMillis();
		
		// Insert 2000 records at a time to improve insert efficiency
		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();/ / remove the list}}long endTime = System.currentTimeMillis();

		logger.info("method finished,total spend time: {} ms.",(endTime-startTime));

		return "batchInsert";
	}
Copy the code

ResultHandler streaming query export

The ResultHandler interface can be used for streaming queries (i.e. line by line reading processing from the database, so it does not occupy local memory). The core of this paper is to call the method of Mapper, pass in a ResultHandler, and then read data in the implemented method, and then process it line by line.

1. Set the parameters in usermapper. XML

ResultSetType is FORWARD_ONLY and fetchSize is -2147483648

	<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>
Copy the code

2. Write methods in usermapper. Java

	ResultHandler {/**}} /** {/**}} /** {/**}} /** {/**
	public void export(ResultHandler<User> resultHandler);
Copy the code

Important note: To satisfy streaming queries, a method return value of void is required and the method has parameters of type ResultHandler.Mybatis source mappermethod. Java can also be seen in the corresponding code is determined as follows:

3. Call ResultHandler from UserServiceImpl implementation class and write excel as follows:

Note: The logic for writing Excel is written in ExportExcelUtils, which can be used in other export code. Just change the method calls of headerArray, fieldArray, exportExcelFileName, and the corresponding Mapper object

	/ * * * / is deduced
	public void export(a) {

		// Define the exported header and the object variable name for each header field
		List<String> headerArray = Arrays.asList("Name"."Age");
		List<String> fieldArray = Arrays.asList("username"."age");

		// Define the name of the Excel file to export without the "XLSX" suffix.
		String exportExcelFileName = "File Test";

		// Call the public method of ExportExcelUtils to export excel
		new ExportExcelUtils(headerArray,fieldArray,exportExcelFileName) {
			
			public void tryFetchDataAndWriteToExcel() {

				// Call mybatis query via streaming query (no memory) and iterate over each row of data for Excel write
				// Change the code here to the corresponding mapper
				// The mapper method needs to return void, and the argument contains ResultHandler.
				userMapper.export(new ResultHandler<User>() {
					public void handleResult(ResultContext<? extends User> resultContext) {

						// Get the data and call back to the ExportExcelUtils method to write the data to ExcelObject aRowData = resultContext.getResultObject(); callBackWriteRowdataToExcel(aRowData); }}); }} instead!export(a); }Copy the code

With an abstract method in ExportExcelUtils tryFetchDataAndWriteToExcel, provided to the user’s own implementation, user in tryFetchDataAndWriteToExcel mybatis mapper query, Then the data in the traversal ResultHandler, then one line in the excel (by writing excel callback callBackWriteRowdataToExcel method), ExportExcelUtils. Details of the Java code is as follows:

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.Arrays;
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.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 cn.gzsendi.modules.framework.reflect.Reflector;
import cn.gzsendi.modules.framework.reflect.reflectasm.MethodAccessor;

public abstract class ExportExcelUtils {
	
	private final Logger logger = LoggerFactory.getLogger(this.getClass());

	private AtomicInteger currentRowNumber = new AtomicInteger(0);// Record the current Excel line number, starting from 0
	private Sheet sheet = null;

	private List<String> headerArray = Arrays.asList("Name"."Age");
	private List<String> fieldArray = Arrays.asList("username"."age");
	
	// Define totalCellNumber variable,
	private int totalCellNumber = headerArray.size();

	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	// Define the excel file name to export, without the XLSX suffix. The default is uuID, which can also be changed by passing in the constructor.
	private String exportFileName = UUID.randomUUID().toString().replace("-"."");

	public ExportExcelUtils(List<String> headerArray,List<String> fieldArray){

		this.headerArray = headerArray;
		this.fieldArray = fieldArray;

	}

	public ExportExcelUtils(List<String> headerArray,List<String> fieldArray,String exportFileName){

		this.headerArray = headerArray;
		this.fieldArray = fieldArray;
		this.exportFileName = exportFileName;

	}

	// The image method is provided to subclasses for implementation, traversing and writing data to Excel
	public abstract void tryFetchDataAndWriteToExcel(a);
	
	/ * * * / is deduced
	public void export(a) {
		
		HttpServletResponse response = ContextHolderUtils.getResponse();
		
		ZipOutputStream zos = null;
		
		try {
			
			logger.info("--------->>>> write Excel start.." );
			
			// Write to the file
			response.setContentType("application/octet-stream");
			response.setHeader("Content-Disposition"."attachment; filename=" + new String((exportFileName+".zip").replaceAll(""."").getBytes("utf-8"),"iso8859-1"));
			OutputStream os = new BufferedOutputStream(response.getOutputStream());
			
			zos = new ZipOutputStream(os);
			
			ZipEntry zipEntry = new ZipEntry(new String((exportFileName+".xlsx").replaceAll(""."")));
			zos.putNextEntry(zipEntry);
			
			SXSSFWorkbook wb = new SXSSFWorkbook();// The default is 100 lines, with more than 100 lines written to temporary files
			wb.setCompressTempFiles(false); // Whether to compress temporary files, otherwise write faster, but more disk, but the program will delete temporary files eventually
            sheet = wb.createSheet("Sheet 1");
			
	        // write the header, Rows starts at 0.
	        Row row = sheet.createRow(0);
	        for (int cellNumber = 0; cellNumber < totalCellNumber; cellNumber++) {
	        	
	        	Cell cell = row.createCell(cellNumber);
                cell.setCellValue(headerArray.get(cellNumber)); // Write header data
	        	
	        }
	        
	        // Write data
		    / * * * * * * * * * * * * * * * * * * * * * * * * * * * * /
	        // Call the code of the concrete implementation subclass to try to get the data to iterate over and write to Excel
	        tryFetchDataAndWriteToExcel();

	        // Finally print the number of lines that were finally written
			logger.info("--------->>>> write to excel size now is {}", currentRowNumber.get() );
	        
	        //Write excel to a file
	        wb.write(zos);
	        
	        if(wb ! = null) { wb.dispose();// It is important to delete temporary files, otherwise the disk may become full
            }
	        
	        wb.close();
	        
		    / * * * * * * * * * * * * * * * * * * * * * * * * * * * * /
	  		
	  		logger.info("--------->>>> all data written to Excel completed.." );
			
		} catch (Exception e) {
			
			logger.error("error",e);
			
		} finally {
			
			// Close the resource
	    	try {if(zos! =null) zos.close(); }catch (IOException e1) {logger.error("error",e1); }}}// Write a row of data to Excel to provide to the ResultHandler loop for callback
	@SuppressWarnings("rawtypes")
	public void callBackWriteRowdataToExcel(Object aRowData) {

		// Reflection gets the value and sets it to the Cell column in Excel
		MethodAccessor methodAccessor = Reflector.getMethodAccessor(aRowData.getClass());

		// Add the line number first
		currentRowNumber.incrementAndGet();
		// Create a new row in Excel
		Row row = sheet.createRow(currentRowNumber.get());
		for (int cellNumber = 0; cellNumber < totalCellNumber; cellNumber++) {

			// If aRowData is a map, special processing is required to obtain it. Cannot be called with methodAccessor reflection.
			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 converts a string formatted date by default
            if(value! =null && value instanceof Date){ cell.setCellValue(sdf.format(value));//
            }else {
            	cell.setCellValue(value==null?"":value.toString());// Write data}}// Print every 5000 entries
		if(currentRowNumber.get() % 5000= =0 ){
			logger.info("--------->>>> write to excel size now is {}", currentRowNumber.get() ); }}}Copy the code

4. The screenshot of the test results is as follows:

The inputhttp://localhost:8080/test/user/export to export test

Third, source code demo download

Detailed source code attachment is as follows: can be downloaded directly for testing

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

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Solve memory overflow problem of large Excel export by Mybatis streaming query ResultHandler

First, batch insert test data

1. Create a table containing two columns username and age

2.UserMapper.xml

3.UserMapper.java

4.UserService.java & UserServiceImpl.java

5. Write test code in userController.java and write 1 million lines of data

ResultHandler streaming query export

1. Set the parameters in usermapper. XML

2. Write methods in usermapper. Java

3. Call ResultHandler from UserServiceImpl implementation class and write excel as follows:

4. The screenshot of the test results is as follows:

Third, source code demo download

Solve memory overflow problem of large Excel export by Mybatis streaming query ResultHandler

First, batch insert test data

1. Create a table containing two columns username and age

2.UserMapper.xml

3.UserMapper.java

4.UserService.java & UserServiceImpl.java

5. Write test code in userController.java and write 1 million lines of data

ResultHandler streaming query export

1. Set the parameters in usermapper. XML

2. Write methods in usermapper. Java

3. Call ResultHandler from UserServiceImpl implementation class and write excel as follows:

4. The screenshot of the test results is as follows:

Third, source code demo download

Related Posts

With 100 million keys to count, which set should I use?

SpringBoot Admin2.0 integrates the Arthas practice for Java diagnostics

Code generator principles and implementation – PYTHON version 3