最近在做项目功能时 ，发现有20万以上的数据。要求导出时直接导出成压缩包。原来的逻辑是使用poi导出到excel，他是操作对象集合然后将结果写到excel中。
使用poi等导出时，没有考虑数据量的问题，大数据量无法满足，有个几千行jvm就哭了。更别提几万行几百万行数据了。
经过一天的研究发现一种不会消耗过多内存的方法：
导出成csv格式
大数据量的导出成csv格式分为以下几步：
1.首先引入需要的jar包 一下是我maven的配置方式
<dependency>
<groupId>org.mvel</groupId>
<artifactId>mvel2</artifactId>
<version>2.2.8.Final</version>
</dependency>
<dependency>
<groupId>net.sourceforge.javacsv</groupId>
<artifactId>javacsv</artifactId>
<version>2.0</version>
</dependency>
2.以下是具体的执行代码，我是用的是jdbcTemplate
public class DownloadVehicleRepair extends AbstractJob {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
protected void executeBusiness(Long aLong) {
System.out.println("开始执行！！！！！！！！！！");
final String fileName = "车辆维修清单.csv";//压缩包里面的文件
final String[] header = {"序号", "第三方机构代码", "机构名称", "分公司", "合作机构", "单位类别", "主品牌", "品牌名称",
"被投诉", "涉及欺诈", "黑名单", "审核状态", "维护时间", "维护人员代码"};
final String sql = "您需要执行sql”;
jdbcTemplate.execute(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement pstmt = connection.prepareStatement(sql);
return pstmt;
}
}, new PreparedStatementCallback<Integer>() {
@Override
public Integer doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
ResultSet rs = preparedStatement.executeQuery();
try {
CsvUtil.writeCsv(RuntimeEnvironmentUtil.getValue(SysConstent.code,SysConstent.path) + "\\VehicleRepairDetail.zip",
fileName, header, rs);//RuntimeEnvironmentUtil.getValue()是为了获取你导出到服务器的路径
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
});
System.out.println("导出完成！！！！！！！！！！！");
}
}
3.以下是帮助类
public class CsvUtil {
// 编码类型
public static final Charset CHARSET = Charset.forName("GBK");
// 分隔符
public static final char DELIMITER = ',';
// 文件后缀
public static final String SUFFIX = ".csv";
public static void writeCsv(OutputStream out, String[] header, ResultSet rs)
throws IOException, SQLException {
CsvWriter writer = null;
try {
writer = new CsvWriter(out, CsvUtil.DELIMITER, CsvUtil.CHARSET);
writeCsv(writer, header, rs);
} finally {
if (writer != null)
writer.close();
}
}
public static void writeCsv(CsvWriter writer, String[] header, ResultSet rs)
throws IOException, SQLException {
if (header != null)
writer.writeRecord(header);
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
while (rs.next()) {
for (int i = 1; i <= columnCount; i++)
writer.write(rs.getString(i));
writer.endRecord();
}
}
public static void writeCsv(File file, String[] header, ResultSet rs)
throws IOException, SQLException {
BufferedOutputStream out = null;
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(file);
out = new BufferedOutputStream(fileOutputStream);
writeCsv(out, header, rs);
} finally {
if (out != null) {
out.flush();
out.close();
}
if (fileOutputStream != null) {
fileOutputStream.close();
}
}
}
public static void writeCsv(String csvFilePath, String[] header,
ResultSet rs) throws IOException, SQLException {
writeCsv(new File(csvFilePath), header, rs);
}
public static void writeCsv(String zipFilePath, String csvName, String[] header, ResultSet rs)
throws IOException, SQLException {
FileOutputStream fos = null;
BufferedOutputStream bos = null;
ZipOutputStream zos = null;
try {
fos = new FileOutputStream(zipFilePath);
bos = new BufferedOutputStream(fos);
zos = new ZipOutputStream(bos);
zos.putNextEntry(new ZipEntry(csvName));
writeCsv(zos, header, rs);
} finally {
StreamUtil.flush(zos);
StreamUtil.close(zos);
//StreamUtil.flush(bos);
StreamUtil.close(bos);
//StreamUtil.flush(fos);
StreamUtil.close(fos);
}
}
}
public class StreamUtil {
public static void flush(Flushable flushable) {
if (flushable != null) {
try {
flushable.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static void close(Closeable closeable){
if(closeable!=null){
try {
closeable.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
4.下面是下载时的action
@RequestMapping(value = "/downloadVehicleRepair", method = RequestMethod.POST)
public ResponseEntity<byte[]> download() throws IOException {
String path = RuntimeEnvironmentUtil.getValue(SysConstent.code,SysConstent.path)+"\\VehicleRepairDetail.zip";
File file = new File(path);
HttpHeaders headers = new HttpHeaders();
String fileName = new String("车辆维修清单.zip".getBytes("UTF-8"), "iso-8859-1");//为了解决中文名称乱码问题
headers.setContentDispositionFormData("attachment", fileName);
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.OK);
}
总结：以上只是关键代码。使用时只需要稍加改变就可以运行。