Geek Answers Handbook

JDBC: export / import CSV source data from / to a remote MySQL database using streams (SELECT INTO OUTFILE / LOAD DATA INFILE)

The web application I'm currently developing supports CSV export from (using SELECT INTO OUTFILE) and imports a MySQL server (using LOAD DATA INFILE) to support huge datasets that are extremely expensive to process using SELECT statements and bulk INSERT in Java code (sets of processing results, string encoding material, legacy business logic, etc.). These CSV files are not controlled by the application, so they simply represent the contents of the source tables from the MySQL database. But as I understand it, this approach is only good if I have local files, so both web application servers and mysqld should work on the same machine.

In the application configuration, you can specify a remote connection to the database. This obviously means that the downloaded CSV files are stored somewhere locally on the computer running the web application, so I cannot specify the location of the data file in the MySQL LOAD DATA INFILE statement. (The same scenario is for CSV download request). So, I'm trying to find a way to specify the CSV file “practically” - using an input / output stream that can be processed by JDBC and MySQL, similar to block management, etc.

Does JDBC / MySQL support this method for CSV files for import and export?

Thanks in advance.

+5
source share
1 answer

LOAD DATA INFILE, LOCAL, CSV , MySQL JDBC Client, com.mysql.jdbc.Statement#setLocalInfileInputStream. :

// conn is an existing java.sql.Connection to a remote server
try (Statement st = conn.createStatement()) {
    String localCsvFileSpec = "C:/Users/Jamie/Desktop/foo.csv";  // on this machine
    ((com.mysql.jdbc.Statement) st).setLocalInfileInputStream(
            new FileInputStream(localCsvFileSpec));
    st.execute(
            "LOAD DATA LOCAL INFILE '(placeholder)' " + 
            "INTO TABLE table01 " +
            "COLUMNS TERMINATED BY ',' " +
            "(id, txt) " + 
            "");
}

. .

, , SELECT INTO OUTFILE , , .

+4

More articles:


All Articles