From 3a45bed34b94937f3c3bb184e6c81d374aa837dd Mon Sep 17 00:00:00 2001
From: xingjinshuang <xingjs@qq.com>
Date: 星期二, 24 十二月 2024 14:20:53 +0800
Subject: [PATCH] @xingjs@20241224@添加解析CSV文件相关实体类、方法
---
src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java | 145 ++++++++++++++++++++++++++++++++++++++++++++++++
pom.xml | 13 ++++
2 files changed, 158 insertions(+), 0 deletions(-)
diff --git a/pom.xml b/pom.xml
index 101c53a..872fdee 100644
--- a/pom.xml
+++ b/pom.xml
@@ -176,6 +176,19 @@
<version>3.3.1</version>
</dependency>
+ <!-- SQLite JDBC -->
+ <dependency>
+ <groupId>org.xerial</groupId>
+ <artifactId>sqlite-jdbc</artifactId>
+ <version>3.42.0.0</version> <!-- 鏍规嵁闇�瑕佽皟鏁寸増鏈� -->
+ </dependency>
+
+ <!-- Apache Commons CSV (鍙��) -->
+ <dependency>
+ <groupId>org.apache.commons</groupId>
+ <artifactId>commons-csv</artifactId>
+ <version>1.8</version> <!-- 鏍规嵁闇�瑕佽皟鏁寸増鏈� -->
+ </dependency>
</dependencies>
diff --git a/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java b/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
new file mode 100644
index 0000000..a4c9c6e
--- /dev/null
+++ b/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
@@ -0,0 +1,145 @@
+package com.se.simu.utils;
+
+import org.apache.commons.csv.CSVFormat;
+import org.apache.commons.csv.CSVRecord;
+
+import java.io.IOException;
+import java.io.InputStreamReader;
+import java.io.Reader;
+import java.nio.file.Files;
+import java.nio.file.Paths;
+import java.sql.*;
+
+public class CsvToSQLiteUtils {
+
+ private static final String DATABASE_URL = "jdbc:sqlite:D:\\0a_project\\simulation\\simuserver\\rainfall.db"; // SQLite鏁版嵁搴撹矾寰�
+ private static final String CREATE_TABLE_SQL =
+ "CREATE TABLE IF NOT EXISTS rainfall (" +
+ " id INTEGER PRIMARY KEY AUTOINCREMENT," +
+ " station_name TEXT NOT NULL," +
+ " rainfall REAL NOT NULL," +
+ " longitude REAL NOT NULL," +
+ " latitude REAL NOT NULL," +
+ " datetime TEXT NOT NULL" +
+ ");";
+
+ private static final String INSERT_DATA_SQL =
+ "INSERT INTO rainfall (station_name, rainfall, longitude, latitude, datetime) " +
+ "VALUES (?, ?, ?, ?, ?);";
+
+ public static void main(String[] args) {
+ // CSV鏂囦欢璺緞
+ String csvFilePath = "D:\\0a_project\\model\\shp\\闆ㄩ噺绔欑偣鏁版嵁\\闆ㄩ噺绔欏寘鍚潗鏍�.csv";
+
+ // 1. 鍒涘缓SQLite鏁版嵁搴撹繛鎺�
+ try (Connection conn = DriverManager.getConnection(DATABASE_URL)) {
+ if (conn != null) {
+ // 鑾峰彇褰撳墠鏃堕棿鎴�
+ Timestamp timestamp = new Timestamp(System.currentTimeMillis());
+ String tableName = "rainfall_" + timestamp.getTime();
+ System.out.println("tableName = " + tableName);
+ // 2. 鍒涘缓琛�
+ createTableIfNotExists(conn, tableName);
+
+ // 3. 璁剧疆 SQLite 杩炴帴鐨勫瓧绗︾紪鐮佷负 UTF-8
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute("PRAGMA encoding = 'UTF-8';"); // 璁剧疆SQLite缂栫爜涓篣TF-8
+ }
+
+ // 4. 寮�濮嬩簨鍔�
+ conn.setAutoCommit(false);
+
+ // 5. 璇诲彇CSV鏂囦欢骞舵彃鍏ユ暟鎹�
+ readCsvAndInsertData(csvFilePath, conn, tableName);
+
+ // 6. 鎻愪氦浜嬪姟
+ conn.commit();
+
+ System.out.println("鏁版嵁鎴愬姛鎻掑叆鍒癝QLite鏁版嵁搴擄紒");
+ }
+ } catch (SQLException e) {
+ System.err.println("SQLite杩炴帴澶辫触: " + e.getMessage());
+ }
+ }
+
+ /**
+ * 鍒涘缓琛紝濡傛灉涓嶅瓨鍦ㄧ殑璇�
+ */
+ private static void createTableIfNotExists(Connection conn) throws SQLException {
+ try (Statement stmt = conn.createStatement()) {
+ stmt.execute(CREATE_TABLE_SQL);
+ }
+ }
+
+ private static void createTableIfNotExists(Connection conn, String tableName) throws SQLException {
+
+ try (Statement stmt = conn.createStatement()) {
+ String CREATE_TABLE_SQL =
+ "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
+ " id INTEGER PRIMARY KEY AUTOINCREMENT," +
+ " station_name TEXT NOT NULL," +
+ " rainfall REAL NOT NULL," +
+ " longitude REAL NOT NULL," +
+ " latitude REAL NOT NULL," +
+ " datetime TEXT NOT NULL" +
+ ");";
+ stmt.execute(CREATE_TABLE_SQL);
+ }
+ }
+
+ /**
+ * 璇诲彇CSV鏂囦欢骞跺皢鏁版嵁鎻掑叆鍒癝QLite鏁版嵁搴�
+ *
+ * @param csvFilePath CSV鏂囦欢璺緞
+ * @param conn SQLite鏁版嵁搴撹繛鎺�
+ */
+ private static void readCsvAndInsertData(String csvFilePath, Connection conn, String tableName) {
+ // 浣跨敤 Apache Commons CSV 璇诲彇CSV鏂囦欢
+ try (Reader reader = new InputStreamReader(Files.newInputStream(Paths.get(csvFilePath)), "GBK")) {
+// try (Reader reader = new InputStreamReader(Files.newInputStream(Paths.get(csvFilePath)), StandardCharsets.UTF_8)) {
+ Iterable<CSVRecord> records = CSVFormat.DEFAULT
+ .withHeader("闆ㄩ噺绔�", "闄嶉洦閲�", "缁忓害", "绾害", "datatime")
+ .withSkipHeaderRecord() // 璺宠繃琛ㄥご
+ .parse(reader);
+
+ String INSERT_DATA_SQL =
+ "INSERT INTO " + tableName + " (station_name, rainfall, longitude, latitude, datetime) " +
+ "VALUES (?, ?, ?, ?, ?);";
+
+ // 鍑嗗SQL鎻掑叆璇彞
+ try (PreparedStatement pstmt = conn.prepareStatement(INSERT_DATA_SQL)) {
+ int batchSize = 1000; // 姣忔壒鎻掑叆鐨勬暟閲�
+ int count = 0;
+
+ for (CSVRecord record : records) {
+ // 鑾峰彇姣忎竴鍒楃殑鏁版嵁
+ String stationName = record.get("闆ㄩ噺绔�");
+ double rainfall = Double.parseDouble(record.get("闄嶉洦閲�"));
+ double longitude = Double.parseDouble(record.get("缁忓害"));
+ double latitude = Double.parseDouble(record.get("绾害"));
+ String datetime = record.get("datatime");
+
+ // 璁剧疆鎻掑叆鏁版嵁鐨勫弬鏁�
+ pstmt.setString(1, stationName);
+ pstmt.setDouble(2, rainfall);
+ pstmt.setDouble(3, longitude);
+ pstmt.setDouble(4, latitude);
+ pstmt.setString(5, datetime);
+
+ // 鎵归噺娣诲姞鍒版壒澶勭悊涓�
+ pstmt.addBatch();
+ count++;
+
+ // 姣�1000鏉℃暟鎹墽琛屼竴娆℃壒澶勭悊
+ if (count % batchSize == 0) {
+ pstmt.executeBatch(); // 鎵ц鎵归噺鎻掑叆
+ }
+ }
+ // 鎵ц鍓╀綑鐨勬壒閲忔彃鍏�
+ pstmt.executeBatch();
+ }
+ } catch (IOException | SQLException e) {
+ System.err.println("璇诲彇CSV鎴栨彃鍏ユ暟鎹椂鍑洪敊: " + e.getMessage());
+ }
+ }
+}
--
Gitblit v1.9.3