From 6cb2134a53422f471f4f9b77c34d67e1fb4d31db Mon Sep 17 00:00:00 2001 From: 张洋洋 <10611411+yang-yang-z@user.noreply.gitee.com> Date: 星期四, 09 一月 2025 10:16:56 +0800 Subject: [PATCH] [add]h5读取改造 --- src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java | 157 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 157 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..d4df8b0 100644 --- a/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java +++ b/src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java @@ -1,5 +1,8 @@ package com.se.simu.utils; +import com.alibaba.fastjson.JSON; +import com.alibaba.fastjson.JSONArray; +import com.alibaba.fastjson.JSONObject; import org.apache.commons.csv.CSVFormat; import org.apache.commons.csv.CSVRecord; @@ -10,6 +13,12 @@ import java.nio.file.Paths; import java.sql.*; +/** + * 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 +151,152 @@ System.err.println("璇诲彇CSV鎴栨彃鍏ユ暟鎹椂鍑洪敊: " + 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 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()); + // } + + // 鐩墠鍏堟牴鎹枃浠剁殑鍐呭锛屾墜鍔ㄧ粰鍊硷紝鍚庣画浣跨敤瑙f瀽鏂囦欢鍐呭涓殑鏃堕棿 + // TODO: 2024/12/27 鐩墠鍏堟牴鎹枃浠剁殑鍐呭锛屾墜鍔ㄧ粰鍊硷紝鎻愰珮澶勭悊閫熷害銆傚悗缁娇鐢ㄨВ鏋愭枃浠跺唴瀹逛腑鐨勬椂闂淬�� + return 1440; + } + } -- Gitblit v1.9.3