将CSV文件导进MySQL表格的Java示例
发布时间:2020-12-15 00:12:02 所属栏目:Java 来源:网络整理
导读:今天PHP站长网 52php.cn把收集自互联网的代码分享给大家,仅供参考。 将CSV文件中的数据导进MySQL表格的Java示例 ImportCsv.java: package com.examples;import java.io.FileReader;import java.sql.Connection;import ja
以下代码由PHP站长网 52php.cn收集自互联网 现在PHP站长网小编把它分享给大家,仅供参考
将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,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 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`) ) 以上内容由PHP站长网【52php.cn】收集整理供大家参考研究 如果以上内容对您有帮助,欢迎收藏、点赞、推荐、分享。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |