java csv mysql数据库_将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();

}

}

}

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

)