参考:https://grokonez.com/spring-framework/spring-boot/excel-file-download-from-springboot-restapi-apache-poi-mysql。虽然这是个可行的方案,然而一旦mysql数据量太大,达到十万级,百万级,千万级,大规模数据加载到内存必然会引起OutofMemoryError。要考虑如何避免OOM,一般有两个方面的思路。
5.如果通过分页导出,每次点击按钮只导2万条,分批导出难道不能满足业务需求吗?
@QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "" + Integer.MIN_VALUE)) @Query(value = "select t from Todo t") Stream<Todo> streamAll();此外还需要在Stream处理数据的方法之上添加@Transactional(readOnly = true),保证事物是只读的。同时需要注入javax.persistence.EntityManager,通过detach从内存中移除已经使用后的对象。
@RequestMapping(value = "/todos.csv", method = RequestMethod.GET) @Transactional(readOnly = true) public void exportTodosCSV(HttpServletResponse response) { response.addHeader("Content-Type", "application/csv"); response.addHeader("Content-Disposition", "attachment; filename=todos.csv"); response.setCharacterEncoding("UTF-8"); try(Stream<Todo> todoStream = todoRepository.streamAll()) { PrintWriter out = response.getWriter(); todoStream.forEach(rethrowConsumer(todo -> { String line = todoToCSV(todo); out.write(line); out.write("\n"); entityManager.detach(todo); })); out.flush(); } catch (IOException e) { log.info("Exception occurred " + e.getMessage(), e); throw new RuntimeException("Exception occurred while exporting results", e); } }
public class DownloadProcessor { private final HttpServletResponse response; public DownloadProcessor(HttpServletResponse response) { this.response = response; String fileName = System.currentTimeMillis() + ".csv"; this.response.addHeader("Content-Type", "application/csv"); this.response.addHeader("Content-Disposition", "attachment; filename="+fileName); this.response.setCharacterEncoding("UTF-8"); } public <E> void processData(E record) { try { response.getWriter().write(record.toString()); //如果是要写入csv,需要重写toString,属性通过","分割 response.getWriter().write("\n"); }catch (IOException e){ e.printStackTrace(); } } }然后通过实现org.apache.ibatis.session.ResultHandler,自定义我们的ResultHandler,它用于获取java对象,然后传递给上面的DownloadProcessor处理类进行写文件操作:
public class CustomResultHandler implements ResultHandler { private final DownloadProcessor downloadProcessor; public CustomResultHandler( DownloadProcessor downloadProcessor) { super(); this.downloadProcessor = downloadProcessor; } @Override public void handleResult(ResultContext resultContext) { Authors authors = (Authors)resultContext.getResultObject(); downloadProcessor.processData(authors); } }实体类:
public class Authors { private Integer id; private String firstName; private String lastName; private String email; private Date birthdate; private Date added; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName == null ? null : firstName.trim(); } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName == null ? null : lastName.trim(); } public String getEmail() { return email; } public void setEmail(String email) { this.email = email == null ? null : email.trim(); } public Date getBirthdate() { return birthdate; } public void setBirthdate(Date birthdate) { this.birthdate = birthdate; } public Date getAdded() { return added; } public void setAdded(Date added) { this.added = added; } @Override public String toString() { return this.id + "," + this.firstName + "," + this.lastName + "," + this.email + "," + this.birthdate + "," + this.added; } }Mapper接口:
public interface AuthorsMapper { List<Authors> selectByExample(AuthorsExample example); List<Authors> streamByExample(AuthorsExample example); //以stream形式从mysql获取数据 }Mapper xml文件核心片段,以下两条select的唯一差异就是在stream获取数据的方式中多了一条属性: fetchSize="-2147483648"
<select id="selectByExample" parameterType="com.alphathur.mysqlstreamingexport.domain.AuthorsExample" resultMap="BaseResultMap"> select <if test="distinct"> distinct </if> 'false' as QUERYID, <include refid="Base_Column_List" /> from authors <if test="_parameter != null"> <include refid="Example_Where_Clause" /> </if> <if test="orderByClause != null"> order by ${orderByClause} </if> </select> <select id="streamByExample" fetchSize="-2147483648" parameterType="com.alphathur.mysqlstreamingexport.domain.AuthorsExample" resultMap="BaseResultMap"> select <if test="distinct"> distinct </if> 'false' as QUERYID, <include refid="Base_Column_List" /> from authors <if test="_parameter != null"> <include refid="Example_Where_Clause" /> </if> <if test="orderByClause != null"> order by ${orderByClause} </if> </select>获取数据的核心service如下,由于只做个简单演示,就懒得写成接口了。其中 streamDownload 方法即为stream取数据写文件的实现,它将以很低的内存占用从MySQL获取数据;此外还提供traditionDownload方法,它是一种传统的下载方式,批量获取全部数据,然后将每个对象写入文件。
@Service public class AuthorsService { private final SqlSessionTemplate sqlSessionTemplate; private final AuthorsMapper authorsMapper; public AuthorsService(SqlSessionTemplate sqlSessionTemplate, AuthorsMapper authorsMapper) { this.sqlSessionTemplate = sqlSessionTemplate; this.authorsMapper = authorsMapper; } /** * 堆代码 duidaima.com * stream读数据写文件方式 * @param httpServletResponse * @throws IOException */ public void streamDownload(HttpServletResponse httpServletResponse) throws IOException { AuthorsExample authorsExample = new AuthorsExample(); authorsExample.createCriteria(); HashMap<String, Object> param = new HashMap<>(); param.put("oredCriteria", authorsExample.getOredCriteria()); param.put("orderByClause", authorsExample.getOrderByClause()); CustomResultHandler customResultHandler = new CustomResultHandler(new DownloadProcessor (httpServletResponse)); sqlSessionTemplate.select( "com.alphathur.mysqlstreamingexport.mapper.AuthorsMapper.streamByExample", param, customResultHandler); httpServletResponse.getWriter().flush(); httpServletResponse.getWriter().close(); } /** * 传统下载方式 * @param httpServletResponse * @throws IOException */ public void traditionDownload(HttpServletResponse httpServletResponse) throws IOException { AuthorsExample authorsExample = new AuthorsExample(); authorsExample.createCriteria(); List<Authors> authors = authorsMapper.selectByExample (authorsExample); DownloadProcessor downloadProcessor = new DownloadProcessor (httpServletResponse); authors.forEach (downloadProcessor::processData); httpServletResponse.getWriter().flush(); httpServletResponse.getWriter().close(); } }下载的入口controller:
@RestController @RequestMapping("download") public class HelloController { private final AuthorsService authorsService; public HelloController(AuthorsService authorsService) { this.authorsService = authorsService; } @GetMapping("streamDownload") public void streamDownload(HttpServletResponse response) throws IOException { authorsService.streamDownload(response); } @GetMapping("traditionDownload") public void traditionDownload(HttpServletResponse response) throws IOException { authorsService.traditionDownload (response); } }实体类对应的表结构创建语句:
CREATE TABLE `authors` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `birthdate` date NOT NULL, `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10095 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;这里有个问题:如何短时间内创建大批量测试数据到MySQL呢?方式是使用存储过程 + 大杀器 select insert 语句!