将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(); 
		} 
	} 
 
} 
 

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