利用Mybatis的resultHandler和POI，实现超大数据量导出到EXCEL。
琴俊人
采用Mybatis持久化框架，查询数据采用List模式返回结果，这种数据返回模式，能满足大多数的在少量返回结果集，或分页结果集的应用场景。
针对要大量返回结果集，特别是需要把超大查询结果集返回或导出的时候，List模式由于需要在应用服务器中构造对象。这不但需要消耗大量内存，而且会引起频繁的垃圾回收，造成性能降低。甚至因为内存无法容纳过大的结果集List，造成OOM异常。
有人采用分页模式，逐页导出的方式来解决。这种解决方式，针对超大结果集，比如数百万数据行时，由于可能会产生上万个分页查询，造成数据库上万次查询，给数据库性能带来灾难性影响。
本文档采用Mybatis的ResultHandler接口，采用ResultHandler回调模式，实现逐条处理，既实现一次查询获得所有数据，不引起数据库性能灾难，又避免应用服务器内存过大消耗的问题。
下面直接上实现代码。
1.依赖的包引入
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>2.超大数据集导出EXCEL工具类ExcelGenerator，本类实现了ResultHandler，通过回调，逐条导出。具体代码
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.util.Map;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* Mybatis查询数据采用List的方式返回结果集，当结果集过大，内存无法将结果集对象全部容纳，抛出OOM异常，
* 本类主要采用Mybatis提供的ResultHandler接口逐条处理数据的方式读取数据，对数据进行转化。
* 并将结果集逐行转化为EXCEL，当结果集过大，Workbook对象也会太大，故可以设置isLargeExcel=true，
* 采用POI缓存机制，达到一定条目，缓存到临时文件的方式，用以实现巨量数据导出为EXCEL。
* @author tbr
*
* @param <T> 转化后，用于输出到EXCEL的对象类
* @param <E> Mybatis对象类
*/
public abstract class ExcelGenerator<T,E> implements ResultHandler<E> {
protected String[] titles;
protected String[] fields;
protected int colcnt;
protected Map<String,DecimalFormat> numberFormats;
protected Map<String,DateFormat> dateFormats;
protected Workbook workbook;
protected String sheetName;
protected int sheetIndex;
protected Sheet sheet;
protected int rownum = 0;
protected boolean isLargeExcel=false;
/**
*
* @param titles excel表头名列表
* @param fields 需要输出的excel列对应的数据对象属性名列表
* @param numberFormats 需要格式化的数字类属性的输出格式映射表
* @param dateFormats 需要格式化的日期型字段属性的输出格式映射表
*/
public ExcelGenerator(String[] titles, String[] fields,
Map<String,DecimalFormat> numberFormats,
Map<String,DateFormat> dateFormats) {
if (titles==null)
this.titles=fields;
else
this.titles=titles;
this.fields=fields;
this.colcnt=fields.length;
this.numberFormats=numberFormats;
this.dateFormats=dateFormats;
}
public void initWorkbook(String sheetName,boolean isLargeExcel) throws IOException{
this.isLargeExcel=isLargeExcel;
this.sheetName=sheetName;
if (isLargeExcel){
SXSSFWorkbook wb = new SXSSFWorkbook(-1);
wb.setCompressTempFiles(true);
workbook=wb;
}else{
workbook = new XSSFWorkbook();
}
sheetIndex=1;
sheet = workbook.createSheet(sheetName+"-"+sheetIndex);
createHeader();
}
public Workbook getWorkbook(){
return workbook;
}
@Override
public void handleResult(ResultContext<? extends E> resultContext) {
rownum++;
try {
E entity=resultContext.getResultObject();
if (entity==null){
return;
}
createRow(convert(entity));
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 数据转化处理
*/
public abstract T convert(E entity);
private void createRow(Object data) throws IOException{
Row row = sheet.createRow(rownum);
for(int cellnum = 0; cellnum < colcnt; cellnum++){
createCell(rownum,row,cellnum,data);
}
if (isLargeExcel && rownum % 1000 == 0) {
// 每100行，缓存一下，减少内存消耗
//System.out.println("rownum:"+rownum+"-----------"+System.currentTimeMillis());
((SXSSFSheet)sheet).flushRows(1000);
}
if (rownum % 1000000 == 0) {
//一百万行，另外创建一个sheet
sheetIndex++;
String newSheetName=sheetName+"-"+sheetIndex;
//System.out.println("rownum:"+rownum+"-----------"+newSheetName);
sheet=workbook.createSheet(newSheetName);
createHeader();
}
}
private void createCell(int rownum,Row row,int cellnum,Object data) throws IOException{
Cell cell = row.createCell(cellnum);
String fieldName=fields[cellnum];
Object fieldValue=getFieldData(data,fieldName);
Object cellValue=fieldValue;
if ( numberFormats != null && numberFormats.containsKey(fieldName) ){
cellValue=numberFormats.get(fieldName).format(fieldValue);
}
if ( dateFormats != null && dateFormats.containsKey(fieldName) ){
cellValue=dateFormats.get(fieldName).format(fieldValue);
}
if (cellValue!=null){
cell.setCellValue(cellValue.toString());
}else{
cell.setCellValue("");
}
}
private Object getFieldData(Object data,String fieldName){
Object ret=data;
String[] fieldList=fieldName.split("\\.");
for(String fn:fieldList){
if (ret==null)
return ret;
try {
ret=BeanUtils.forceGetProperty(ret,fn);
} catch (NoSuchFieldException e) {
e.printStackTrace();
ret=null;
}
}
return ret;
}
private void createHeader(){
rownum=0;
//输出表头
Row row = sheet.createRow(rownum);
Cell cell;
for (int cellnum=0;cellnum<titles.length;cellnum++ ){
cell = row.createCell(cellnum);
cell.setCellValue(titles[cellnum]);
}
}
}
3.使用方法
定义使用ResultHandler的Mapper方法。
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.ResultSetType;
import org.apache.ibatis.session.ResultHandler;
@Mapper
public interface XXXXMapper {
......
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
@ResultType(XXXX.class)
void query1By1(@Param("params") Map params,ResultHandler<XXXX> handler);
......
}
4.导出数据例子
.......
@RequestMapping("2excel")
public void test2Excel() throws IOException{
String fileName="测试文件";
String[] titles={......};//excel标题列表
String[] fields={......};//导出到excel的属性列表
Map<String,DecimalFormat> numberFormats=null;//数字格式map
Map<String,DateFormat> dateFormats=null;//日期格式map
String fn=java.net.URLEncoder.encode(fileName, "UTF-8");
response.setCharacterEncoding("utf-8");
OutputStream out;
response.setContentType("Content-Type: application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment; filename="
+ fn + ".xlsx");
out = response.getOutputStream();
Map<String,Object> paras=new HashMap<>();
ExcelGenerator<BizBean,EOBean> deu=new ExcelGenerator<BizBean,EOBean>(titles,fields,numberFormats,dateFormats){
public BizBean convert(EOBean entity){
BizBean bizentity=new BizBean();
.......//对象转化，将数据库实体对象转化为导出所需要的业务对象
return bizentity;
}
};
deu.initWorkbook(fileName, true);
appInfoMapper.query1By1(paras,deu);
Workbook wb=deu.getWorkbook();
wb.write(out);
out.flush();
if (wb instanceof SXSSFWorkbook){
((SXSSFWorkbook)wb).dispose();
}
wb.close();
out.close();
return;
}
......到此，你就可以照此开发超大规模数据的导出了。你不但可以导出几百几千的，你甚至可以导出上亿的。只是这个EXCEL太大了，你的OFFICE是否能打开就不知道。
5.写在后面
当然，你也可以修改一下，导出为CSV文件，这个就没有任何数据量的限制了。
是不是很爽
类似资料：