| | |
|---|
| | | 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; |
|---|
| | | |
|---|
| | |
|---|
| | | 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数据库路径 |
|---|
| | |
|---|
| | | 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编码为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 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 datetime 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; |
|---|
| | | } |
|---|
| | | |
|---|
| | | } |
|---|
