[大数据]使用mybatis的流式查询进行大数据量的导出csv文件

前提：
1.要进行百万级数据的导出
2.导出格式为csv文件

首先需要导入依赖

        <!-- https://mvnrepository.com/artifact/net.sourceforge.javacsv/javacsv -->
        <dependency>
            <groupId>net.sourceforge.javacsv</groupId>
            <artifactId>javacsv</artifactId>
            <version>2.0</version>
        </dependency>

Controller：

    /**
     *  信息导出
     * @param request
     * @param response
     */
    @RequestMapping(value = "/downloadFile", method = RequestMethod.POST, produces ="application/x-www-form-urlencoded;charset=UTF-8") 
    //前端也可以为JSON，此处是前端问题
    public void downloadFile( HttpServletResponse response, Vo vo)  {
        log.info("start download , info:{}", vo);
        long start = System.currentTimeMillis();
        File tempFile = Service.download(vo);
        CsvUtil.outCsvStream(response, tempFile, ExportInfoConstant.PROPERTY_NAME);  //CsvUtil是我自己写的封装类，仅供参考
        CsvUtil.deleteFile(tempFile);
        log.info("Download Over , Use Time:{}", System.currentTimeMillis() - start);
    }

Service与Impl：

    /**
     * 导出Service
     * @param  
     * @return
     */
    File download(Vo vo);


 @Override
    public File downloadProperty(Vo vo) {
        File tempFile = null;
        try {
            long start = System.currentTimeMillis();
            tempFile = File.createTempFile("vehical", ".csv");  //创建临时文件
            CsvWriter csvWriter = new CsvWriter(tempFile.getCanonicalPath(), ',', Charset.forName("UTF-8"));
            csvWriter.writeRecord(ExportInfoConstant.PROPERTY_HEAD_INFO); //头文件，自定义的常量
            log.info("导入header文件耗时：{}ms", System.currentTimeMillis() - start);
            Mapper.download(vo, new ResultHandler<Entity>() { //Entity是需要输出的实体类
                @Override
                public void handleResult(ResultContext<? extends Entity> resultContext) {//mybatis流式查询
                    Entity o = resultContext.getResultObject();
                    try {
                        csvWriter.write(o.getGameItem());
                        csvWriter.write(o.getPropertyName());
                        csvWriter.write(o.getPropertyType());
                //这里有多重导入方法，可以自己封装方法，也可以参考我写的封装方法
                        csvWriter.write(DateUtils.getStrFromDate(o.getCreateTime()));
                        csvWriter.write(DateUtils.getStrFromDate(o.getUpdateTime()));
                    } catch (Exception e) {
                        log.error("导出异常，异常信息:{}",e.getMessage());
                    }
                }
            });
        } catch (IOException e) {
            log.error("download GameProperty error:{}", e.getMessage());
        }
        return tempFile;
    }

Mapper（mybatis流式查询）:

@Select("<script>" +
           ...//省略
            "</script>")
    @Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
    @ResultType(Entity.class)
    void download(@Param("Vo")Vo vo, ResultHandler<Entity> handler);

CsvUtil（自己写的封装方法）：

@Slf4j
public class CsvUtil {

    /**
     * 通过流写入csv
     */
    public static void outCsvStream(HttpServletResponse response, File tempFile, String fileName)  {
        OutputStream out = null;
        FileInputStream in = null;
        if (tempFile == null) {
            log.error("file not exit");
            return;
        }
        try {
            out = response.getOutputStream();
            byte[] b = new byte[10240];
            File fileLoad = new File(tempFile.getCanonicalPath());
            String dateTimeInfo = DateUtils.format(new Date());
            response.reset();
            response.setContentType("application/csv");
            response.setHeader("content-disposition", "attachment; filename="+ URLEncoder.encode(fileName,"UTF-8")+"_"+dateTimeInfo +".csv");
            in = new FileInputStream(fileLoad);
            int n;
            out.write(new byte[] {( byte ) 0xEF, ( byte ) 0xBB, ( byte ) 0xBF }); //防止字符乱码
            while ((n = in.read(b)) != -1) {
                //每次写入out10240字节
                out.write(b, 0, n);
            }
        } catch (IOException e) {
            log.error("导出{}文件异常，异常信息：{}",  fileName, e.getMessage());
        } finally {
            if (null != in) {
                try {
                    in.close();
                } catch (IOException e) {
                    log.error("输入流关闭异常，异常信息：{}", e.getMessage());
                }
            if (null != out) {
                try {
                    out.close();
                } catch (IOException e) {
                    log.error("response输出流关闭异常,异常信息:{}", e.getMessage());
                }
            }
            }
        }
    }

    /**
     * 删除单个文件
     * @return 单个文件删除成功返回true，否则返回false
     */
    public static boolean deleteFile(File file) {
        if (file.exists() && file.isFile()) {
            if (file.delete()) {
                log.info("文件删除成功");
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

/**
* 通过反射机制将实体类的属性写入头文件
*
*/
    public static void writeHeader(CsvWriter csvWriter, Object o) throws IOException {
        Field[] fields = o.getClass().getDeclaredFields();
        String[] headers = new String[fields.length];
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            headers[i] =  fields[i].getName();
        }
        csvWriter.writeRecord(headers);
    }

/**
* 通过反射机制将实体类的属性对应的值全部写入文件
*
*/
    public static void writeBody(CsvWriter csvWriter, Object o) {
        try {
            Field[] fields = o.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                String getter = "get" + StringUtils.capitalize(fields[i].getName());
                Method method = o.getClass().getMethod(getter, new Class[]{});
                Object value = method.invoke(o, new Object[]{});
                if (null == value) {
                    csvWriter.write(" ");
                } else {csvWriter.write(value.toString());
                }
            }
            csvWriter.endRecord();
        } catch (Exception e) {
            log.info("获取属性值失败！" + e, e);
        }
    }

}