利用Mybatis的resultHandler和POI，实现超大数据量导出到EXCEL。

最新推荐文章于 2024-06-18 10:11:51 发布
最新推荐文章于 2024-06-18 10:11:51 发布
阅读量3k 收藏 35
点赞数 4
分类专栏： 大数据集导出 文章标签： mybatis excel poi java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tanghuan/article/details/109181846
版权

采用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文件，这个就没有任何数据量的限制了。

是不是很爽

tanghuan
关注
  • 4
    点赞
  • 踩
  • 35
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
专栏目录
Mybatis 中 ResultHandler 的用法（获取大结果集，实现结果流式输出）
长安明月的博客
09-20 4300
Mybatis 中的 ResultHandler 相当于数据结果集的处理器，它是一个回调函数（Callback），用来处理每一行数据的结果，这个回调函数可以在查询结果处理到一定量时触发，对结果集数据进行定制化处理。ResultHandler 的使用可以大幅提升数据处理的效率，当我们需要处理大量的数据时，一般会使用 ResultHandler 来进行结果的处理，避免一次查询就全部返回结果，浪费内存资源或造成 OOM。import org// SQL 返回值为 void，所以我们并没有接收这个返回值。
1. 使用MyBatis的ResultHandler实现流式查询，避免大数据量一次加载
大头鱼
03-11 1400
使用MyBatis的ResultHandler实现流式查询，避免大数据量一次加载
带你学习Mybatis之ResultSetHandler结果集处理
最新发布
Lxn2zh的博客
06-18 379
当真正要使用延迟加载的属性时，会通过代理对象执行数据库加载操作，得到真正的数据。Javassist是一个开源的生成java字节码的类库，其主要优点在于简单、快速，直接使用Javassist提供的API就可以动态的修改类的结构，或是动态的生成类。CGLIB采用字节码技术实现动态代理功能，原理是通过字节码技术为目标类生成子类，并在子类中采用方法拦截的方式拦截所有父类方法的调用，从而实现代理的功能。延迟加载使用动态代理实现的，由于映射的结果对象都为普通的类，没有使用接口，所以无法使用JDK代理。
【mybatis】大数据流式查询(案例)
掘金者说
10-14 756
【代码】【mybatis】大数据流式查询(案例)
从 MySQL 数据库里读取 500w 数据行进行处理，应该怎么做更效益化？
meser88的博客
11-18 1708
前言 由于现在 ORM 框架的成熟运用，很多小伙伴对于 JDBC 的概念有些薄弱，ORM 框架底层其实是通过 JDBC 操作的 DB JDBC（JavaDataBase Connectivity）是 Java 数据库连接, 说得直白点就是使用 Java 语言操作数据库 由 SUN 公司提供出一套访问数据库的规范 API， 并提供相对应的连接数据库协议标准， 然后 各厂商根据规范提供一套访问自家数据库的 API 接口 1 MySql JDBC 大数据量操作 整篇文章以大数据量操作为议题，通过开发过程
Mybatis流式查询
m0_61811711的博客
07-04 3936
Mybatis流式查询的四种方式
MySQL设置了useCursorFetch，FetchSize参数的影响
cfy_zybing的专栏
04-28 8035
从MySQL中获取一定量的数据出来，如果不设置 useCursorFetch，mysql会把所有的数据从数据库端搬到客户端后再做处理，这样我们原先使用ResultHandler的方式节省内存的打算就落空了。 还好MySQL在连接串上，使用了useCursorFetch=true，就可以达到将部分数据搬到客户端就进行处理，那么这时 FetchSize 对系统会产生什么影响； 以下FetchSiz...
mybatisPlus、BigExcelWriter大数据量流式查询并导出
weixin_44792849的博客
08-06 1222
对于大量数据输出，采用EasyExcel.write()或者hutool的ExcelWriter容易引起内存溢出，因此有了BigExcelWriter。Oracle的fetchSize建议设置成1000，MySQL的fetchSize建议设置成Integer.MIN_VALUE。因此流式查询则使用数据库游标，查一行处理一行，消耗内存较少，不阻塞数据库IO,在大数据处理场景中推荐使用。DAO层的流式处理函数的声明。
mybatis查询postgre，fetchSize失效排查记录
xx244的博客
10-11 1268
下级入口DataSourceUtils.getConnection(this.dataSource);下级入口queryFromDatabase–>getConnection。在此处已可查看获取到的数据库连接，自动提交为true。下级入口executor.query。
jdbc，mybatis中fetchsize使用，批处理方案
热门推荐
FOREVER_DO
08-18 2万+
数据库优化之查询优化，fetchsize使用，常用sql
【Spring系列】SpringBoot+Mybatis实现excel文件的下载方式
vvshuai的博客
11-28 376
SpringBoot整合POI实现xls文件下载 使用文件流的方式进行返回 引入依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.10-FINAL</version> </dependency> <dependency> <groupId>org.apa
SpringMVC + Apache POI 实现WEB中Excel下载功能
LH9898的博客
02-26 465
项目中需要将web页面中的报表导出成Excel，在网上搜寻了写资料，实现了相关功能，如图1所示： 项目后台架构采用的是SpringMVC+Spring+Mybatis，通过引入Apache POI实现excel的下载功能。 导出效果如图2所示： 首先页面中点击“导出Excel”按钮就会触发如下js代码： [javascript] view plain copy prin...
使用Mybatis Stream 模式 优化大数据量查询和导出
Myth的博客
07-13 3639
背景 特定查询条件下测试数据量 70w （查询条件优化并命中了组合索引），group by 聚合后 3w 条数据，并进行导出为Excel 1. 查询数据库 select * from xxx group by user where create_date BETWEEN ‘’ AND ‘’ 1.1 分页查询 假如分页大小为 1000, 需要执行 30 次SQL才能得到所有数据。 此时MySQL扫描的行数是 70w * 30 = 2100w。 由于 group by 使用了中间表，当分页执行到尾声，性能下降特
百万数据的导入导出解决方案
竹林幽深
10-13 801
https://mp.weixin.qq.com/s/Uthw86eXVOXi3Zaz8q4LxQ
使用流式读取导出
beyondMyfaith的博客
05-25 1396
流式读取导出
mybatis流式导出大数据excel
sinat_34163739的博客
04-30 1281
void selectStream(Vo vo,ResultHandler<Vo> resultHandler); mapper.java代码 <select id="selectStream" resultMap="VoResult" resultSetType="FORWARD_ONLY" fetchSize="-2147483648"> select * from table1 </select> mapper.xml代码resultSetType=...
大数据导出（最高可达2.6624亿）
my learning notes
03-27 871
SXSSFWorkbook wb = new SXSSFWorkbook(15);// 构造一个空的工作薄，并指定一个窗口访问 Sheet sh = wb.createSheet(); // 居中 // 宽度设置 sh.setColumnWidth(0, 25 * 256); sh.setColumnWidt
springboot mybatis poi 实现excel导入导出
05-14
实现Excel导入导出，需要用到POI库。下面介绍一下使用SpringBoot集成MyBatis和POI实现Excel导入导出的步骤。 1. 引入依赖 在pom.xml文件中添加以下依赖： ``` <!-- SpringBoot MyBatis 依赖 --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> <!-- POI 依赖 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency> ``` 2. 创建实体类 创建一个实体类，用于映射Excel文件中的数据。 ```java public class User { private Integer id; private String name; private String email; private String phone; // 省略 getter 和 setter 方法 } ``` 3. 创建Mapper 创建一个Mapper，用于操作数据库。 ```java @Mapper public interface UserMapper { List<User> findAll(); void insert(User user); void batchInsert(List<User> userList); } ``` 4. 创建Service 创建一个Service，用于导入和导出Excel文件。 ```java @Service public class UserService { @Autowired private UserMapper userMapper; public List<User> findAll() { return userMapper.findAll(); } public void insert(User user) { userMapper.insert(user); } public void batchInsert(MultipartFile file) throws IOException { List<User> userList = readExcel(file); userMapper.batchInsert(userList); } public void exportExcel(HttpServletResponse response) throws IOException { List<User> userList = userMapper.findAll(); writeExcel(response, userList); } private List<User> readExcel(MultipartFile file) throws IOException { List<User> userList = new ArrayList<>(); Workbook workbook = WorkbookFactory.create(file.getInputStream()); Sheet sheet = workbook.getSheetAt(0); for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); User user = new User(); user.setName(row.getCell(0).getStringCellValue()); user.setEmail(row.getCell(1).getStringCellValue()); user.setPhone(row.getCell(2).getStringCellValue()); userList.add(user); } return userList; } private void writeExcel(HttpServletResponse response, List<User> userList) throws IOException { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("Users"); Row headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("Name"); headerRow.createCell(1).setCellValue("Email"); headerRow.createCell(2).setCellValue("Phone"); for (int i = 0; i < userList.size(); i++) { Row row = sheet.createRow(i + 1); User user = userList.get(i); row.createCell(0).setCellValue(user.getName()); row.createCell(1).setCellValue(user.getEmail()); row.createCell(2).setCellValue(user.getPhone()); } response.setHeader("Content-Disposition", "attachment; filename=users.xlsx"); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); workbook.write(response.getOutputStream()); } } ``` 5. 创建Controller 创建一个Controller，用于接收导入和导出Excel文件的请求。 ```java @RestController public class UserController { @Autowired private UserService userService; @GetMapping("/users") public List<User> findAll() { return userService.findAll(); } @PostMapping("/users") public void insert(@RequestBody User user) { userService.insert(user); } @PostMapping("/users/import") public void batchInsert(@RequestParam("file") MultipartFile file) throws IOException { userService.batchInsert(file); } @GetMapping("/users/export") public void exportExcel(HttpServletResponse response) throws IOException { userService.exportExcel(response); } } ``` 至此，就完成了SpringBoot集成MyBatis和POI实现Excel导入导出的步骤。
评论 5
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值