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

Question

The web application I'm currently developing supports CSV export from (using SELECT INTO OUTFILE) and import to (using LOAD DATA INFILE) MySQL server to maintain huge data sets that are extremely expensive to be processed using SELECT and bulk INSERT statements in Java code (processing result sets, string encoding stuff, business logic heritage, etc). These CSV files are not application-driven, so they just represent the raw tables content from the MySQL database. But as far as I understand this approach is good only if I have local files, so both web application server and mysqld must run at the same machine.

The application configuration can specify a remote database connection. That obviously means that uploaded CSV files are stored somewhere locally at the machine where the web application is running on, so I cannot specify the data file location in MySQL LOAD DATA INFILE statement. (The same scenario is for a CSV download request). So, what I'm trying to find is a way to specify the CSV file "virtually" - using a I/O stream that could be processed by JDBC and MySQL, similarly to blobs management etc.

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

Thanks in advance.

Answer 1

You can run LOAD DATA INFILE using the LOCAL option which will then load the CSV file from where the MySQL JDBC Client is running from, using com.mysql.jdbc.Statement#setLocalInfileInputStream. For example:

// 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) " + 
            "");
}

See this post for more information.

Unfortunately, it appears that you can't use SELECT INTO OUTFILE to export files anywhere other than your database server, from this answer on Stack Overflow.