java实现百万级别数据导出excel(JPA,hibernate,mybatis三种方式)

在业务系统中，导出报表的需求会很常见，而随着时间推移业务量不断增加，数据库的数据可能达到百万甚至千万级别。对于导出报表功能，最简单的做法就是从数据库里面把需要的数据一次性加载到内存，然后写入excel文件，再把excel文件返回给用户。这种做法在数据量不大的时候是可行的，但是一旦需要导出几十万甚至上百万的数据，很可能出现OOM导致服务崩溃的情况，而且导出所消耗的时间会大大增加。

这里提供一种支持百万级别数据导出的方法，并且消耗很少的内存，核心思想就是不要一次性把数据加载到内存中。主要是从两个方面去解决：

1.从数据库加载数据不要一次性加载，可以分页的方式或者用游标的方式分批加载数据，加载一批数据处理一批并且释放内存，这样内存占用始终处于一个比较平稳的状态。分页的方式加载编码比较繁琐，我一般是采用游标方式逐行加载。目前常用的持久层框架有JPA，mybaits,hibernate，下面会分别列出JPA，hibernate及mybatis通过游标方式加载数据。

2.写入excel也是分批写入，推荐阿里的EasyExcel，占用内存极低。

EasyExcel的pom依赖：

 
	com.alibaba
	easyexcel
	2.1.1
	true

jdk1.8，idea2019，堆内存：-Xms256M -Xms256M(导出100万数据毫无压力)，springboot,数据库是mysql

pom.xml:

org.springframework.boot
spring-boot-starter-web



org.springframework.boot
spring-boot-starter-data-jpa


    org.projectlombok
    lombok
    true

repository:

@Repository
public interface UserRepository extends JpaRepository {
	
	//@QueryHint(name = HINT_FETCH_SIZE,value = Integer.MIN_VALUE+"") 值设置为Integer.MIN_VALUE告诉mysql需要逐条返回数据，并且返回值需要用stream来接收
    @QueryHints(@QueryHint(name = HINT_FETCH_SIZE,value = Integer.MIN_VALUE+""))
    @Query(value = "select * from user limit 500000",nativeQuery = true)
    Stream findAllList();

}

service:

注意:

需要加事务注解，并且是只读事务

需要及时调用entityManager的detach方法释放内存，不然还是会出现OOM

   @Autowired
   private EntityManager entityManager;

   @Autowired
   private UserRepository userRepository;
   
   Transactional(readOnly = true)
   public void exportData3(ScrollResultsHandler scrollResultsHandler){

        Stream allList = userRepository.findAllList.forEach((o)->{
            UserEntity userEntity = (UserEntity) o;
            
            UserExportVO userExportVO = UserExportVO.builer()
            .userName(userEntity.getUsername())
            .mobile(userEntity.getMobile())
            .build();
            
            scrollResultsHandler.handle(userExportVO);
            
			//对象被session持有，调用detach方法释放内存
            entityManager.detach(userEntity);

        });

    }

controller:

    @RequestMapping("export4")
    public void export4(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String filenames="bigdata4";
        response.addHeader("Content-Disposition", "filename=" + filenames + ".xlsx");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), UserExportVO.class).build();
        WriteSheet[] writeSheet = new WriteSheet[] {
                EasyExcel.writerSheet(0, "sheet").build()
        };
  
  		userService.exportData(s->{
                UserExportVO resultObject = s;
                ArrayList arrayList = new ArrayList();
                arrayList.add(resultObject);
                excelWriter.write(arrayList, writeSheet[0]);
            });

        excelWriter.finish();
        
    }

使用到的相关的类：

public interface ScrollResultsHandler {

    void handle(T t);

}

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.time.LocalDateTime;


@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class UserExportVO {

    @ExcelProperty(value = "用户名")
    private String userName;

    @ExcelProperty(value = "手机号")
    private String mobile;

}

pom.xml:

   
        
            org.springframework.boot
            spring-boot-starter-web
        

        
            org.springframework.boot
            spring-boot-starter-data-jpa
        
        
        
            com.querydsl
            querydsl-apt
            5.0.0
            provided
        
        
        
            com.querydsl
            querydsl-jpa
            5.0.0
        

        
            com.querydsl
            querydsl-core
            5.0.0
        

        


	
	
		com.mysema.maven
		apt-maven-plugin
		1.1.3
		
			
				
					process
				
				
					target/generated-sources/java
					com.querydsl.apt.jpa.JPAAnnotationProcessor
				
			
		
	

    

service:

@Autowired
private JPAQueryFactory jpaQueryFactory;
private QUserEntity qUserEntity = QUserEntity.userEntity;

@Transactional(readOnly = true)
public void exportData2(ScrollResultsHandler scrollResultsHandler){

    //需要用stream方式接收，这样才能逐条处理
	Stream userExportVOStream = jpaQueryFactory.select(Projections.bean(UserExportVO.class
			, qUserEntity.userName, qUserEntity.mobile))
			.from(qUserEntity)
			//.join(xxxEntity) 
			//.on(xxxx)
			//setHint(HINT_FETCH_SIZE,Integer.MIN_VALUE+"") 告诉mysql需要逐条返回数据,注意值需要设置为Integer.MIN_VALUE才能生效
			.setHint(HINT_FETCH_SIZE,Integer.MIN_VALUE+"")
			.limit(1000000)
			.stream();

	userExportVOStream.forEach(dto->{
		scrollResultsHandler.handle(dto);
	});
	
}

controller:

同上

pom.xml:

	org.mybatis
	mybatis
	3.5.9

dao:

@Mapper
public interface UserDao {

	//ResultSetType.TYPE_FORWORD_ONLY 结果集的游标只能向下滚动,fetchSize需要设置为Integer.MIN_VALUE游标才能生效
    @Options(resultSetType = ResultSetType.FORWARD_ONLY,fetchSize = Integer.MIN_VALUE)
    @ResultType(UserExportVO.class)
    @Select("select userName,mobile from user limit 500000")
    void reportAll2(ResultHandler handler);

}

service:

@Transactional(readOnly = true)
    public void export2(ResultHandler handler){
        userDao.reportAll2(handler);
}    

controller:

同上

service:

   @Autowired
   private EntityManager entityManager;

public void exportData(ScrollResultsHandler scrollResultsHandler){
        //当不需要缓存时，最好使用StatelessSession
        StatelessSession session = ((Session) entityManager.getDelegate()).getSessionFactory().openStatelessSession();

        Query query = session.getNamedQuery("getAllList");
        query.setCacheMode(CacheMode.IGNORE);
        //setFetchSize(Integer.MIN_VALUE)告诉mysql逐条返回数据
        query.setFetchSize(Integer.MIN_VALUE);
        query.setFirstResult(0);
        query.setMaxResults(1000000);
        query.setReadOnly(true);
        query.setLockMode("a", LockMode.NONE);
        //ScrollMode.TYPE_FORWORD_ONLY 结果集的游标只能向下滚动
        ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);

        while (results.next()) {
            UserEntity userEntity = (UserEntity) results.get(0);      
            UserExportVO userExportVO = UserExportVO.builer()
            .userName(userEntity.getUsername())
            .mobile(userEntity.getMobile())
            .build();
            scrollResultsHandler.handle(userExportVO);
        }
        results.close();
        session.close();

}

controller：

同上