海量数据导出解决内存溢出问题csv txt格式

/** *  * @param path * @param sql  查询数据的sql语句 * @param colTitleList 表头 * @param connName  查询的数据库 * @param tempPath  临时存放目录  要正确 * @param emax * @param response * @param fileName 文件名 * @param maxOneFile  每页存放的数据量-- 总数据超过最大存放数据量则生成压缩包   * @param fileType 文件类型 text csv * @return * @throws IOException */ public  int writeBigData(String path,String sql,ArrayList colTitleList,String connName,String tempPath,int emax,HttpServletResponse response,String fileName, long maxOneFile,String fileType) throws IOException {     Connection conn = null;     PreparedStatement stmt = null; ResultSet rs = null; String fileNameDecode =  URLDecoder.decode(fileName, "utf-8");//文件名编码 boolean b = true;//数量是否超出设定值 boolean c = false;//数量是否超过50w List<List<String>> resultList = new ArrayList<List<String>>(); StringBuffer titleStr = new StringBuffer();//表头   for(int i=0;i<colTitleList.size();i++){ String outValue = String.valueOf(colTitleList.get(i)); titleStr.append(outValue); if (i != colTitleList.size() - 1) titleStr.append(","); }   titleStr.append("\r\n");   ZipOutputStream zipTempFile = null;//要引用Apache下面的包 才有shezhiorg.apache.tools.zip.ZipOutputStream; try { conn = DAPDBManager.getConnection(DAOConstants.BEAN_SHARECPMP_DATA_SOURCE); stmt = conn.prepareStatement(sql); rs = stmt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData();//获取rs对象列的编号，类型和属性 int columnCount = rsmd.getColumnCount();//获取列数 不是行数 int filesize = 0;//文件行数 long hasInputZipCnt = 0; int fileSeq = 1;//zip文件个数 while (rs.next()) { filesize++; List<String> rowList = new ArrayList<String>(); for (int i = 1; i <= columnCount; i++) {//把每列的值遍历出来 if(rs.getMetaData().getColumnType(i) == Types.DECIMAL){//判断是不是数字类型的  取法不同 rowList.add(String.valueOf(rs.getDouble(i))); }else if (rs.getString(i) != null) rowList.add(rs.getString(i)); else rowList.add(""); } resultList.add(rowList); //如果超过最大数量，就把b置为false，并且创建文件，把list的内容写入文件 System.out.println("下载行数"+rs.getRow()); if(b && rs.getRow() > emax) { b = false; File file = new File(tempPath + "."+fileType);//先创建一个文件 if(file.exists()){//如果临时文件存在就删除 file.delete(); }        if(!file.createNewFile()){         return 0;        }        appendContent(tempPath + "."+fileType,resultList,titleStr.toString(),fileType);//内容写入 resultList.clear(); } else { if(!b){// 进来    ---rs.getRow() > emax if(filesize < maxOneFile && resultList.size()>1000){//超过1000就写入数据 appendContent(tempPath + "."+fileType,resultList,null,fileType); resultList.clear(); } if(filesize >= maxOneFile && !c) { //先把List清空 c = true;//数量是否超过50w appendContent(tempPath + "."+fileType,resultList,null,fileType); resultList.clear(); zipTempFile = new ZipOutputStream(new FileOutputStream(tempPath + ".zip")); zipTempFile.setEncoding("gbk");//要引用Apache下面的包 才有shezhiorg.apache.tools.zip.ZipOutputStream; zipTempFile.putNextEntry(new ZipEntry(fileNameDecode + "_" + fileSeq + "."+fileType)); FileInputStream fis = new FileInputStream(tempPath + "."+fileType); int len; byte[] buffer = new byte[40960]; // 读入需要下载的文件的内容，打包到zip文件 while ((len = fis.read(buffer)) > 0) { zipTempFile.write(buffer, 0, len); } zipTempFile.closeEntry(); fis.close(); hasInputZipCnt = 0; fileSeq++; } else if(c) { hasInputZipCnt++; if(hasInputZipCnt == maxOneFile) { appendContentZip(zipTempFile, resultList, null,fileType); zipTempFile.closeEntry(); resultList.clear(); hasInputZipCnt = 0; fileSeq++; } else { if(hasInputZipCnt == 1) { zipTempFile.setEncoding("gbk");//要引用Apache下面的包 才有shezhiorg.apache.tools.zip.ZipOutputStream; zipTempFile.putNextEntry(new ZipEntry(fileNameDecode + "_" + fileSeq + "."+fileType)); appendContentZip(zipTempFile, null, titleStr.toString(),fileType); } if(resultList.size() > 1000) { appendContentZip(zipTempFile, resultList, null,fileType); resultList.clear(); } } } } } } //****************************************************************** if(!b){ if(c) {//没有超出设定值  但超过50w appendContentZip(zipTempFile, resultList, null,fileType); zipTempFile.closeEntry(); zipTempFile.flush(); zipTempFile.close(); new File(tempPath + ".zip").renameTo(new File(path + ".zip")); downFile(path + ".zip",fileName+".zip", response); } else { appendContent(tempPath + "."+fileType,resultList,null,fileType); //重命名 new File(tempPath + "."+fileType).renameTo(new File(path + "."+fileType)); downFile(path + "."+fileType,fileNameDecode+"."+fileType, response); } return 1; }else{//超出设定值 b=true response.setContentType("application/octet-stream;charset=GBK"); response.setHeader("Content-disposition", "attachment;filename ="+fileName+"."+fileType); if(!"xls".equals(fileType)){ writeCSV(response.getOutputStream(),colTitleList,resultList); }else{    this.writeExcel(response,colTitleList,resultList,fileName);//response.getOutputStream() } return 2; } } catch (Exception ex) { ex.printStackTrace(); return 0; } finally { DBQueryUtil.close(rs); DBQueryUtil.close(stmt); DBQueryUtil.close(conn); //删除temp文件 File file = new File(tempPath + "."+fileType); if(file.exists()){ file.delete(); } }

   }

/**        * 追加文件：使用RandomAccessFile        *         * @param fileName 文件名        * @param content 追加的内容        * @param fileTitle 文件表头     * @param resultData 文件内容 * @throws IOException      */        public static void appendContent(String fileName, List resultData,String fileTitle,String fileType) throws IOException {   StringBuffer fileContent = new StringBuffer(); String separate =  "";//文件分割  csv用引号分割  txt 用逗号分割txt 可以换 String firstSep = "";//开头分割   txt为空  csv双引号 if("txt".equals(fileType)){ separate = ""; }else{ separate = "\""; firstSep = "\""; }   if(!DAPUtil.isNull(fileTitle)){//内容写入   fileContent = new StringBuffer(fileTitle);//表头写入   }       for(int i=0;i<resultData.size();i++) {         ArrayList rowData = (ArrayList)resultData.get(i);         for(int j=0;j<rowData.size();j++) {         String value = (String) rowData.get(j);             fileContent.append(firstSep+value.replace("\"", "\"\"")+separate);                if (j != rowData.size() - 1)                 fileContent.append(",");            }            fileContent.append("\r\n");         }   BufferedWriter out = null;              try {                  out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(fileName, true)));                  out.write(fileContent.toString());              } catch (Exception e) {                  e.printStackTrace();              } finally {                  try {                  if(out != null){               out.close();                  }              } catch (IOException e) {                  e.printStackTrace();                 }             }    } 

    public static void appendContentZip(ZipOutputStream zipFileStream, List resultData,String ct,String fileType) {   StringBuffer sbr = new StringBuffer();   String separate =  "";//文件分割  csv用引号分割  txt 用逗号分割txt 可以换   String firstSep = "";//开头分割   txt为空  csv双引号   if("txt".equals(fileType)){   separate = "";   }else{   separate = "\"";   firstSep = "\"";   }   if(ct==null){   for(int i=0;i<resultData.size();i++) {   ArrayList rowData = (ArrayList)resultData.get(i);           for(int j=0;j<rowData.size();j++) {           String value = (String) rowData.get(j);               sbr.append(firstSep+value.replace("\"", "\"\"")+separate);               if (j != rowData.size() - 1)               sbr.append(",");           }           sbr.append("\r\n");   }   }else{   sbr = new StringBuffer(ct);   }            try {     if(!DAPUtil.isNull(sbr))     zipFileStream.write(sbr.toString().getBytes());            } catch (Exception e) {            e.printStackTrace();             } finally {             }         }

/** * 下载文件 * @param realPath * @param request * @param response * @throws Exception */ public static void downFile(String realPath,String fileName,HttpServletResponse response) throws Exception { BufferedInputStream bis = null; BufferedOutputStream bos = null; InputStream fis = null; OutputStream fos = null; try { File uploadFile = new File(realPath); fis = new FileInputStream(uploadFile); bis = new BufferedInputStream(fis); response.setContentType("application/octet-stream;charset=GBK"); response.setHeader("Content-disposition", "attachment;filename ="+fileName); response.resetBuffer(); fos = response.getOutputStream(); bos = new BufferedOutputStream(fos); int bytesRead = 0; byte[] buffer = new byte[4096]; while((bytesRead = bis.read(buffer, 0, 4096)) != -1) { bos.write(buffer, 0, bytesRead); } bos.flush(); } catch (FileNotFoundException e) { PrintWriter pw = response.getWriter(); pw.print("<script>alert('文件不存在!');window.location.href=document.referrer;</script>"); pw.close(); } catch (Exception e) { e.printStackTrace(); } finally { if (bis != null)     bis.close(); if (fis != null)    fis.close(); if (bos != null)    bos.close(); if (fos != null)    fos.close(); } }

    /**      * 导出成CSV  TXT      * resultData中每个是一行数据。      * @param out      * @param colTitleList 标题      * @param resultData 数据。      * @throws Exception      */     public static void writeCSV(OutputStream out,List colTitleList,List resultData) throws Exception  {     if (resultData == null)        resultData = new ArrayList();     PrintWriter pw = null;     if (EDAUtil.isWeblogic())     pw = new PrintWriter(new OutputStreamWriter(new BufferedOutputStream(out,1024),"GBK"));     else      pw = new PrintWriter(new BufferedOutputStream(out,1024));     if (colTitleList != null){ for(int i=0;i<colTitleList.size();i++){ String outValue = String.valueOf(colTitleList.get(i)); pw.print(outValue); if (i != colTitleList.size() - 1) pw.print(","); } pw.println();     }     for(int i=0;i<resultData.size();i++) {        ArrayList rowData = (ArrayList)resultData.get(i);               for(int j=0;j<rowData.size();j++) {              String value = (String) rowData.get(j);              pw.print("\""+value.replace("\"", "\"\"")+"\"");                   if (j != rowData.size() - 1)                      pw.print(",");               }            pw.println();         } pw.close();     }

    /**      * 导出成Excel      * resultData中每个是一行数据。      * @param out      * @param colTitleList 标题      * @param resultData 数据。      * @throws Exception      */     public void writeExcel(HttpServletResponse response,List colTitleList,List resultData,String fileName) throws Exception  {     OutputStream ouputStream = null;    HSSFWorkbook wb = new HSSFWorkbook();     int pageSize = 1;     HSSFSheet sheet = wb.createSheet("sheet"+pageSize);     HSSFCellStyle style = wb.createCellStyle();      HSSFRow row = sheet.createRow(0);     if (resultData == null)        resultData = new ArrayList();     if (colTitleList != null){ for(int i=0;i<colTitleList.size();i++){ String outValue = String.valueOf(colTitleList.get(i)); HSSFCell cell = row.createCell(i); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(style); cell.setCellValue(outValue); }     }     for(int i=0;i<resultData.size();i++) {        ArrayList rowData = (ArrayList)resultData.get(i);        row = sheet.createRow(i+1);               for(int j=0;j<rowData.size();j++) {              String value = (String) rowData.get(j);            row.createCell(j).setCellValue(value);               }         }    ouputStream = response.getOutputStream(); response.setContentType("application/vnd.ms-excel");   response.setHeader("Content-disposition", "attachment;filename = "+fileName+".xls"); wb.write(ouputStream); ouputStream.flush();   ouputStream.close();     }

虽然 代码有点多 但跑起来很快 一百多万的数据一两分钟就能导完