数据库导出CSV格式,并压缩成ZIP的形式导出
来源:互联网 发布:基金组合 知乎 编辑:程序博客网 时间:2024/06/05 14:27
因为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;
- }
- }
0 0
- 数据库导出CSV格式,并压缩成ZIP的形式导出
- 数据库导出CSV格式,并压缩成ZIP的形式导出
- 从数据库中导出csv数据,并压缩成zip下载
- android 将SQLite数据库的表格导出为csv格式,并解析csv文件
- JAVA,导出CSV,设最大行数限制,使用winzipaes压缩成带密码的zip文件
- 导出CSV格式的文件
- Linux筛选日志并导出csv格式
- php 导出成csv格式
- Postgresql数据库导入和导出CSV格式
- 导出Zip压缩Excel
- Android将数据库数据导出成excel格式的文本(CSV)
- 导出CSV格式
- 数据库文件导出csv格式
- 导出csv 格式
- DateGridView导出到csv格式的Excel
- HBase导出CSV格式数据的方法
- Grails导出csv格式的文件
- php导入导出CSV格式的数据
- C#--工作笔记(直方图整体UserControls)
- VS改大小写的快捷键
- SOA与ESB的关系
- java工具类 获取包下所有类
- Cocos2d-x教程 CocoStudio篇 (3) UICheckBox 复选框控件
- 数据库导出CSV格式,并压缩成ZIP的形式导出
- main函数
- BANK_BASE(i)的理解 phys_addr_t
- LIBUSB 介绍
- C#使用GDAL计算某点的高程值
- explicit关键字的作用
- 持续集成 最佳实践 研讨会(1月25日 广州)
- hadoop配置过程中出错的地方
- Makefile详解和实例