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

上篇文章介绍了java导出文件格式篇xls,xlsx,csvhttps://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