【SpringBoot/MVC】从Oracle下载百万条记录的CSV

package com.hy.csvdld.ctrl;import java.io.File;import java.io.FileInputStream;import java.net.URLDecoder;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.log4j.Logger;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.ui.Model;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RequestMapping;import com.hy.csvdld.service.EmpService;import com.hy.csvdld.util.CsvMaker;@Controllerpublic class WebCtrl { private static Logger log = Logger.getLogger(WebCtrl.class); @Autowired EmpService empService; @RequestMapping("/") public String index(Model model) { log.info("进入index页"); return "index.html"; } @RequestMapping("/downloadTen") public void downloadTen(HttpServletResponse res, HttpServletRequest req) throws Exception { log.info("Start downloadTen"); SimpleDateFormat dfs = new SimpleDateFormat("yyyyMMddHHmmss"); Date time = new Date(); String tStamp = dfs.format(time); String localFilename = tStamp+".csv"; String path=req.getSession().getServletContext().getRealPath("/"); String localFilepath = path+localFilename; log.info("准备生成的本地路径文件名="+localFilepath); res.setContentType("multipart/form-data"); res.setCharacterEncoding("UTF-8"); res.setContentType("text/html"); String userAgent = req.getHeader("User-Agent"); if (userAgent.contains("MSIE") || userAgent.contains("Trident")) { // IE Core localFilename = java.net.URLEncoder.encode(localFilename, "UTF-8"); } else { // Non-IE Core localFilename = new String((localFilename).getBytes("UTF-8"), "ISO-8859-1"); } res.setHeader("Content-Disposition", "attachment;fileName=" + localFilename); localFilepath = URLDecoder.decode(localFilepath, "UTF-8"); File file=new File(localFilepath); CsvMaker maker=new CsvMaker(); maker.makeTenCsv(file, empService); log.info("已经生成文件:"+localFilepath); FileInputStream instream = new FileInputStream(localFilepath); ServletOutputStream outstream = res.getOutputStream(); int b = 0; byte[] buffer = new byte[1024]; while ((b = instream.read(buffer)) != -1) { outstream.write(buffer, 0, b); } instream.close(); if (outstream != null) { outstream.flush(); outstream.close(); boolean isDeleted=file.delete(); if(isDeleted) { log.info("已经删除文件："+localFilepath); } } } @RequestMapping("/downloadMany/{count}") public void downloadMany(HttpServletResponse res, HttpServletRequest req,@PathVariable String count) throws Exception { log.info("Start downloadGeneratedCsvFile"); SimpleDateFormat dfs = new SimpleDateFormat("yyyyMMddHHmmss"); Date time = new Date(); String tStamp = dfs.format(time); String localFilename = tStamp+".csv"; String path=req.getSession().getServletContext().getRealPath("/"); String localFilepath = path+localFilename; log.info("准备生成的本地路径文件名="+localFilepath); res.setContentType("multipart/form-data"); res.setCharacterEncoding("UTF-8"); res.setContentType("text/html"); String userAgent = req.getHeader("User-Agent"); if (userAgent.contains("MSIE") || userAgent.contains("Trident")) { // IE Core localFilename = java.net.URLEncoder.encode(localFilename, "UTF-8"); } else { // Non-IE Core localFilename = new String((localFilename).getBytes("UTF-8"), "ISO-8859-1"); } res.setHeader("Content-Disposition", "attachment;fileName=" + localFilename); localFilepath = URLDecoder.decode(localFilepath, "UTF-8"); File file=new File(localFilepath); CsvMaker mk=new CsvMaker(); mk.makeManyCsv(file, empService, Integer.parseInt(count)); log.info("已经生成文件:"+localFilepath); FileInputStream instream = new FileInputStream(localFilepath); ServletOutputStream outstream = res.getOutputStream(); int b = 0; byte[] buffer = new byte[1024]; while ((b = instream.read(buffer)) != -1) { outstream.write(buffer, 0, b); } instream.close(); if (outstream != null) { outstream.flush(); outstream.close(); boolean isDeleted=file.delete(); if(isDeleted) { log.info("已经删除文件："+localFilepath); } } } @RequestMapping("/downloadPartial/{count}") public void downloadPartial(HttpServletResponse res, HttpServletRequest req,@PathVariable String count) throws Exception { log.info("Start downloadPartial"); SimpleDateFormat dfs = new SimpleDateFormat("yyyyMMddHHmmss"); Date time = new Date(); String tStamp = dfs.format(time); String localFilename = tStamp+".csv"; String path=req.getSession().getServletContext().getRealPath("/"); String localFilepath = path+localFilename; log.info("准备生成的本地路径文件名="+localFilepath); res.setContentType("multipart/form-data"); res.setCharacterEncoding("UTF-8"); res.setContentType("text/html"); String userAgent = req.getHeader("User-Agent"); if (userAgent.contains("MSIE") || userAgent.contains("Trident")) { // IE Core localFilename = java.net.URLEncoder.encode(localFilename, "UTF-8"); } else { // Non-IE Core localFilename = new String((localFilename).getBytes("UTF-8"), "ISO-8859-1"); } res.setHeader("Content-Disposition", "attachment;fileName=" + localFilename); localFilepath = URLDecoder.decode(localFilepath, "UTF-8"); File file=new File(localFilepath); CsvMaker mk=new CsvMaker(); mk.makePartialCsv(file, empService, Integer.parseInt(count)); log.info("已经生成文件:"+localFilepath); FileInputStream instream = new FileInputStream(localFilepath); ServletOutputStream outstream = res.getOutputStream(); int b = 0; byte[] buffer = new byte[1024]; while ((b = instream.read(buffer)) != -1) { outstream.write(buffer, 0, b); } instream.close(); if (outstream != null) { outstream.flush(); outstream.close(); boolean isDeleted=file.delete(); if(isDeleted) { log.info("已经删除文件："+localFilepath); } } }}

package com.hy.csvdld.util;import java.io.File;import java.io.FileWriter;import java.io.IOException;import java.util.List;import org.apache.log4j.Logger;import com.hy.csvdld.Entity.Emp;import com.hy.csvdld.service.EmpService;// 用于生成CSV文件public class CsvMaker { private static Logger log = Logger.getLogger(CsvMaker.class); public void makeTenCsv(File file, EmpService empService) { try { List<Emp> emps = empService.selectTenEmp(); FileWriter fileWriter = new FileWriter(file, true); int index = 0; for (Emp emp:emps) { index++; String info =""+index+","+ emp.asCsvLine()+ System.getProperty("line.separator"); fileWriter.write(info); } fileWriter.flush(); fileWriter.close(); } catch (IOException e) { e.printStackTrace(); } } public void makeManyCsv(File file, EmpService empService,int count) { try { List<Emp> emps = empService.selectMany(count); FileWriter fileWriter = new FileWriter(file, true); int index = 0; for (Emp emp:emps) { index++; String info =""+index+","+ emp.asCsvLine()+ System.getProperty("line.separator"); fileWriter.write(info); } fileWriter.flush(); fileWriter.close(); } catch (IOException e) { e.printStackTrace(); } } // 当count过大时，分批下载 public void makePartialCsv(File file, EmpService empService,int count) { try { int PartialSize=10000; int times=count/PartialSize; for(int i=0;i<times;i++){ log.info("第"+i+"批次处理"); FileWriter fileWriter = new FileWriter(file, true); List<Emp> emps = empService.selectPartial(i*PartialSize, (i+1)*PartialSize); int index = i*PartialSize; for (Emp emp:emps) { index++; String info =""+index+","+ emp.asCsvLine()+ System.getProperty("line.separator"); fileWriter.write(info); } fileWriter.flush(); fileWriter.close(); } } catch (IOException e) { e.printStackTrace(); } }}

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" ><mapper namespace="com.hy.csvdld.dao.EmpMapper"> <select id="selectTenEmp" resultType="com.hy.csvdld.Entity.Emp"> select id,name,age,createdtime as ctime from tb01 where rownum&lt;11 order by id </select> <select id="selectManyEmp" resultType="com.hy.csvdld.Entity.Emp"> select id,name,age,createdtime as ctime from tb01 where rownum&lt;#{count} order by id </select> <select id="selectPartialEmp" resultType="com.hy.csvdld.Entity.Emp"> select * from (select rownum no,id,name,age,createdtime as ctime from tb01 where rownum&lt;=#{max} order by id) tbTmp where no&gt;#{min} </select></mapper>