Java导出大批量数据(分批查询导出篇)

本篇介绍下 大批量数据的导出思路和方法

导出数据慢的原因:

一次性查询太多数据 sql会很慢 太多数据导出处理很慢

这里我讲解下分批查询和分批导出

目录

分批查询方法

一: 根据数据量 分割每一部分数据

二: 根据查询时间间隔 分割为每一天一个时间段

分批查询 分批导出实例

分批查询方法

一: 根据数据量 分割每一部分数据

1.  
   //查询总条数
2.  
   int count = testService.selectSize(test);
3.  
   //数据量分割值10万
4.  
   int num = 100000;
5.  
   //循环次数/分割次数
6.  
   int cycles = count / num;
7.  
   //余数
8.  
   int remainder = count % num;
9.  
   String sql = "";
10.  
    List<List<Test>> getDownloadList = new ArrayList<>();
11.  
    //分批查询次数
12.  
    for (int i = 0; i < cycles; i ) {
13.  
    //sql=select .... from test where .... order by create_time " " limit " (i * num) "," num;
14.  
    //limit前参数
15.  
    test.setFront(i * num);
16.  
    //limit后参数
17.  
    test.setAfter(num);
18.  
    List<Test>testList = testService.selectAll(test);
19.  
    getDownloadList.addAll(testList);
20.  
    }
21.  
    if (remainder > 0) {
22.  
    test.setFront(num * cycles);
23.  
    test.setAfter((num * cycles) remainder);
24.  
    //sql=select .... from test where .... order by create_time " " limit " (num * cycles) "," ((num * cycles) remainder);
25.  
    List<Test>testList = testService.selectAll(test);
26.  
    getDownloadList.addAll(testList);
27.  
    }
28.  
    //导出操作
29.  
    .....

二: 根据查询时间间隔 分割为每一天一个时间段

1.  
   Long beginTime = ...;(毫米级)
2.  
   Long endTime = ...;
3.  
   List<List<Test>> getDownloadList = new ArrayList<>();
4.  
   //对查询时间进行分割 每天查一次 map中时间顺序 从小到大
5.  
   Map<String, String> timeMap = new LinkedHashMap<>();
6.  
   if (endTime - beginTime > 86400) {
7.  
   //要查询分割的次数
8.  
   int l = (int) ((endTime - beginTime) / 86400);
9.  
   for (int i = 1; i <= l; i ) {
10.  
    timeMap.put(DateUtils.longToString(beginTime (i - 1) * 86400), DateUtils.longToString(beginTime (i * 86400)));
11.  
    }
12.  
    //查询间隔不是整天数时 则补余下时间
13.  
    if (beginTime l * 86400 != endTime) {
14.  
    timeMap.put(DateUtils.longToString(beginTime (l * 86400)), DateUtils.longToString(endTime));
15.  
    }
16.  
    } else {
17.  
    //小于一天时处理
18.  
    timeMap.put(DateUtils.longToString(beginTime), DateUtils.longToString(endTime));
19.  
    }
20.  
    //如果数据展示要倒序 需要将map中时间翻过来
21.  
    List<String> times = new ArrayList<>(timeMap.keySet());
22.  
    Collections.reverse(times);
23.  
    for (String beginTimeStr : times) {
24.  
    String endTimeStr = timeMap.get(beginTimeStr);
25.  
    sql:.....
26.  
    List<Test>testList=......;
27.  
    getDownloadList.addAll(testList);
28.  
    }
29.  
    //导出操作
30.  
    .....
31.  
     
32.  
     
33.  
    /**
34.  
    * long类型时间戳(秒级)转String 1654012800 --> 2022-06-01 00:00:00
35.  
    *
36.  
    * @param value
37.  
    * @return
38.  
    */
39.  
    public static String longToString(long value) {
40.  
    if (String.valueOf(value).length() == 10) {
41.  
    value = value * 1000;
42.  
    }
43.  
    Date time = new Date(value);
44.  
    return formatYMDHMS.format(time);
45.  
    }

分批查询 分批导出实例

controller层

1.  
    
2.  
   if ((endTime - beginTime) / (3600 * 24) > 31) {
3.  
   throw new CustomException("一次最多请求31天数据,请分批导出");
4.  
   }
5.  
   String sql = "select count(1) as count" " from test where create_time between " beginTime " and " endTime";
6.  
   List<Map<String, Object>> mapList = ClickHouseUtils.execSQL(sql);
7.  
   //根据数据量分割上传 也可根据时间
8.  
   int count = Integer.parseInt(mapList.get(0).get("count").toString());
9.  
   //数据量分割值20万
10.  
    int num = 200000;
11.  
    //循环次数/分割次数
12.  
    int cycles = count / num;
13.  
    //余数
14.  
    int remainder = count % num;
15.  
    String path = getDefaultBaseDir();
16.  
    String csvFile = "临时历史数据.csv";
17.  
    String absolutePath = path "/" csvFile;
18.  
    //获取表头
19.  
    String[] exportDataTitle = dataService.exportDataTitle(fields);
20.  
    File file = new File(path);
21.  
    //检查是否存在此文件夹 如没有则创建
22.  
    if (!file.exists()) {
23.  
    if (file.mkdirs()) {
24.  
    logger.info("历史查询目录创建成功");
25.  
    } else {
26.  
    logger.error("历史查询目录创建失败");
27.  
    }
28.  
    }
29.  
    for (int i = 0; i < cycles; i ) {
30.  
    sql = "select .. from test where create_time between " beginTime " and " endTime " order by create_time limit " (i * num) "," num;
31.  
    FileVO fileVO = dataService.fileVO(sql, ids, fields);
32.  
    PoiUtils.exportCSVFile(exportDataTitle, fileVO.getDownloadList(), i, absolutePath);
33.  
    }
34.  
    if (remainder > 0) {
35.  
    sql = "select .. from test where create_time between " beginTime " and " endTime " order by create_time limit " (num * cycles) "," ((num * cycles) remainder);
36.  
    FileVO fileVO = dataService.fileVO(sql, ids, fields);
37.  
    PoiUtils.exportCSVFile(exportDataTitle, fileVO.getDownloadList(), cycles, absolutePath);
38.  
    }
39.  
    //输出csv流文件，提供给浏览器下载
40.  
    PoiUtils.outCsvStreamCSV(response, absolutePath);
41.  
    logger.info("历史查询下载目录: " absolutePath);
42.  
    //删除临时文件
43.  
    PoiUtils.deleteFile(new File(absolutePath));
44.  
    logger.info("历史查询删除目录: " absolutePath);
45.  
     

PoiUtils层

1.  
   /**
2.  
   * 上传csv文件到服务器
3.  
   *
4.  
   * @param title
5.  
   * @param downloadList
6.  
   * @param i
7.  
   * @param absolutePath
8.  
   * @throws IOException
9.  
   */
10.  
    public static void exportCSVFile(String[] title, List<List<String>> downloadList, int i, String absolutePath) throws IOException {
11.  
    BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(absolutePath, true));
12.  
    logger.info("创建文件地址: " absolutePath);
13.  
    //如果是第一次循环 添加表头
14.  
    if (i == 0) {
15.  
    PoiUtils.writeHead(title, bufferedWriter);
16.  
    //另起一行
17.  
    bufferedWriter.newLine();
18.  
    }
19.  
    //循环list中数据 逐个添加
20.  
    for (List<String> list : downloadList) {
21.  
    CSVFileUtil.writeRow(list, bufferedWriter);
22.  
    bufferedWriter.newLine();
23.  
    }
24.  
    bufferedWriter.close();
25.  
    }
26.  
     
27.  
     
28.  
    /**
29.  
    * csv文件表头写入
30.  
    *
31.  
    * @param title
32.  
    * @param bufferedWriter
33.  
    * @throws IOException
34.  
    */
35.  
    public static void writeHead(String[] title, BufferedWriter bufferedWriter) throws IOException {
36.  
    // 写表头
37.  
    int i = 0;
38.  
    for (String data : title) {
39.  
    bufferedWriter.write(data);
40.  
    if (i != title.length - 1) {
41.  
    bufferedWriter.write(",");
42.  
    }
43.  
    i ;
44.  
    }
45.  
    }
46.  
     
47.  
    /**
48.  
    * 分割csv文件传浏览器
49.  
    *
50.  
    * @param response
51.  
    * @param absolutePath
52.  
    * @throws IOException
53.  
    */
54.  
    public static void outCsvStreamCSV(HttpServletResponse response, String absolutePath) throws IOException {
55.  
    java.io.OutputStream out = response.getOutputStream();
56.  
    byte[] b = new byte[10240];
57.  
    java.io.File fileLoad = new java.io.File(absolutePath);
58.  
    response.reset();
59.  
    response.setContentType("application/csv");
60.  
    response.setHeader("content-disposition", "attachment; filename=" URLEncoder.encode("export.csv", "UTF-8"));
61.  
    java.io.FileInputStream in = new java.io.FileInputStream(fileLoad);
62.  
    int n;
63.  
    //为了保证excel打开csv不出现中文乱码
64.  
    out.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
65.  
    while ((n = in.read(b)) != -1) {
66.  
    //每次写入out1024字节
67.  
    out.write(b, 0, n);
68.  
    }
69.  
    in.close();
70.  
    out.close();
71.  
    }

CSVFileUtils

1.  
   /**
2.  
   * csv导出工具类
3.  
   */
4.  
   @Slf4j
5.  
   public class CSVFileUtil {
6.  
   /**
7.  
   * 读取
8.  
   *
9.  
   * @param file csv文件(路径 文件)
10.  
    * @param delimiter 分割符
11.  
    * @return
12.  
    */
13.  
    public static List<String[]> importCsv(File file, String delimiter, String charsetName) {
14.  
    List<String[]> dataList = new ArrayList<>();
15.  
    BufferedReader br = null;
16.  
    try {
17.  
    InputStreamReader isr = new InputStreamReader(new FileInputStream(file), charsetName);
18.  
    br = new BufferedReader(isr);
19.  
    String line = "";
20.  
    while ((line = br.readLine()) != null) {
21.  
    dataList.add(line.split(delimiter));
22.  
    }
23.  
    } catch (Exception e) {
24.  
    } finally {
25.  
    if (br != null) {
26.  
    try {
27.  
    br.close();
28.  
    br = null;
29.  
    } catch (IOException e) {
30.  
    e.printStackTrace();
31.  
    }
32.  
    }
33.  
    }
34.  
    return dataList;
35.  
    }
36.  
     
37.  
    /**
38.  
    * 写入
39.  
    * csv文件(路径 文件名)，csv文件不存在会自动创建
40.  
    *
41.  
    * @param exportData 数据
42.  
    * @return
43.  
    */
44.  
    public static File exportCsv(List<List<String>> exportData, String outPutPath, String fileName) {
45.  
    File csvFile = null;
46.  
    BufferedWriter csvFileOutputStream = null;
47.  
    try {
48.  
    File file = new File(outPutPath);
49.  
    if (!file.exists()) {
50.  
    if (file.mkdirs()) {
51.  
    log.info("创建成功");
52.  
    } else {
53.  
    log.error("创建失败");
54.  
    }
55.  
    }
56.  
    //定义文件名格式并创建
57.  
    csvFile = File.createTempFile(fileName, ".csv", new File(outPutPath));
58.  
    csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile, true), StandardCharsets.UTF_8), 1024);
59.  
    for (List<String> exportDatum : exportData) {
60.  
    writeRow(exportDatum, csvFileOutputStream);
61.  
    csvFileOutputStream.newLine();
62.  
    }
63.  
    } catch (Exception e) {
64.  
    e.printStackTrace();
65.  
    } finally {
66.  
    try {
67.  
    if (csvFileOutputStream != null) {
68.  
    csvFileOutputStream.close();
69.  
    }
70.  
    } catch (IOException e) {
71.  
    e.printStackTrace();
72.  
    }
73.  
    }
74.  
    return csvFile;
75.  
    }
76.  
     
77.  
    /**
78.  
    * 写一行数据
79.  
    *
80.  
    * @param row 数据列表
81.  
    * @param csvWriter
82.  
    * @throws IOException
83.  
    */
84.  
    public static void writeRow(List<String> row, BufferedWriter csvWriter) throws IOException {
85.  
    int i = 0;
86.  
    for (String data : row) {
87.  
    csvWriter.write(data);
88.  
    if (i != row.size() - 1) {
89.  
    csvWriter.write(",");
90.  
    }
91.  
    i ;
92.  
    }
93.  
    }
94.  
     
95.  
    /**
96.  
    * 剔除特殊字符
97.  
    *
98.  
    * @param str 数据
99.  
    */
100.  
     public static String DelQuota(String str) {
101.  
     String result = str;
102.  
     String[] strQuota = {"~", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "`", ";", "'", ",", ".", "/", ":", "/,", "<", ">", "?"};
103.  
     for (String s : strQuota) {
104.  
     if (result.contains(s)) {
105.  
     result = result.replace(s, "");
106.  
     }
107.  
     }
108.  
     return result;
109.  
     }
110.  
      
111.  
     /**
112.  
     * 测试
113.  
     *
114.  
     * @param args
115.  
     */
116.  
     public static void main(String[] args) {
117.  
     exportCsv();
118.  
     //importCsv();
119.  
     }
120.  
      
121.  
     /**
122.  
     * CSV读取测试
123.  
     *
124.  
     * @throws Exception
125.  
     */
126.  
     public static void importCsv() {
127.  
     List<String[]> dataList = CSVFileUtil.importCsv(new File("F:/test_two.csv"), ",", "GB2312");
128.  
     if (!dataList.isEmpty()) {
129.  
     for (String[] cells : dataList) {
130.  
     if (cells != null && cells.length > 0) {
131.  
     for (String cell : cells) {
132.  
     System.out.print(cell " ");
133.  
     }
134.  
     System.out.println();
135.  
     }
136.  
     }
137.  
     }
138.  
     }
139.  
      
140.  
     /**
141.  
     * CSV写入测试
142.  
     *
143.  
     * @throws Exception
144.  
     */
145.  
     public static void exportCsv() {
146.  
     List<List<String>> listList = new ArrayList<>();
147.  
      
148.  
     List<String> list1 = new ArrayList<>();
149.  
     List<String> list2 = new ArrayList<>();
150.  
     List<String> list3 = new ArrayList<>();
151.  
     list1.add("编号");
152.  
     list1.add("姓名");
153.  
     list1.add("身高");
154.  
     list1.add("电话");
155.  
      
156.  
     list2.add("1");
157.  
     list2.add("小明");
158.  
     list2.add("180cm");
159.  
     list2.add("1111111");
160.  
      
161.  
     list3.add("2");
162.  
     list3.add("小红");
163.  
     list3.add("176cm");
164.  
     list3.add("1111111");
165.  
      
166.  
     listList.add(list1);
167.  
     listList.add(list2);
168.  
     listList.add(list3);
169.  
      
170.  
     CSVFileUtil.exportCsv(listList, "D://", "testFile");
171.  
     }
172.  
     }

到这里分批查询 分批导出已经介绍完了

大家根据需求调整代码 根据源码多测试

最后有遇到什么问题可以留言告诉我哦 欢迎评论区讨论🤪

这篇好文章是转载于：学新通技术网