From 4fd3f760a30adf230f85b3e82d9a72af407266e7 Mon Sep 17 00:00:00 2001
From: 张洋洋 <10611411+yang-yang-z@user.noreply.gitee.com>
Date: 星期四, 20 二月 2025 11:14:01 +0800
Subject: [PATCH] [add]服务发布
---
src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java | 294 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 294 insertions(+), 0 deletions(-)
diff --git a/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java b/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
index a4c9c6e..85b398f 100644
--- a/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
+++ b/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java
@@ -1,5 +1,9 @@
package com.se.simu.utils;
+import com.alibaba.fastjson.JSON;
+import com.alibaba.fastjson.JSONArray;
+import com.alibaba.fastjson.JSONObject;
+import com.se.simu.domain.vo.StationRainVo;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVRecord;
@@ -9,7 +13,17 @@
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import java.util.Objects;
+/**
+ * CSV 鍒� SQLite 瀹炵敤绋嬪簭
+ *
+ * @author xingjinshuang@smartearth.cn
+ * @date 2024/12/26
+ */
public class CsvToSQLiteUtils {
private static final String DATABASE_URL = "jdbc:sqlite:D:\\0a_project\\simulation\\simuserver\\rainfall.db"; // SQLite鏁版嵁搴撹矾寰�
@@ -142,4 +156,284 @@
System.err.println("璇诲彇CSV鎴栨彃鍏ユ暟鎹椂鍑洪敊: " + e.getMessage());
}
}
+
+ /**
+ * 璇诲彇 CSV 淇濆瓨鏈湴
+ *
+ * @param array 鏁版嵁
+ * @param tableName 琛ㄥ悕绉�
+ */
+ public static void readCsvSaveLocal(JSONArray array, String tableName) {
+ // 1. 鍒涘缓SQLite鏁版嵁搴撹繛鎺�
+ try (Connection conn = DriverManager.getConnection("jdbc:sqlite:rainfall.db")) {
+ if (conn != null) {
+ // 2. 鍒涘缓琛紙濡傛灉涓嶅瓨鍦級
+ createTableIfNotExists(conn, tableName);
+ // 3. 璁剧疆SQLite杩炴帴鐨勫瓧绗︾紪鐮佷负UTF-8
+ try (Statement stmt = conn.createStatement()) {
+ // 璁剧疆SQLite缂栫爜涓篣TF-8
+ stmt.execute("PRAGMA encoding = 'UTF-8';");
+ }
+ // 4. 寮�濮嬩簨鍔�
+ conn.setAutoCommit(false);
+ // 5. 璇诲彇CSV鏂囦欢骞舵彃鍏ユ暟鎹�
+ readCsvAndInsertDatas(array, conn, tableName);
+ // 6. 鎻愪氦浜嬪姟
+ conn.commit();
+ System.out.println("鏁版嵁鎴愬姛鎻掑叆鍒癝QLite鏁版嵁搴擄紒");
+ }
+ } catch (SQLException e) {
+ System.err.println("SQLite杩炴帴澶辫触: " + e.getMessage());
+ }
+ }
+
+ /**
+ * 璇诲彇 CSV 淇濆瓨鏈湴
+ *
+ * @param stationRainFile Station Rain 鏂囦欢
+ * @param tableName 琛ㄥ悕绉�
+ */
+ public static JSONArray readCsvSaveLocal(String stationRainFile, String tableName) {
+ // 1. 鍒涘缓SQLite鏁版嵁搴撹繛鎺�
+ JSONArray array = null;
+ try (Connection conn = DriverManager.getConnection("jdbc:sqlite:rainfall.db")) {
+ if (conn != null) {
+ // 2. 鍒涘缓琛紙濡傛灉涓嶅瓨鍦級
+ createTableIfNotExists(conn, tableName);
+ // 3. 璁剧疆SQLite杩炴帴鐨勫瓧绗︾紪鐮佷负UTF-8
+ try (Statement stmt = conn.createStatement()) {
+ // 璁剧疆SQLite缂栫爜涓篣TF-8
+ stmt.execute("PRAGMA encoding = 'UTF-8';");
+ }
+ // 4. 寮�濮嬩簨鍔�
+ conn.setAutoCommit(false);
+ // 5. 璇诲彇CSV鏂囦欢骞舵彃鍏ユ暟鎹�
+ array = readCsvAndInsertDatas(stationRainFile, conn, tableName);
+ // 6. 鎻愪氦浜嬪姟
+ conn.commit();
+ System.out.println("鏁版嵁鎴愬姛鎻掑叆鍒癝QLite鏁版嵁搴擄紒");
+ }
+ } catch (SQLException e) {
+ System.err.println("SQLite杩炴帴澶辫触: " + e.getMessage());
+ }
+ return array;
+ }
+
+ private static void readCsvAndInsertDatas(JSONArray array, Connection conn, String tableName) {
+
+ String insertDataSql = "INSERT INTO " + tableName + " (station_name, rainfall, longitude, latitude, datetime) VALUES (?, ?, ?, ?, ?);";
+ try (PreparedStatement pstmt = conn.prepareStatement(insertDataSql)) {
+ // 鎵归噺澶у皬
+ int batchSize = 1000;
+ int count = 0;
+ for (int i = 0; i < array.size(); i++) {
+ JSONObject object = array.getJSONObject(i);
+ // 鑾峰彇姣忎竴鍒楃殑鏁版嵁
+ String stationName = object.getString("stationName");
+ double rainfall = object.getDouble("rainfall");
+ double longitude = object.getDouble("longitude");
+ double latitude = object.getDouble("latitude");
+ String datetime = object.getString("datetime");
+ // 璁剧疆鎻掑叆鏁版嵁鐨勫弬鏁�
+ pstmt.setString(1, stationName);
+ pstmt.setDouble(2, rainfall);
+ pstmt.setDouble(3, longitude);
+ pstmt.setDouble(4, latitude);
+ pstmt.setString(5, datetime);
+ // 娣诲姞鍒版壒澶勭悊
+ pstmt.addBatch();
+ count++;
+ // 姣廱atchSize鏉℃暟鎹墽琛屼竴娆℃壒閲忔彃鍏�
+ if (count % batchSize == 0) {
+ pstmt.executeBatch();
+ }
+ }
+ // 鎵ц鍓╀綑鐨勬壒閲忔彃鍏�
+ pstmt.executeBatch();
+ } catch (SQLException e) {
+ System.err.println("鎵归噺鎻掑叆鏁版嵁鏃跺嚭閿�: " + e.getMessage());
+ }
+ }
+
+ private static JSONArray readCsvAndInsertDatas(String csvFilePath, Connection conn, String tableName) {
+ // 浣跨敤 Apache Commons CSV 璇诲彇CSV鏂囦欢
+ JSONArray array = new JSONArray();
+ try (Reader reader = new InputStreamReader(Files.newInputStream(Paths.get(csvFilePath)), "GBK")) {
+ Iterable<CSVRecord> records = CSVFormat.DEFAULT
+ .withHeader("闆ㄩ噺绔�", "闄嶉洦閲�", "缁忓害", "绾害", "datatime")
+ .withSkipHeaderRecord() // 璺宠繃琛ㄥご
+ .parse(reader);
+
+ String insertDataSql = "INSERT INTO " + tableName + " (station_name, rainfall, longitude, latitude, datetime) VALUES (?, ?, ?, ?, ?);";
+ try (PreparedStatement pstmt = conn.prepareStatement(insertDataSql)) {
+ // 鎵归噺澶у皬
+ 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);
+ JSONObject jsonObject = new JSONObject();
+ jsonObject.put("stationName", stationName);
+ jsonObject.put("rainfall", rainfall);
+ jsonObject.put("longitude", longitude);
+ jsonObject.put("latitude", latitude);
+ jsonObject.put("datetime", datetime);
+ array.add(jsonObject);
+ // 娣诲姞鍒版壒澶勭悊
+ pstmt.addBatch();
+ count++;
+
+ // 姣廱atchSize鏉℃暟鎹墽琛屼竴娆℃壒閲忔彃鍏�
+ if (count % batchSize == 0) {
+ pstmt.executeBatch();
+ }
+ }
+ // 鎵ц鍓╀綑鐨勬壒閲忔彃鍏�
+ pstmt.executeBatch();
+ } catch (SQLException e) {
+ System.err.println("鎵归噺鎻掑叆鏁版嵁鏃跺嚭閿�: " + e.getMessage());
+ }
+ } catch (IOException e) {
+ System.err.println("璇诲彇CSV鎴栨彃鍏ユ暟鎹椂鍑洪敊: " + e.getMessage());
+ }
+ return array;
+ }
+
+ /**
+ * 鑾峰彇鎬昏
+ *
+ * @param tableName 琛ㄥ悕绉�
+ * @return {@link Double}
+ */
+ public static Double getTotal(String tableName) {
+ // 1. 鍒涘缓SQLite鏁版嵁搴撹繛鎺�
+ try (Connection conn = DriverManager.getConnection("jdbc:sqlite:rainfall.db")) {
+ if (conn != null) {
+ // 2. 鎵цSQL鏌ヨ
+ String queryDataSql = "select sum(rainfall_difference) as total from(SELECT ABS( (SELECT rainfall FROM " + tableName + " WHERE station_name = s.station_name ORDER BY datetime ASC LIMIT 1) - (SELECT rainfall FROM " + tableName + " WHERE station_name = s.station_name ORDER BY datetime DESC LIMIT 1)) AS rainfall_difference FROM " + tableName + " s GROUP BY station_name)";
+ // 3. 澶勭悊鏌ヨ缁撴灉
+ try (PreparedStatement pstmt = conn.prepareStatement(queryDataSql)) {
+ ResultSet rs = pstmt.executeQuery();
+ while (rs.next()) {
+ // 鑾峰彇鎬诲拰
+ return rs.getDouble("total");
+ }
+ } catch (SQLException e) {
+ System.err.println("鏌ヨ鏁版嵁鏃跺嚭閿�: " + e.getMessage());
+ }
+ }
+ } catch (SQLException e) {
+ System.err.println("SQLite杩炴帴澶辫触: " + e.getMessage());
+ }
+ return null;
+ }
+
+ /**
+ * @param tableName 琛ㄥ悕绉�
+ * @return {@link Integer}
+ */
+ public static Integer getDuration(String tableName) throws SQLException {
+ // // 1. 杩炴帴鏁版嵁搴�
+ // try (Connection conn = DriverManager.getConnection("jdbc:sqlite:rainfall.db")) {
+ // // 2. 鎵ц鏌ヨ璇彞
+ // String queryDataSql = "SELECT duration FROM " + tableName;
+ // try (PreparedStatement pstmt = conn.prepareStatement(queryDataSql)) {
+ // ResultSet rs = pstmt.executeQuery();
+ // while (rs.next()) {
+ // // 鑾峰彇鎬诲拰
+ // return rs.getInt("duration");
+ // }
+ // } catch (SQLException e) {
+ // throw new RuntimeException(e);
+ // }
+ // } catch (SQLException e) {
+ // System.err.println("SQLite杩炴帴澶辫触: " + e.getMessage());
+ // }
+
+ // 鐩墠鍏堟牴鎹枃浠剁殑鍐呭锛屾墜鍔ㄧ粰鍊硷紝鍚庣画浣跨敤瑙ｆ瀽鏂囦欢鍐呭涓殑鏃堕棿
+ // TODO: 2024/12/27 鐩墠鍏堟牴鎹枃浠剁殑鍐呭锛屾墜鍔ㄧ粰鍊硷紝鎻愰珮澶勭悊閫熷害銆傚悗缁娇鐢ㄨВ鏋愭枃浠跺唴瀹逛腑鐨勬椂闂淬��
+ return 1440;
+ }
+
+ /**
+ * 鍒嗙粍鏌ヨ
+ *
+ * @param tableName 琛ㄥ悕绉�
+ * @return {@link Double}
+ */
+ public static List<String> getNameList(String tableName) {
+ // 1. 鍒涘缓SQLite鏁版嵁搴撹繛鎺�
+ try (Connection conn = DriverManager.getConnection("jdbc:sqlite:rainfall.db")) {
+ if (conn != null) {
+ // 2. 鎵цSQL鏌ヨ
+ String queryDataSql = "SELECT station_name FROM " + tableName + " GROUP BY station_name";
+ // 3. 澶勭悊鏌ヨ缁撴灉
+ try (PreparedStatement pstmt = conn.prepareStatement(queryDataSql)) {
+ ResultSet rs = pstmt.executeQuery();
+ List<String> list = new ArrayList<>();
+ while (rs.next()) {
+ // 鑾峰彇鎬诲拰
+ list.add(rs.getString("station_name"));
+ }
+ return list;
+ } catch (SQLException e) {
+ System.err.println("鏌ヨ鏁版嵁鏃跺嚭閿�: " + e.getMessage());
+ }
+ }
+ } catch (SQLException e) {
+ System.err.println("SQLite杩炴帴澶辫触: " + e.getMessage());
+ }
+ return null;
+ }
+
+ /**
+ * 鍒嗙粍鏌ヨ
+ *
+ * @param tableName 琛ㄥ悕绉�
+ * @return {@link Double}
+ */
+ public static List<StationRainVo> getList(String tableName, String name) {
+ // 1. 鍒涘缓SQLite鏁版嵁搴撹繛鎺�
+ try (Connection conn = DriverManager.getConnection("jdbc:sqlite:rainfall.db")) {
+ if (conn != null) {
+ // 2. 鎵цSQL鏌ヨ
+ String queryDataSql = "SELECT * FROM " + tableName + " WHERE station_name='" + name + "' ORDER BY id asc";
+ // 3. 澶勭悊鏌ヨ缁撴灉
+ try (PreparedStatement pstmt = conn.prepareStatement(queryDataSql)) {
+ ResultSet rs = pstmt.executeQuery();
+ List<StationRainVo> list = new ArrayList<>();
+ StationRainVo vo;
+ while (rs.next()) {
+ vo = new StationRainVo();
+ vo.setId(rs.getLong("id"));
+ vo.setStationName(rs.getString("station_name"));
+ vo.setRainfall(rs.getDouble("rainfall"));
+ vo.setLatitude(rs.getDouble("latitude"));
+ vo.setLongitude(rs.getDouble("longitude"));
+ vo.setDatetime(rs.getString("datetime"));
+ list.add(vo);
+ }
+ return list;
+ } catch (SQLException e) {
+ System.err.println("鏌ヨ鏁版嵁鏃跺嚭閿�: " + e.getMessage());
+ }
+ }
+ } catch (SQLException e) {
+ System.err.println("SQLite杩炴帴澶辫触: " + e.getMessage());
+ }
+ return null;
+ }
+
}
--
Gitblit v1.9.3