数据库大数据量导出（支持csv和excel）

大数据量的数据导出（支持csv和excel）

我用的 JAVA17，oracle 数据库

主要思路是先接收每一行的数据，然后临时储存起来，到一定数据量（可自由配置，默认设置了10w）的时候就写出到文件中

使用了 hutool 和 druid 数据源（主要是用 NamedParameterJdbcTemplate ）,可以使用多线程配合使用，查询速度更快

maven 如下

		<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.11</version>
        </dependency>
        
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>21.7.0.0</version>
        </dependency>

        <dependency>
            <groupId>com.oracle.database.nls</groupId>
            <artifactId>orai18n</artifactId>
            <version>21.7.0.0</version>
        </dependency>
		
		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.9</version>
        </dependency>

工具类代码

package com.xxx.common;

import ch.qos.logback.classic.Level;
import ch.qos.logback.classic.LoggerContext;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.Console;
import cn.hutool.core.text.csv.CsvUtil;
import cn.hutool.core.text.csv.CsvWriter;
import cn.hutool.core.util.CharsetUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.lhk.datasql.config.HisDataSource;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.Assert;

import java.awt.*;
import java.io.File;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @Author: yren
 * @Date: 2023/2/9
 * @ApiNote
 */
public class QueryItem {
    public enum FileType {
        EXCEL, CSV
    }

    public static String SEPARATOR = File.separator;
    public static String DESKTOP_PREFIX = Desktop.getDesktop() + SEPARATOR;

    private NamedParameterJdbcTemplate jdbcTemplate = MyDataSource.getJdbcTemplate();
    private ResultSetMetaInfo info;//储存列信息
    private int total;//查询总条目数量
    private String sql;
    private Map<String, Object> params;
    private String path = DESKTOP_PREFIX;
    private String fileName;
    private File file;
    private FileType type = FileType.EXCEL;
    private int size = 100000;//设置批量处理数据的大小
    /****************************************************  文件 *******************************************************/
    private ExcelWriter excelWriter;
    private CsvWriter csvWriter;
    private int writerRowIndex = 0;//储存文件当前行数，以便下次继续写入

    /**
     * 调整 sql 打印日志级别，调整之后不会打印 sql
     */
    public static void sqlLogInfo() {
        LoggerContext loggerContext = (LoggerContext) LoggerFactory.getILoggerFactory();
        List<ch.qos.logback.classic.Logger> loggerList = loggerContext.getLoggerList();
        loggerList.forEach(logger -> logger.setLevel(Level.INFO));
    }

    /**
     * 使用方法如下
     * - Map<String, Object> params = MyUtil.transDateParams("2021-01-01", "2022-12-31");
     * - new Thread(() -> QueryUtil.create()
     * -    .setFileName("view_xx1")
     * -    .setType(QueryUtil.FileType.CSV)
     * -    .setSql("select * from view_xx1 where 入库时间 between :startDate and :endDate")
     * -    .setParams(params)
     * -    .setSize(100000)
     * -    .queryData()).start();
     * -
     * - new Thread(() -> QueryUtil.create()
     * -    .setFileName("view_xx2")
     * -    .setType(QueryUtil.FileType.EXCEL)
     * -    .setSql("select * from view_xx2 where 入库时间 between :startDate and :endDate")
     * -    .setParams(params)
     * -    .setSize(100000)
     * -    .queryData()).start();
     */

    private void queryBefore() {
        Assert.notNull(sql, "查询 sql语句 不能为空！");
        Assert.notNull(fileName, "导出的文件名称不能为空！");
        switch (type) {
            case CSV -> {
                this.file = FileUtil.file(path, fileName + ".csv");
                csvWriter = CsvUtil.getWriter(file, CharsetUtil.CHARSET_GBK);
            }
            case EXCEL -> {
                this.file = FileUtil.file(path, fileName + ".xlsx");
                excelWriter = ExcelUtil.getBigWriter(file);
            }
            default -> throw new RuntimeException("类型错误！");
        }
        File parentFile = this.file.getParentFile();
        if (parentFile != null) {
            if (!parentFile.exists()) {
                parentFile.mkdirs();
            }
        }
        if (this.file.exists()) {
            this.file.delete();
        }
        this.total = jdbcTemplate.queryForObject("select count(*) from( " + sql + " )", params, Integer.class);
    }

    private void queryAfter() {
        if (csvWriter != null) {
            csvWriter.close();
        }
        if (excelWriter != null) {
            excelWriter.close();
        }
    }

    public void queryData() {
        queryBefore();
        List<Map<String, Object>> list = new ArrayList<>();
        TimeInterval timer = DateUtil.timer();
        AtomicInteger index = new AtomicInteger(0);
        jdbcTemplate.query(sql, params, rs -> {
            int idx = index.incrementAndGet();
            list.add(processRow(rs));
            if (idx % size == 0) {
                processList(list);
                Console.log("=== 已写入 【{}[{}/{}]】 行 ... ", file.getName(), idx, total);
                list.clear();
            } else if (idx == this.total) {
                processList(list);
                Console.log("=== 已写入 【{}[{}/{}]】 行 ... ", file.getName(), idx, total);
                list.clear();
            }
        });
        Console.log("=== 总共写入 【{}[{}]】 行,花费时间 {} ms... ", fileName, index.get(), timer.intervalMs());
        queryAfter();
    }

    private Map<String, Object> processRow(ResultSet rs) {
        if (info == null) {
            synchronized (this) {
                if (info == null) {
                    info = new ResultSetMetaInfo(rs);
                }
            }
        }
        Map<String, Object> tMap = new LinkedHashMap<>();
        for (int i = 1; i <= info.columnCount; i++) {
            try {
                tMap.put(info.columnNames[i - 1], JdbcUtils.getResultSetValue(rs, i));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tMap;
    }

    private void processList(List<Map<String, Object>> list) {
        if (CollUtil.isEmpty(list)) {
            return;
        }
        switch (type) {
            case CSV -> writeToCSVFile(file, list);
            case EXCEL -> writeToExcelFile(file, list);
            default -> throw new RuntimeException("类型错误！");
        }
    }

    /**
     * 写入 excel 文件
     */
    private void writeToExcelFile(File file, List<Map<String, Object>> data) {
        if (file.exists()) {
            excelWriter.setCurrentRow(writerRowIndex);
        }
        excelWriter.write(data);
        writerRowIndex = excelWriter.getCurrentRow();
    }

    private void writeToCSVFile(File file, List<Map<String, Object>> data) {
        if (writerRowIndex == 0) {
            csvWriter.writeHeaderLine(parseLine(data.get(0), true));
            writerRowIndex = 1;
        }
        data.forEach(m -> csvWriter.writeLine(parseLine(m, false)));
        writerRowIndex += data.size();
    }

    private String[] parseLine(Map<String, Object> data, boolean parseKey) {
        String[] arr = new String[data.size()];
        AtomicInteger idx = new AtomicInteger(0);
        data.forEach((k, v) -> arr[idx.getAndIncrement()] = (parseKey ? k : Convert.toStr(v)));
        return arr;
    }

    public static QueryItem create() {
        return new QueryItem();
    }

    public String getSql() {
        return sql;
    }

    public QueryItem setSql(String sql) {
        this.sql = sql;
        return this;
    }

    public Map<String, Object> getParams() {
        return params;
    }

    public QueryItem setParams(Map<String, Object> params) {
        this.params = params;
        return this;
    }

    public String getPath() {
        return path;
    }

    public QueryItem setPath(String path) {
        this.path = path;
        return this;
    }

    public String getFileName() {
        return fileName;
    }

    public QueryItem setFileName(String fileName) {
        this.fileName = fileName;
        return this;
    }

    public QueryItem setType(FileType type) {
        this.type = type;
        return this;
    }

    public QueryItem setSize(int size) {
        this.size = size;
        return this;
    }

    private class ResultSetMetaInfo {
        int columnCount;
        int[] columnTypes;
        String[] columnNames;

        /**
         * @see org.springframework.jdbc.core.RowCountCallbackHandler#processRow(ResultSet)
         */
        ResultSetMetaInfo(ResultSet rs) {
            try {
                ResultSetMetaData rsmd = rs.getMetaData();
                this.columnCount = rsmd.getColumnCount();
                this.columnTypes = new int[columnCount];
                this.columnNames = new String[columnCount];
                for (int i = 0; i < columnCount; i++) {
                    columnTypes[i] = rsmd.getColumnType(i + 1);
                    columnNames[i] = JdbcUtils.lookupColumnName(rsmd, i + 1);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

主函数代码

public class TestUtil {

    public static void main(String[] args) throws SQLException {
        String path = QueryUtil.DESKTOP_PREFIX + "test";
        Map<String, Object> params = MyUtil.transDateParams("2021-01-01", "2021-01-31");

        new Thread(() -> QueryUtil.create()
                .setPath(path)
                .setFileName("view_xx1")
                .setType(QueryUtil.FileType.CSV)
                .setSql("select * from view_xx1 where time between :startDate and :endDate")
                .setParams(params)
                .setSize(50000)
                .queryData()).start();

        new Thread(() -> QueryUtil.create()
                .setPath(path)
                .setFileName("view_xx2")
                .setType(QueryUtil.FileType.CSV)
                .setSql("select * from view_xx2 where time  between :startDate and :endDate")
                .setParams(params)
                .setSize(50000)
                .queryData()).start();

        new Thread(() -> QueryUtil.create()
                .setPath(path)
                .setFileName("view_xx3")
                .setType(QueryUtil.FileType.CSV)
                .setSql("select * from view_xx3 where time  between :startDate and :endDate")
                .setParams(params)
                .setSize(50000)
                .queryData()).start();

        new Thread(() -> QueryUtil.create()
                .setPath(path)
                .setFileName("view_xx4")
                .setType(QueryUtil.FileType.CSV)
                .setSql("select * from view_xx4 where time  between :startDate and :endDate")
                .setParams(params)
                .setSize(50000)
                .queryData()).start();
    }
}

#####运行结果

"C:\Program Files\Java\jdk-17.0.5\bin\java.exe" -agentlib:jdwp=transport=dt_socket,address=127.0.0.1:1907,suspend=y,server=n -javaagent:C:\Users\yren\AppData\Local\JetBrains\IntelliJIdea2021.3\captureAgent\debugger-agent.jar -Dfile.encoding=GBK -classpath "D:\IdeaWorkspace\DataSql\target\classes;D:\soft_dev\maven-repository\com\microsoft\sqlserver\mssql-jdbc\11.2.2.jre17\mssql-jdbc-11.2.2.jre17.jar;D:\soft_dev\maven-repository\org\tmatesoft\svnkit\svnkit\1.10.8\svnkit-1.10.8.jar;D:\soft_dev\maven-repository\org\apache\sshd\sshd-core\2.8.0\sshd-core-2.8.0.jar;D:\soft_dev\maven-repository\org\slf4j\jcl-over-slf4j\1.7.30\jcl-over-slf4j-1.7.30.jar;D:\soft_dev\maven-repository\org\apache\sshd\sshd-common\2.8.0\sshd-common-2.8.0.jar;D:\soft_dev\maven-repository\net\i2p\crypto\eddsa\0.3.0\eddsa-0.3.0.jar;D:\soft_dev\maven-repository\de\regnis\q\sequence\sequence-library\1.0.4\sequence-library-1.0.4.jar;D:\soft_dev\maven-repository\org\tmatesoft\sqljet\sqljet\1.1.14\sqljet-1.1.14.jar;D:\soft_dev\maven-repository\org\antlr\antlr-runtime\3.4\antlr-runtime-3.4.jar;D:\soft_dev\maven-repository\net\java\dev\jna\jna\4.5.2\jna-4.5.2.jar;D:\soft_dev\maven-repository\net\java\dev\jna\jna-platform\4.5.2\jna-platform-4.5.2.jar;D:\soft_dev\maven-repository\com\trilead\trilead-ssh2\1.0.0-build222\trilead-ssh2-1.0.0-build222.jar;D:\soft_dev\maven-repository\com\jcraft\jsch.agentproxy.connector-factory\0.0.7\jsch.agentproxy.connector-factory-0.0.7.jar;D:\soft_dev\maven-repository\com\jcraft\jsch.agentproxy.core\0.0.7\jsch.agentproxy.core-0.0.7.jar;D:\soft_dev\maven-repository\com\jcraft\jsch.agentproxy.usocket-jna\0.0.7\jsch.agentproxy.usocket-jna-0.0.7.jar;D:\soft_dev\maven-repository\net\java\dev\jna\platform\3.4.0\platform-3.4.0.jar;D:\soft_dev\maven-repository\com\jcraft\jsch.agentproxy.usocket-nc\0.0.7\jsch.agentproxy.usocket-nc-0.0.7.jar;D:\soft_dev\maven-repository\com\jcraft\jsch.agentproxy.sshagent\0.0.7\jsch.agentproxy.sshagent-0.0.7.jar;D:\soft_dev\maven-repository\com\jcraft\jsch.agentproxy.pageant\0.0.7\jsch.agentproxy.pageant-0.0.7.jar;D:\soft_dev\maven-repository\com\jcraft\jsch.agentproxy.svnkit-trilead-ssh2\0.0.7\jsch.agentproxy.svnkit-trilead-ssh2-0.0.7.jar;D:\soft_dev\maven-repository\org\lz4\lz4-java\1.4.1\lz4-java-1.4.1.jar;D:\soft_dev\maven-repository\org\apache\commons\commons-lang3\3.8\commons-lang3-3.8.jar;D:\soft_dev\maven-repository\org\apache\poi\poi-ooxml\4.1.2\poi-ooxml-4.1.2.jar;D:\soft_dev\maven-repository\org\apache\poi\poi\4.1.2\poi-4.1.2.jar;D:\soft_dev\maven-repository\commons-codec\commons-codec\1.13\commons-codec-1.13.jar;D:\soft_dev\maven-repository\org\apache\commons\commons-collections4\4.4\commons-collections4-4.4.jar;D:\soft_dev\maven-repository\org\apache\commons\commons-math3\3.6.1\commons-math3-3.6.1.jar;D:\soft_dev\maven-repository\com\zaxxer\SparseBitSet\1.2\SparseBitSet-1.2.jar;D:\soft_dev\maven-repository\org\apache\poi\poi-ooxml-schemas\4.1.2\poi-ooxml-schemas-4.1.2.jar;D:\soft_dev\maven-repository\org\apache\xmlbeans\xmlbeans\3.1.0\xmlbeans-3.1.0.jar;D:\soft_dev\maven-repository\org\apache\commons\commons-compress\1.19\commons-compress-1.19.jar;D:\soft_dev\maven-repository\com\github\virtuald\curvesapi\1.06\curvesapi-1.06.jar;D:\soft_dev\maven-repository\xerces\xercesImpl\2.12.0\xercesImpl-2.12.0.jar;D:\soft_dev\maven-repository\xml-apis\xml-apis\1.4.01\xml-apis-1.4.01.jar;D:\soft_dev\maven-repository\cn\hutool\hutool-all\5.8.11\hutool-all-5.8.11.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-starter-web\2.2.5.RELEASE\spring-boot-starter-web-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-starter\2.2.5.RELEASE\spring-boot-starter-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot\2.2.5.RELEASE\spring-boot-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-starter-logging\2.2.5.RELEASE\spring-boot-starter-logging-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\apache\logging\log4j\log4j-to-slf4j\2.12.1\log4j-to-slf4j-2.12.1.jar;D:\soft_dev\maven-repository\org\apache\logging\log4j\log4j-api\2.12.1\log4j-api-2.12.1.jar;D:\soft_dev\maven-repository\org\slf4j\jul-to-slf4j\1.7.30\jul-to-slf4j-1.7.30.jar;D:\soft_dev\maven-repository\jakarta\annotation\jakarta.annotation-api\1.3.5\jakarta.annotation-api-1.3.5.jar;D:\soft_dev\maven-repository\org\springframework\spring-core\5.2.4.RELEASE\spring-core-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\spring-jcl\5.2.4.RELEASE\spring-jcl-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\yaml\snakeyaml\1.25\snakeyaml-1.25.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-starter-json\2.2.5.RELEASE\spring-boot-starter-json-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\com\fasterxml\jackson\core\jackson-databind\2.10.2\jackson-databind-2.10.2.jar;D:\soft_dev\maven-repository\com\fasterxml\jackson\core\jackson-annotations\2.10.2\jackson-annotations-2.10.2.jar;D:\soft_dev\maven-repository\com\fasterxml\jackson\core\jackson-core\2.10.2\jackson-core-2.10.2.jar;D:\soft_dev\maven-repository\com\fasterxml\jackson\datatype\jackson-datatype-jdk8\2.10.2\jackson-datatype-jdk8-2.10.2.jar;D:\soft_dev\maven-repository\com\fasterxml\jackson\datatype\jackson-datatype-jsr310\2.10.2\jackson-datatype-jsr310-2.10.2.jar;D:\soft_dev\maven-repository\com\fasterxml\jackson\module\jackson-module-parameter-names\2.10.2\jackson-module-parameter-names-2.10.2.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-starter-tomcat\2.2.5.RELEASE\spring-boot-starter-tomcat-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\apache\tomcat\embed\tomcat-embed-core\9.0.31\tomcat-embed-core-9.0.31.jar;D:\soft_dev\maven-repository\org\apache\tomcat\embed\tomcat-embed-el\9.0.31\tomcat-embed-el-9.0.31.jar;D:\soft_dev\maven-repository\org\apache\tomcat\embed\tomcat-embed-websocket\9.0.31\tomcat-embed-websocket-9.0.31.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-starter-validation\2.2.5.RELEASE\spring-boot-starter-validation-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\jakarta\validation\jakarta.validation-api\2.0.2\jakarta.validation-api-2.0.2.jar;D:\soft_dev\maven-repository\org\hibernate\validator\hibernate-validator\6.0.18.Final\hibernate-validator-6.0.18.Final.jar;D:\soft_dev\maven-repository\org\springframework\spring-web\5.2.4.RELEASE\spring-web-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\spring-beans\5.2.4.RELEASE\spring-beans-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\spring-webmvc\5.2.4.RELEASE\spring-webmvc-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\spring-aop\5.2.4.RELEASE\spring-aop-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\spring-context\5.2.4.RELEASE\spring-context-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\spring-expression\5.2.4.RELEASE\spring-expression-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-starter-batch\2.2.5.RELEASE\spring-boot-starter-batch-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-starter-jdbc\2.2.5.RELEASE\spring-boot-starter-jdbc-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\com\zaxxer\HikariCP\3.4.2\HikariCP-3.4.2.jar;D:\soft_dev\maven-repository\org\springframework\spring-jdbc\5.2.4.RELEASE\spring-jdbc-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\batch\spring-batch-core\4.2.1.RELEASE\spring-batch-core-4.2.1.RELEASE.jar;D:\soft_dev\maven-repository\io\micrometer\micrometer-core\1.3.5\micrometer-core-1.3.5.jar;D:\soft_dev\maven-repository\org\hdrhistogram\HdrHistogram\2.1.11\HdrHistogram-2.1.11.jar;D:\soft_dev\maven-repository\org\latencyutils\LatencyUtils\2.0.3\LatencyUtils-2.0.3.jar;D:\soft_dev\maven-repository\javax\batch\javax.batch-api\1.0\javax.batch-api-1.0.jar;D:\soft_dev\maven-repository\org\codehaus\jettison\jettison\1.2\jettison-1.2.jar;D:\soft_dev\maven-repository\org\springframework\batch\spring-batch-infrastructure\4.2.1.RELEASE\spring-batch-infrastructure-4.2.1.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\retry\spring-retry\1.2.5.RELEASE\spring-retry-1.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\spring-tx\5.2.4.RELEASE\spring-tx-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-starter-data-jpa\2.2.5.RELEASE\spring-boot-starter-data-jpa-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-starter-aop\2.2.5.RELEASE\spring-boot-starter-aop-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\aspectj\aspectjweaver\1.9.5\aspectjweaver-1.9.5.jar;D:\soft_dev\maven-repository\jakarta\activation\jakarta.activation-api\1.2.2\jakarta.activation-api-1.2.2.jar;D:\soft_dev\maven-repository\jakarta\persistence\jakarta.persistence-api\2.2.3\jakarta.persistence-api-2.2.3.jar;D:\soft_dev\maven-repository\jakarta\transaction\jakarta.transaction-api\1.3.3\jakarta.transaction-api-1.3.3.jar;D:\soft_dev\maven-repository\org\hibernate\hibernate-core\5.4.12.Final\hibernate-core-5.4.12.Final.jar;D:\soft_dev\maven-repository\org\jboss\logging\jboss-logging\3.4.1.Final\jboss-logging-3.4.1.Final.jar;D:\soft_dev\maven-repository\org\javassist\javassist\3.24.0-GA\javassist-3.24.0-GA.jar;D:\soft_dev\maven-repository\net\bytebuddy\byte-buddy\1.10.8\byte-buddy-1.10.8.jar;D:\soft_dev\maven-repository\antlr\antlr\2.7.7\antlr-2.7.7.jar;D:\soft_dev\maven-repository\org\jboss\jandex\2.1.1.Final\jandex-2.1.1.Final.jar;D:\soft_dev\maven-repository\com\fasterxml\classmate\1.5.1\classmate-1.5.1.jar;D:\soft_dev\maven-repository\org\dom4j\dom4j\2.1.1\dom4j-2.1.1.jar;D:\soft_dev\maven-repository\org\hibernate\common\hibernate-commons-annotations\5.1.0.Final\hibernate-commons-annotations-5.1.0.Final.jar;D:\soft_dev\maven-repository\org\glassfish\jaxb\jaxb-runtime\2.3.2\jaxb-runtime-2.3.2.jar;D:\soft_dev\maven-repository\jakarta\xml\bind\jakarta.xml.bind-api\2.3.2\jakarta.xml.bind-api-2.3.2.jar;D:\soft_dev\maven-repository\org\glassfish\jaxb\txw2\2.3.2\txw2-2.3.2.jar;D:\soft_dev\maven-repository\com\sun\istack\istack-commons-runtime\3.0.8\istack-commons-runtime-3.0.8.jar;D:\soft_dev\maven-repository\org\jvnet\staxex\stax-ex\1.8.1\stax-ex-1.8.1.jar;D:\soft_dev\maven-repository\com\sun\xml\fastinfoset\FastInfoset\1.2.16\FastInfoset-1.2.16.jar;D:\soft_dev\maven-repository\org\springframework\data\spring-data-jpa\2.2.5.RELEASE\spring-data-jpa-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\data\spring-data-commons\2.2.5.RELEASE\spring-data-commons-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\spring-orm\5.2.4.RELEASE\spring-orm-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\org\springframework\spring-aspects\5.2.4.RELEASE\spring-aspects-5.2.4.RELEASE.jar;D:\soft_dev\maven-repository\com\alibaba\druid-spring-boot-starter\1.2.9\druid-spring-boot-starter-1.2.9.jar;D:\soft_dev\maven-repository\com\alibaba\druid\1.2.9\druid-1.2.9.jar;D:\soft_dev\maven-repository\javax\annotation\javax.annotation-api\1.3.2\javax.annotation-api-1.3.2.jar;D:\soft_dev\maven-repository\org\slf4j\slf4j-api\1.7.30\slf4j-api-1.7.30.jar;D:\soft_dev\maven-repository\org\springframework\boot\spring-boot-autoconfigure\2.2.5.RELEASE\spring-boot-autoconfigure-2.2.5.RELEASE.jar;D:\soft_dev\maven-repository\com\oracle\database\jdbc\ojdbc8\21.7.0.0\ojdbc8-21.7.0.0.jar;D:\soft_dev\maven-repository\com\oracle\database\nls\orai18n\21.7.0.0\orai18n-21.7.0.0.jar;D:\soft_dev\maven-repository\org\projectlombok\lombok\1.18.12\lombok-1.18.12.jar;D:\soft_dev\maven-repository\com\alibaba\fastjson\1.2.66\fastjson-1.2.66.jar;D:\soft_dev\maven-repository\cn\smallbun\screw\screw-core\1.0.5\screw-core-1.0.5.jar;D:\soft_dev\maven-repository\commons-lang\commons-lang\2.6\commons-lang-2.6.jar;D:\soft_dev\maven-repository\commons-logging\commons-logging\1.2\commons-logging-1.2.jar;D:\soft_dev\maven-repository\org\freemarker\freemarker\2.3.29\freemarker-2.3.29.jar;D:\soft_dev\maven-repository\ch\qos\logback\logback-classic\1.2.3\logback-classic-1.2.3.jar;D:\soft_dev\maven-repository\ch\qos\logback\logback-core\1.2.3\logback-core-1.2.3.jar;D:\soft_dev\maven-repository\junit\junit\4.12\junit-4.12.jar;D:\soft_dev\maven-repository\org\hamcrest\hamcrest-core\2.1\hamcrest-core-2.1.jar;D:\soft_dev\maven-repository\org\hamcrest\hamcrest\2.1\hamcrest-2.1.jar;D:\soft_dev\IntelliJ IDEA 2021.3.2\lib\idea_rt.jar" com.lhk.datasql.lifework.cwk.TestUtil
Connected to the target VM, address: '127.0.0.1:1907', transport: 'socket'
09:24:04.875 [Thread-2] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
09:24:04.875 [Thread-0] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
09:24:04.875 [Thread-1] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
09:24:04.879 [Thread-2] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select count(*) from( select * from view_xx4 where time between ? and ? )]
09:24:04.879 [Thread-0] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select count(*) from( select * from view_xx1 where time between ? and ? )]
09:24:04.879 [Thread-1] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select count(*) from( select * from view_xx2 where time between ? and ? )]
09:24:04.887 [Thread-1] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
09:24:04.887 [Thread-2] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
09:24:04.887 [Thread-0] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
09:24:05.239 [Thread-2] INFO com.alibaba.druid.pool.DruidDataSource - {dataSource-1} inited
09:24:05.340 [Thread-3] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
09:24:05.341 [Thread-3] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select count(*) from( select * from view_xx3 where time between ? and ? )]
09:24:05.341 [Thread-3] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
09:24:06.182 [Thread-0] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
09:24:06.182 [Thread-0] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select * from view_xx1 where time between ? and ?]
09:24:06.182 [Thread-0] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
09:24:06.197 [Thread-1] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
09:24:06.197 [Thread-1] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select * from view_xx2 where time between ? and ?]
09:24:06.197 [Thread-1] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
=== 已写入 【view_xx1.csv[1120/1120]】 行 ... 
=== 总共写入 【view_xx1[1120]】 行,花费时间 384 ms... 
09:24:06.633 [Thread-3] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
09:24:06.633 [Thread-3] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select * from view_xx3 where time between ? and ?]
09:24:06.633 [Thread-3] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
=== 已写入 【view_xx2.csv[2767/2767]】 行 ... 
=== 总共写入 【view_xx2[2767]】 行,花费时间 505 ms... 
09:24:06.829 [Thread-2] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
09:24:06.829 [Thread-2] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select * from view_xx4 where time between ? and ?]
09:24:06.829 [Thread-2] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
=== 已写入 【view_xx3.csv[50000/105998]】 行 ... 
=== 已写入 【view_xx4.csv[50000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[100000/934044]】 行 ... 
=== 已写入 【view_xx3.csv[100000/105998]】 行 ... 
=== 已写入 【view_xx3.csv[105998/105998]】 行 ... 
=== 总共写入 【view_xx3[105998]】 行,花费时间 11139 ms... 
=== 已写入 【view_xx4.csv[150000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[200000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[250000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[300000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[350000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[400000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[450000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[500000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[550000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[600000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[650000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[700000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[750000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[800000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[850000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[900000/934044]】 行 ... 
=== 已写入 【view_xx4.csv[934044/934044]】 行 ... 
=== 总共写入 【view_xx4[934044]】 行,花费时间 86687 ms... 
Disconnected from the target VM, address: '127.0.0.1:1907', transport: 'socket'

Process finished with exit code 0

结果文件

以下内容可以忽略

发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】
发文助手会检测您的文章标题、错别字、内容质量，助您提升文章质量。【创作规范】