import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/*
 1. mysql-connector-java: 8.0.18

 2. Table creation Script:
  select * from time_CREATE TABLE `time_test` (
     `id` bigint(20) NOT NULL AUTO_INCREMENT,
     `ts` timestamp NULL DEFAULT NULL,
     `dt` datetime DEFAULT NULL,
      `name` varchar(45) DEFAULT NULL,
     PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

3. Data File: data.csv
2019-03-09 23:01:54,2019-03-09 23:01:54,2019-03-09 23:01:54
2019-03-10 02:00:00,2019-03-10 02:00:00,2019-03-10 02:00:00
2019-03-10 23:01:54,2019-03-10 23:01:54,2019-03-10 23:01:54
2019-03-11 23:01:54,2019-03-11 23:01:54,2019-03-11 23:01:54
2019-04-13 23:01:54,2019-04-13 23:01:54,2019-04-13 23:01:54
2019-11-02 23:01:54,2019-11-02 23:01:54,2019-11-02 23:01:54
2019-11-03 02:00:00,2019-11-03 02:00:00,2019-11-03 02:00:00
2019-11-03 01:00:00,2019-11-03 01:00:00,2019-11-03 01:00:00
2019-11-03 03:00:00,2019-11-03 03:00:00,2019-11-03 03:00:00
2019-11-03 04:00:00,2019-11-03 04:00:00,2019-11-03 04:00:00
2019-11-03 23:01:54,2019-11-03 23:01:54,2019-11-03 23:01:54
2019-11-21 23:01:54,2019-11-21 23:01:54,2019-11-21 23:01:54

 */

//JDBC_URL = "jdbc:mysql://dbserver:3306/dbname1?allowLoadLocalInfile=true
//JDBC_URL = "jdbc:mysql://dbserver:3306/dbname1?allowLoadLocalInfile=true&serverTimezone=UTC
public class TestLoadInFileMySql8 {

    public static void main(String[] args) {
        testLoadInFile();
    }

    public static void testLoadInFile(){


        File dataFile = new File("./data.csv");
        StringBuilder loadQuery = new StringBuilder();
        loadQuery.append("LOAD DATA LOCAL INFILE '").append(dataFile.getAbsolutePath()).
                append("' INTO TABLE time_test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' " +
                        " (ts,dt,date_input_str)");
        PreparedStatement preparedStmt = null;
        try (Connection connection = getConnection()){
             preparedStmt = connection.prepareStatement(loadQuery.toString());

             connection.setAutoCommit(false);
             preparedStmt.execute(loadQuery.toString());
             connection.commit();

             preparedStmt = connection.prepareStatement("select ts, dt, name from time_test");
             ResultSet resultSet = preparedStmt.executeQuery();

             while(resultSet.next()) {
                Object ts = resultSet.getObject("ts");
                Object dt = resultSet.getObject("dt");
                String name = resultSet.getString("name");
                System.out.print("ts:"+ ts + " ,");
                System.out.print("dt:"+ dt +", ");
                System.out.println("name:"+ name);

            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            if(preparedStmt!= null) {
                try {
                    preparedStmt.close();
                } catch (Exception e) {
                }
            }
        }
    }
}