使用mybatis的流式查询进行大数据量的导出csv文件
前提:1.要进行百万级数据的导出2.导出格式为csv文件首先需要导入依赖<!-- https://mvnrepository.com/artifact/net.sourceforge.javacsv/javacsv --><dependency><groupId>net.sourceforge.javacsv</groupId><artifac
·
前提:
1.要进行百万级数据的导出
2.导出格式为csv文件
首先需要导入依赖
<!-- https://mvnrepository.com/artifact/net.sourceforge.javacsv/javacsv -->
<dependency>
<groupId>net.sourceforge.javacsv</groupId>
<artifactId>javacsv</artifactId>
<version>2.0</version>
</dependency>
Controller:
/**
* 信息导出
* @param request
* @param response
*/
@RequestMapping(value = "/downloadFile", method = RequestMethod.POST, produces ="application/x-www-form-urlencoded;charset=UTF-8")
//前端也可以为JSON,此处是前端问题
public void downloadFile( HttpServletResponse response, Vo vo) {
log.info("start download , info:{}", vo);
long start = System.currentTimeMillis();
File tempFile = Service.download(vo);
CsvUtil.outCsvStream(response, tempFile, ExportInfoConstant.PROPERTY_NAME); //CsvUtil是我自己写的封装类,仅供参考
CsvUtil.deleteFile(tempFile);
log.info("Download Over , Use Time:{}", System.currentTimeMillis() - start);
}
Service与Impl:
/**
* 导出Service
* @param
* @return
*/
File download(Vo vo);
@Override
public File downloadProperty(Vo vo) {
File tempFile = null;
try {
long start = System.currentTimeMillis();
tempFile = File.createTempFile("vehical", ".csv"); //创建临时文件
CsvWriter csvWriter = new CsvWriter(tempFile.getCanonicalPath(), ',', Charset.forName("UTF-8"));
csvWriter.writeRecord(ExportInfoConstant.PROPERTY_HEAD_INFO); //头文件,自定义的常量
log.info("导入header文件耗时:{}ms", System.currentTimeMillis() - start);
Mapper.download(vo, new ResultHandler<Entity>() { //Entity是需要输出的实体类
@Override
public void handleResult(ResultContext<? extends Entity> resultContext) {//mybatis流式查询
Entity o = resultContext.getResultObject();
try {
csvWriter.write(o.getGameItem());
csvWriter.write(o.getPropertyName());
csvWriter.write(o.getPropertyType());
//这里有多重导入方法,可以自己封装方法,也可以参考我写的封装方法
csvWriter.write(DateUtils.getStrFromDate(o.getCreateTime()));
csvWriter.write(DateUtils.getStrFromDate(o.getUpdateTime()));
} catch (Exception e) {
log.error("导出异常,异常信息:{}",e.getMessage());
}
}
});
} catch (IOException e) {
log.error("download GameProperty error:{}", e.getMessage());
}
return tempFile;
}
Mapper(mybatis流式查询):
@Select("<script>" +
...//省略
"</script>")
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = 1000)
@ResultType(Entity.class)
void download(@Param("Vo")Vo vo, ResultHandler<Entity> handler);
CsvUtil(自己写的封装方法):
@Slf4j
public class CsvUtil {
/**
* 通过流写入csv
*/
public static void outCsvStream(HttpServletResponse response, File tempFile, String fileName) {
OutputStream out = null;
FileInputStream in = null;
if (tempFile == null) {
log.error("file not exit");
return;
}
try {
out = response.getOutputStream();
byte[] b = new byte[10240];
File fileLoad = new File(tempFile.getCanonicalPath());
String dateTimeInfo = DateUtils.format(new Date());
response.reset();
response.setContentType("application/csv");
response.setHeader("content-disposition", "attachment; filename="+ URLEncoder.encode(fileName,"UTF-8")+"_"+dateTimeInfo +".csv");
in = new FileInputStream(fileLoad);
int n;
out.write(new byte[] {( byte ) 0xEF, ( byte ) 0xBB, ( byte ) 0xBF }); //防止字符乱码
while ((n = in.read(b)) != -1) {
//每次写入out10240字节
out.write(b, 0, n);
}
} catch (IOException e) {
log.error("导出{}文件异常,异常信息:{}", fileName, e.getMessage());
} finally {
if (null != in) {
try {
in.close();
} catch (IOException e) {
log.error("输入流关闭异常,异常信息:{}", e.getMessage());
}
if (null != out) {
try {
out.close();
} catch (IOException e) {
log.error("response输出流关闭异常,异常信息:{}", e.getMessage());
}
}
}
}
}
/**
* 删除单个文件
* @return 单个文件删除成功返回true,否则返回false
*/
public static boolean deleteFile(File file) {
if (file.exists() && file.isFile()) {
if (file.delete()) {
log.info("文件删除成功");
return true;
} else {
return false;
}
} else {
return false;
}
}
/**
* 通过反射机制将实体类的属性写入头文件
*
*/
public static void writeHeader(CsvWriter csvWriter, Object o) throws IOException {
Field[] fields = o.getClass().getDeclaredFields();
String[] headers = new String[fields.length];
for (int i = 0; i < fields.length; i++) {
fields[i].setAccessible(true);
headers[i] = fields[i].getName();
}
csvWriter.writeRecord(headers);
}
/**
* 通过反射机制将实体类的属性对应的值全部写入文件
*
*/
public static void writeBody(CsvWriter csvWriter, Object o) {
try {
Field[] fields = o.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
fields[i].setAccessible(true);
String getter = "get" + StringUtils.capitalize(fields[i].getName());
Method method = o.getClass().getMethod(getter, new Class[]{});
Object value = method.invoke(o, new Object[]{});
if (null == value) {
csvWriter.write(" ");
} else {csvWriter.write(value.toString());
}
}
csvWriter.endRecord();
} catch (Exception e) {
log.info("获取属性值失败!" + e, e);
}
}
}
更多推荐
所有评论(0)