MySQL 流式导入

package com.jointables;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.*;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class QueryAllTable {

    public static Connection getConnection() throws SQLException {
        //int maxActive = Integer.parseInt(SystemConfig.ruleOtherArgsConfig.get(uuid).getProperty(SystemConstants.HIVE_MAX_CONN, "10"));
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setMaxWait(-1);
        dataSource.setMinIdle(1);
        dataSource.setInitialSize(1);
        dataSource.setMaxActive(2);
        dataSource.setKeepAlive(true);
        dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);
        dataSource.setTestWhileIdle(true);
        dataSource.setBreakAfterAcquireFailure(true);
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        dataSource.setPhyTimeoutMillis(3600 * 24 * 1000);
        dataSource.setTimeBetweenConnectErrorMillis(600000);
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setValidationQuery("select 1");
        String ip = "192.168.40.226";
        String port = "3306";
        String db = "test";
        String url = "jdbc:mysql://" + ip + ":" + port + "/" + db + "?useSSL=false&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true&allowLoadLocalInfile=true";
        dataSource.setUrl(url);
        return dataSource.getConnection();
    }

    /**
     * MySQL使用load data local infile 从文件中导入数据比insert语句要快，MySQL文档上说要快20倍左右。
     * 服务端设置 set global local_infile=1;
     * 客户端 jdbc 设置 allowLoadLocalInfile=true
     */

    public static void loadToMysqlByIO() throws SQLException, ClassNotFoundException, NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        StringBuilder builder = new StringBuilder();
        for (int i = 4; i < 10; i++) {
            builder.append(i);
            builder.append("\t");
            builder.append(2);
            builder.append("\t");
            builder.append("whz");
            builder.append("\n");
        }
        String sql ="LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.ts1 (id,c1,c2)";
        ByteArrayInputStream inputStream = new ByteArrayInputStream(builder.toString().getBytes());
        Class<?> aClass = Class.forName("com.mysql.cj.jdbc.ClientPreparedStatement");
        Method setLocalInfileInputStream = aClass.getMethod("setLocalInfileInputStream", InputStream.class);
        PreparedStatement statement = getConnection().prepareStatement(sql);
        Object unwrap = statement.unwrap(aClass);
        setLocalInfileInputStream.invoke(unwrap, inputStream);
        int i = statement.executeUpdate();
        System.out.println(i);
    }

}