@xingjs@20241226@添加查询管线工具类,修改解决查询站点接口token存在无法获取的情况，解决后可正常获取token，并查询出需要的站点...

xingjinshuang

2024-12-26 0272ac944a277bc7ed683bab2d1fb13fcfb58869

 src/main/java/com/se/simu/utils/CsvToSQLiteUtils.java

@@ -10,6 +10,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 +148,86 @@
            System.err.println("读取CSV或插入数据时出错: " + e.getMessage());
        }
    }


    /**
     * 读取 CSV 保存本地
     *
     * @param stationRainFile Station Rain 文件
     * @param tableName       表名称
     */
    public static void readCsvSaveLocal(String stationRainFile, 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(stationRainFile, conn, tableName);
                // 6. 提交事务
                conn.commit();
                System.out.println("数据成功插入到SQLite数据库！");
            }
        } catch (SQLException e) {
            System.err.println("SQLite连接失败: " + e.getMessage());
        }
    }


    private static void readCsvAndInsertDatas(String csvFilePath, Connection conn, String tableName) {
        // 使用 Apache Commons CSV 读取CSV文件
        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);

                    // 添加到批处理
                    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());
        }
    }

}