在业务系统中，导出报表的需求会很常见，而随着时间推移业务量不断增加，数据库的数据可能达到百万甚至千万级别。对于导出报表功能，最简单的做法就是从数据库里面把需要的数据一次性加载到内存，然后写入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
JPA使用游标方式导出百万数据(两种方式，推荐使用QueryDSL) 1.使用jpa原生方式,这种适合sql比较简单的情况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();
}
使用到的相关的类：
/**
* @author 奔腾的野马
* @date 2022/04/25 09:12
*/
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;
/**
* @Author: 奔腾的野马
* @Date: 2021/10/16 16:19
*/
@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:
/**
* @author 奔腾的野马
* @date 2022/04/16 19:14
*/
@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：
同上
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）