e292. loading a flat file to a mysql table.txt

来自「这里面包含了一百多个JAVA源文件」· 文本 代码 · 共 27 行

The default format of a file to load into a MySQL table is as follows: the fields must be separated by tabs, the input lines terminated by '\n', and backslashes(\), newlines (\n), and tabs (\t) escaped by a backslash. The MySQL documentation explains how to change these defaults. 
This example loads a flat file called infile.txt to a MySQL table named mysql_2_table with an INT and a VARCHAR(20) column. 

    try {
        // Create the statement
        Statement stmt = connection.createStatement();
    
        // Load the data
        String filename = "c:\\\\temp\\\\infile.txt";
        String tablename = "mysql_2_table";
        stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE " + tablename);
    
        // If the file is comma-separated, use this statement
        stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE "
                           + tablename + " FIELDS TERMINATED BY ','");
    
        // If the file is terminated by \r\n, use this statement
        stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE "
                           + tablename + " LINES TERMINATED BY '\\r\\n'");
    } catch (SQLException e) {
    }

An example of the contents of infile.txt (\t represents an invisible tab character): 
    123\tString1
    234\tString2