数据库导出CSV格式，并压缩成ZIP的形式导出

因为Excel导出会导致内存溢出，所以以CSV格式导出。

以下实现的功能：从数据读取数，以CSV格式导出，并压缩成ZIP，让用户下载。下面是一次导出的文件是6万条，超过6万条的，自动生成第二个文件。这个是客户的要求，一次导出一个文件其实更方便。曾经尝试过，下载百万千万条数据生成CSV格式，是不会内存溢出的。

不足之处有两个地方，CSV格式下载到本地，用户提出，一定要可以自己选路径，那没办法，本来都已经下载到本地了，只好再压缩一遍让客户自己选了。

第二，本模块不是通用的。做了一个通用的模块，但是因为每次取多少个字段不确定，只是在while里加for循环来实现，所以效率几何倍数的递减。

实现之前要导入两三个jar文件：ant.jar，commons-compress-1.3.jar，opencsv-1.8.jar。

示例下载地址：

import java.io.File;import java.io.FileInputStream;import java.io.FileWriter;import java.io.IOException;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import javax.servlet.http.HttpServletResponse;import org.apache.tools.zip.ZipEntry;import org.apache.tools.zip.ZipOutputStream;import oracle.jdbc.OracleTypes;import au.com.bytecode.opencsv.CSVWriter;/** * @title  * @description Action * @copyright Copyright (c) 2012 * @author painarthur * @version 1.0 */public class Ncp_fhyDAO {public void exportCsv(HttpServletResponse response,int resultCountAll,String path,String csvExportIllustrate,int page, int pageSize,String p_cswjg_dm,String p_csssq,String p_cHymx_dm,String p_cHY_Level,String p_cPX_ZD,String p_cPX_SJ) throws DataAccessException {JDBCDataSource jDBCDataSource = JDBCDataSource.getJDBCDataSource();PreparedStatement preparedStatement = null;ResultSet result = null;Connection conn = null;File tempFile = null;CSVWriter writer = null;ZipOutputStream zos = null;FileInputStream fis = null;int t1 = (int) System.currentTimeMillis();try {conn = jDBCDataSource.getConnection();CallableStatement proc = conn.prepareCall("{call sjcq.BP_**_FHY_HS(?,?,?,?,?,?,?)}");proc.setString(1, p_csssq);proc.setString(2, p_cswjg_dm);proc.setString(3, p_cHymx_dm);proc.setString(4, p_cHY_Level);proc.setString(5, p_cPX_ZD);proc.setString(6, p_cPX_SJ);proc.registerOutParameter(7, OracleTypes.VARCHAR);proc.execute();String queryStr = proc.getString(7); //存储过程输出SQL语句preparedStatement = conn.prepareStatement(queryStr);result = preparedStatement.executeQuery(queryStr);String fileName = "农产品**查询";String fileExtension = ".csv";int bs = -1;//倍数int exportMax = 60000;  //一次导出6万条数据int temp = 1;String executeStr = "";String fullName = "";//FileWriter w = null;//if(resultCountAll/exportMax>=1){bs = resultCountAll/exportMax;//}while(bs>=0){String [] includeHeaders = {"行业代码","行业名称","户数"};  //定义导出CSV文件头文件if(resultCountAll>=(exportMax*temp)){  //一次导出6万条数据，多出的部分，生成第二个6万条数据文件executeStr = "SELECT *  FROM (select rownum myNum,TempAA.* FROM (" + queryStr + ")TempAA ) where myNum>="+ (temp-1)*exportMax + " and TempAA.myNum<=" + exportMax*temp;tempFile = new File(path+fileName+temp+fileExtension);fullName = path+fileName+temp+fileExtension;   //指定导出的文件目录}else if(resultCountAll%exportMax!=0){  //剩下的不足6万条的导出最后一个文件executeStr = "SELECT *  FROM (select rownum myNum,TempAA.* FROM (" + queryStr + ")TempAA ) where myNum>="+ (temp-1)*exportMax + " and myNum<=" + resultCountAll;fullName = path+fileName+temp+fileExtension;  }preparedStatement = conn.prepareStatement(executeStr);result = preparedStatement.executeQuery(executeStr);   writer = new CSVWriter(new FileWriter(fullName));   //new一个CSV文件writer.writeNext(includeHeaders);while (result.next()) {String[] data = new String[]{StringUtil.isoToGbk(result.getString("HY_DM")),//行业代码     StringUtil.isoToGbk(result.getString("HY_MC")),//行业名称     StringUtil.isoToGbk(result.getString("HS"))//户数};writer.writeNext(data);}writer.flush();writer.close();if (result != null) {result.close();result = null;}if (preparedStatement != null) {preparedStatement.close();preparedStatement = null;}bs-=1;temp+=1;}tempFile = null;               //如果不以压缩包的形式导出，下面的代码就完全没有必要要了。上面的部分，系统会以CSV格式导出到本地。                response.setContentType("application/zip");response.reset();response.setCharacterEncoding("GBK");response.setHeader("Content-Disposition","attachment; filename=" + new String(fileName.getBytes("GBK"),"ISO8859-1")+".zip" );//文件压缩int leng=0;zos = new ZipOutputStream(response.getOutputStream());   File delFile = null;for(int i = temp-1;i>0;i--){fis = new FileInputStream(path+fileName+ i + ".csv");ZipEntry z1 = new ZipEntry(fileName+i+".csv");zos.setComment(csvExportIllustrate);zos.putNextEntry(z1);byte[] b = new byte[1024];while((leng = fis.read(b))!=-1){zos.write(b,0,leng);}zos.setEncoding("GBK");fis.close();}zos.close();//文件删除for(int i = temp-1;i>0;i--){delFile = new File(path+fileName+ i + ".csv");delFile.delete();}} catch (Exception e) {throw new DataAccessException(e);} finally {// 释放资源try {if(zos!=null){try {zos.close();} catch (IOException e) {e.printStackTrace();}zos = null;}if(fis!=null){try {fis.close();} catch (IOException e) {e.printStackTrace();}fis = null;}if(writer!=null){try {writer.close();} catch (IOException e) {e.printStackTrace();}writer = null;}if (result != null) {result.close();result = null;}if (preparedStatement != null) {preparedStatement.close();preparedStatement = null;}} catch (SQLException e1) {throw new DataAccessException(e1);}}}}

以下是通用的，如果有更好的实现方法或功能，请提出建议。

import java.io.File;import java.io.FileInputStream;import java.io.FileWriter;import java.io.IOException;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.zip.ZipEntry;import java.util.zip.ZipOutputStream;import javax.servlet.http.HttpServletResponse;import oracle.jdbc.OracleTypes;import au.com.bytecode.opencsv.CSVWriter;public class ExportCsvToZip {private String zipSm = "超过6万条数据后，以CSV格式导出。";//导出zip文件说明/* * add by li_dyue * date: 20120324 * function:compose csv files and export zip *  *  csvFileName 暂时生成的csv文件名称 *  exportZipName 要导出的zip文件名称 *  includeHeaders csv文件的列表的第一列，例如：纳税人识别号、纳税人名称、税务机关等 *  dataPO 给csv文件赋值的PO们 *  querysql 需要查询的sql语句 *  exportMax 定义一次最多导出多少条csv文件 *  resultNumber 一共需要导出多少条记录 *   *  文件给生成到本地，查出一条就往文件里写一条， *  如果要把这些数据都放到内存中再压缩，那么在大数据量的时候，一定会发生内存溢出问题。 *   */public void exportZip(HttpServletResponse response, String csvFileName, String exportZipName,String[] includeHeaders, String[] dataPO, String querysql, int resultNumber)throws DataAccessException {JDBCDataSource jDBCDataSource = JDBCDataSource.getJDBCDataSource();List rs_list = new ArrayList();PageListData pageListData = new PageListData();PreparedStatement preparedStatement = null;ResultSet result = null;Connection conn = null;File tempFile = null;CSVWriter writer = null;ZipOutputStream zos = null;FileInputStream fis = null;//path: 先在服务器上生成csv文件的路径StringBuffer path = new StringBuffer();path.append("c:/lee/");String fileExtension = ".csv";String c = "";int countAll = 0;int bs = -1;//倍数int exportMax = 60000;int temp = 1;String executeStr = "";StringBuffer bf = new StringBuffer();int t1 = (int) System.currentTimeMillis();String[] data = new String[dataPO.length];try {conn = jDBCDataSource.getConnection();countAll = resultNumber;if(countAll/exportMax>=1){bs = countAll/exportMax;}while(bs>=0){if(countAll>=(exportMax*temp)){executeStr = "SELECT *  FROM (select rownum myNum,TempAA.* FROM (" + querysql + ")TempAA ) where myNum>="+ (temp-1)*exportMax + " and myNum<=" + exportMax*temp;preparedStatement = conn.prepareStatement(executeStr);result = preparedStatement.executeQuery(executeStr);tempFile = new File(path+csvFileName+temp+fileExtension);System.out.println("csvFileName:"+csvFileName+temp+fileExtension);writer = new CSVWriter(new FileWriter(tempFile));writer.writeNext(includeHeaders);while (result.next()) {//String[] data = new String[dataPO.length];                                                //!!!  此处的for循环，严重影响了导出的效率，暂无好的解决方案                                                for(int i=0;i<dataPO.length;i++){data[i] =  StringUtil.isoToGbk(result.getString(dataPO[i]));}writer.writeNext(data);}writer.close();if (result != null) {result.close();result = null;}if (preparedStatement != null) {preparedStatement.close();preparedStatement = null;}}else if(countAll%exportMax!=0){executeStr = "SELECT *  FROM (select rownum myNum,aa.* FROM (" + querysql + ")aa ) where myNum>="+ (temp-1)*exportMax + " and myNum<=" + countAll;preparedStatement = conn.prepareStatement(executeStr);result = preparedStatement.executeQuery(executeStr);tempFile = new File(path+csvFileName+temp+fileExtension);System.out.println("csvFileName:"+csvFileName+temp+fileExtension);writer = new CSVWriter(new FileWriter(tempFile));writer.writeNext(includeHeaders);while (result.next()) {for(int i=0;i<dataPO.length;i++){data[i] =  StringUtil.isoToGbk(result.getString(dataPO[i]));}writer.writeNext(data);}writer.close();if (result != null) {result.close();result = null;}if (preparedStatement != null) {preparedStatement.close();preparedStatement = null;}}bs-=1;temp+=1;}int t2 = (int) System.currentTimeMillis();System.out.println((t2-t1)/3600);File file = new File("c:/lee/c.zip");String outputFile = csvFileName+temp + ".csv";response.setContentType("application/zip");response.reset();exportZipName = "aa";response.setHeader("Content-Disposition","attachment; filename=\"" + "c.zip" + "\"" );response.setCharacterEncoding("UTF-8");int leng=0;zos = new ZipOutputStream(response.getOutputStream());   for(int i = temp-1;i>0;i--){//System.out.println("=:"+path+csvFileName+i+fileExtension);fis = new FileInputStream(path+csvFileName+i+fileExtension);ZipEntry z1 = new ZipEntry(csvFileName+temp+fileExtension);zos.putNextEntry(z1);byte[] b = new byte[1024];while((leng = fis.read(b))!=-1){zos.write(b,0,leng);}}zos.close();fis.close();} catch (Exception e) {throw new DataAccessException(e);} finally {// 释放资源try {if(response.getOutputStream() != null)response.getOutputStream().close();} catch (IOException e2) {e2.printStackTrace();}try {if(zos!=null){try {zos.close();} catch (IOException e) {e.printStackTrace();}zos = null;}if(fis!=null){try {fis.close();} catch (IOException e) {e.printStackTrace();}fis = null;}if(writer!=null){try {writer.close();} catch (IOException e) {e.printStackTrace();}writer = null;}if (result != null) {result.close();result = null;}if (preparedStatement != null) {preparedStatement.close();preparedStatement = null;}} catch (SQLException e1) {throw new DataAccessException(e1);}}}public void deleteCsvFiles(String path,String fileName){}//如果导出内容小于6000条，那么导出excel格式；如果导出内容大于6000条，那么导出cvs格式，然后再压缩。public boolean ExportToExcel(int resultCountAll){boolean exportExcel = false;if(resultCountAll<6000){exportExcel = true;}return exportExcel;}public String getZipSm() {return zipSm;}public void setZipSm(String zipSm) {this.zipSm = zipSm;}}