上篇文章介绍了java导出文件格式篇xls,xlsx,csv
https://blog.csdn.net/weixin_56567361/article/details/126640185
本篇介绍下 大批量数据的导出思路和方法
导出数据慢的原因:
一次性查询太多数据 sql会很慢 太多数据导出处理很慢
这里我讲解下分批查询和分批导出
目录
分批查询方法
一: 根据数据量 分割每一部分数据
//查询总条数int count = testService.selectSize(test);//数据量分割值10万int num = 100000;//循环次数/分割次数int cycles = count / num;//余数int remainder = count % num;String sql = "";List> getDownloadList = new ArrayList<>();//分批查询次数for (int i = 0; i < cycles; i++) {//sql=select .... from test where .... order by create_time " + " limit " + (i * num) + "," + num;//limit前参数test.setFront(i * num);//limit后参数test.setAfter(num);ListtestList = testService.selectAll(test);getDownloadList.addAll(testList);}if (remainder > 0) {test.setFront(num * cycles);test.setAfter((num * cycles) + remainder);//sql=select .... from test where .... order by create_time " + " limit " + (num * cycles) + "," + ((num * cycles) + remainder);ListtestList = testService.selectAll(test);getDownloadList.addAll(testList);}//导出操作..... 二: 根据查询时间间隔 分割为每一天一个时间段
Long beginTime = ...;(毫米级)Long endTime = ...;List> getDownloadList = new ArrayList<>();//对查询时间进行分割 每天查一次 map中时间顺序 从小到大Map timeMap = new LinkedHashMap<>();if (endTime - beginTime > 86400) { //要查询分割的次数 int l = (int) ((endTime - beginTime) / 86400); for (int i = 1; i <= l; i++) { timeMap.put(DateUtils.longToString(beginTime + (i - 1) * 86400), DateUtils.longToString(beginTime + (i * 86400))); } //查询间隔不是整天数时 则补余下时间 if (beginTime + l * 86400 != endTime) { timeMap.put(DateUtils.longToString(beginTime + (l * 86400)), DateUtils.longToString(endTime)); }} else { //小于一天时处理 timeMap.put(DateUtils.longToString(beginTime), DateUtils.longToString(endTime));}//如果数据展示要倒序 需要将map中时间翻过来List times = new ArrayList<>(timeMap.keySet());Collections.reverse(times);for (String beginTimeStr : times) {String endTimeStr = timeMap.get(beginTimeStr);sql:.....ListtestList=......;getDownloadList.addAll(testList);}//导出操作.....public static String longToString(long value) {if (String.valueOf(value).length() == 10) { value = value * 1000;} Date time = new Date(value); return formatYMDHMS.format(time);} 分批查询+分批导出实例
controller层
if ((endTime - beginTime) / (3600 * 24) > 31) { throw new CustomException("一次最多请求31天数据,请分批导出"); } String sql = "select count(1) as count" + " from test where create_time between " + beginTime + " and " + endTime"; List> mapList = ClickHouseUtils.execSQL(sql); //根据数据量分割上传 也可根据时间 int count = Integer.parseInt(mapList.get(0).get("count").toString()); //数据量分割值20万 int num = 200000; //循环次数/分割次数 int cycles = count / num; //余数 int remainder = count % num; String path = getDefaultBaseDir(); String csvFile = "临时历史数据.csv"; String absolutePath = path + "/" + csvFile; //获取表头 String[] exportDataTitle = dataService.exportDataTitle(fields); File file = new File(path); //检查是否存在此文件夹 如没有则创建 if (!file.exists()) { if (file.mkdirs()) { logger.info("历史查询目录创建成功"); } else { logger.error("历史查询目录创建失败"); } } for (int i = 0; i < cycles; i++) { sql = "select .. from test where create_time between " + beginTime + " and " + endTime + " order by create_time limit " + (i * num) + "," + num; FileVO fileVO = dataService.fileVO(sql, ids, fields); PoiUtils.exportCSVFile(exportDataTitle, fileVO.getDownloadList(), i, absolutePath); } if (remainder > 0) { sql = "select .. from test where create_time between " + beginTime + " and " + endTime + " order by create_time limit " + (num * cycles) + "," + ((num * cycles) + remainder); FileVO fileVO = dataService.fileVO(sql, ids, fields); PoiUtils.exportCSVFile(exportDataTitle, fileVO.getDownloadList(), cycles, absolutePath); } //输出csv流文件，提供给浏览器下载 PoiUtils.outCsvStreamCSV(response, absolutePath); logger.info("历史查询下载目录: " + absolutePath); //删除临时文件 PoiUtils.deleteFile(new File(absolutePath)); logger.info("历史查询删除目录: " + absolutePath); PoiUtils层 public static void exportCSVFile(String[] title, List> downloadList, int i, String absolutePath) throws IOException { BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(absolutePath, true)); logger.info("创建文件地址: " + absolutePath); //如果是第一次循环 添加表头 if (i == 0) { PoiUtils.writeHead(title, bufferedWriter); //另起一行 bufferedWriter.newLine(); } //循环list中数据 逐个添加 for (List list : downloadList) { CSVFileUtil.writeRow(list, bufferedWriter); bufferedWriter.newLine(); } bufferedWriter.close(); } public static void writeHead(String[] title, BufferedWriter bufferedWriter) throws IOException { // 写表头 int i = 0; for (String data : title) { bufferedWriter.write(data); if (i != title.length - 1) { bufferedWriter.write(","); } i++; } } public static void outCsvStreamCSV(HttpServletResponse response, String absolutePath) throws IOException { java.io.OutputStream out = response.getOutputStream(); byte[] b = new byte[10240]; java.io.File fileLoad = new java.io.File(absolutePath); response.reset(); response.setContentType("application/csv"); response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode("export.csv", "UTF-8")); java.io.FileInputStream in = new java.io.FileInputStream(fileLoad); int n; //为了保证excel打开csv不出现中文乱码 out.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF}); while ((n = in.read(b)) != -1) { //每次写入out1024字节 out.write(b, 0, n); } in.close(); out.close(); } CSVFileUtils
@Slf4jpublic class CSVFileUtil { public static List importCsv(File file, String delimiter, String charsetName) { List dataList = new ArrayList<>(); BufferedReader br = null; try { InputStreamReader isr = new InputStreamReader(new FileInputStream(file), charsetName); br = new BufferedReader(isr); String line = ""; while ((line = br.readLine()) != null) { dataList.add(line.split(delimiter)); } } catch (Exception e) { } finally { if (br != null) { try { br.close(); br = null; } catch (IOException e) { e.printStackTrace(); } } } return dataList; } public static File exportCsv(List> exportData, String outPutPath, String fileName) { File csvFile = null; BufferedWriter csvFileOutputStream = null; try { File file = new File(outPutPath); if (!file.exists()) { if (file.mkdirs()) { log.info("创建成功"); } else { log.error("创建失败"); } } //定义文件名格式并创建 csvFile = File.createTempFile(fileName, ".csv", new File(outPutPath)); csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile, true), StandardCharsets.UTF_8), 1024); for (List exportDatum : exportData) { writeRow(exportDatum, csvFileOutputStream); csvFileOutputStream.newLine(); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (csvFileOutputStream != null) { csvFileOutputStream.close(); } } catch (IOException e) { e.printStackTrace(); } } return csvFile; } public static void writeRow(List row, BufferedWriter csvWriter) throws IOException { int i = 0; for (String data : row) { csvWriter.write(data); if (i != row.size() - 1) { csvWriter.write(","); } i++; } } public static String DelQuota(String str) { String result = str; String[] strQuota = {"~", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "`", ";", "'", ",", ".", "/", ":", "/,", "<", ">", "?"}; for (String s : strQuota) { if (result.contains(s)) { result = result.replace(s, ""); } } return result; } public static void main(String[] args) { exportCsv(); //importCsv(); } public static void importCsv() { List dataList = CSVFileUtil.importCsv(new File("F:/test_two.csv"), ",", "GB2312"); if (!dataList.isEmpty()) { for (String[] cells : dataList) { if (cells != null && cells.length > 0) { for (String cell : cells) { System.out.print(cell + " "); } System.out.println(); } } } } public static void exportCsv() { List> listList = new ArrayList<>(); List list1 = new ArrayList<>(); List list2 = new ArrayList<>(); List list3 = new ArrayList<>(); list1.add("编号"); list1.add("姓名"); list1.add("身高"); list1.add("电话"); list2.add("1"); list2.add("小明"); list2.add("180cm"); list2.add("1111111"); list3.add("2"); list3.add("小红"); list3.add("176cm"); list3.add("1111111"); listList.add(list1); listList.add(list2); listList.add(list3); CSVFileUtil.exportCsv(listList, "D://", "testFile"); }} 到这里分批查询+分批导出已经介绍完了
大家根据需求调整代码 根据源码多测试
最后有遇到什么问题可以留言告诉我哦 欢迎评论区讨论🤪
来源地址：https://blog.csdn.net/weixin_56567361/article/details/126647979
