simuserver.git - Gitblit

			@@ -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编码为UTF-8
			stmt.execute("PRAGMA encoding = 'UTF-8';");
			}
			// 4. 开始事务
			conn.setAutoCommit(false);
			// 5. 读取CSV文件并插入数据
			readCsvAndInsertDatas(array, conn, tableName);
			// 6. 提交事务
			conn.commit();
			System.out.println("数据成功插入到SQLite数据库！");
			}
			} 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编码为UTF-8
			stmt.execute("PRAGMA encoding = 'UTF-8';");
			}
			// 4. 开始事务
			conn.setAutoCommit(false);
			// 5. 读取CSV文件并插入数据
			array = readCsvAndInsertDatas(stationRainFile, conn, tableName);
			// 6. 提交事务
			conn.commit();
			System.out.println("数据成功插入到SQLite数据库！");
			}
			} 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++;
			// 每batchSize条数据执行一次批量插入
			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++;

			// 每batchSize条数据执行一次批量插入
			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;
			}

			}