海量数据导出解决内存溢出问题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();
    }

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