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

工程下载地址：https://files.cnblogs.com/files/xiandedanteng/CsvDownloadOracle20191110-2.rar

画面：

核心代码：

控制器：

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;

@Controller
public 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);
            }
        }
    }
}

CSV生成器：

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();
        }
    }
}

Mapper.xml:

<?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>

这个方案是MYSQL同类方案的新番，差别在于用rownum去替代MySql中的limit函数

--END-- 2019年11月10日14:51:32