将CSV文件导进MySQL表格的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();
		}
	}

}

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`)
)