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

冰之杍
4,637 阅读6分钟

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

一.先批量插入测试数据

1.建表，包含2个字段username,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 '姓名',
  `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

	<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

int batchInsert(List<User> list);

4.UserService.java & UserServiceImpl.java

int batchInsert(List<User> list);

在这里插入图片描述

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

	//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

	<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中编写方法

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

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

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

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

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，如下：

	/**导出*/
	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，最终关闭资源流等，如下：`

	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…

avatar
文章
阅读
粉丝