將CSV文件導進MySQL表格的Java示例

將CSV文件中的數據導進MySQL表格的Java示例
ImportCsv.java:

package com.examples;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import com.opencsv.CSVReader;
public class ImportCsv
{
    public static void main(String[] args)
    {
            readCsv();
            readCsvUsingLoad();
    }

private static void readCsv()
{

    try (CSVReader reader = new CSVReader(new FileReader("upload.csv"), ','); 
                 Connection connection = DBConnection.getConnection();)
    {
            String insertQuery = "Insert into txn_tbl (txn_id,txn_amount, card_number, terminal_id) values (null,?,?,?)";
            PreparedStatement pstmt = connection.prepareStatement(insertQuery);
            String[] rowData = null;
            int i = 0;
            while((rowData = reader.readNext()) != null)
            {
                for (String data : rowData)
                {
                        pstmt.setString((i % 3) + 1, data);

                        if (++i % 3 == 0)
                                pstmt.addBatch();// add batch

                        if (i % 30 == 0)// insert when the batch size is 10
                                pstmt.executeBatch();
                }
            }
            System.out.println("Data Successfully Uploaded");
    }
    catch (Exception e)
    {
            e.printStackTrace();
    }

}

private static void readCsvUsingLoad()
{
    try (Connection connection = DBConnection.getConnection())
    {

            String loadQuery = "LOAD DATA LOCAL INFILE '" + "C:\\upload.csv" + "' INTO TABLE txn_tbl FIELDS TERMINATED BY ','" + " LINES TERMINATED BY '\n' (txn_amount, card_number, terminal_id) ";
            System.out.println(loadQuery);
            Statement stmt = connection.createStatement();
            stmt.execute(loadQuery);
    }
    catch (Exception e)
    {
            e.printStackTrace();
    }
}


}
</pre> 
  Sample CSV File: 
254.23,123456789,12345
2854.00,987654321,87924
8724.03,598767812,56568
 
 
  Create txn_tbl SQL :  
 
CREATE TABLE `txn_tbl` (
`txn_id`  int(11) NOT NULL AUTO_INCREMENT ,
`txn_amount`  double NOT NULL ,
`card_number`  bigint(20) NOT NULL ,
`terminal_id`  bigint(20) NULL DEFAULT NULL ,
PRIMARY KEY (`txn_id`)
)
 
 </em>