@xingjs@20241224@添加解析CSV文件相关实体类、方法

xingjinshuang

2024-12-24 3a45bed34b94937f3c3bb184e6c81d374aa837dd

@xingjs@20241224@添加解析CSV文件相关实体类、方法

 pom.xml

@@ -176,6 +176,19 @@
            <version>3.3.1</version>
        </dependency>

        <!-- SQLite JDBC -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.42.0.0</version>  <!-- æ ¹æ®éœ€è¦è°ƒæ•´ç‰ˆæœ¬ -->
        </dependency>

        <!-- Apache Commons CSV (可选) -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-csv</artifactId>
            <version>1.8</version> <!-- æ ¹æ®éœ€è¦è°ƒæ•´ç‰ˆæœ¬ -->
        </dependency>


    </dependencies>

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

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,145 @@
package com.se.simu.utils;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVRecord;

import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;

public class CsvToSQLiteUtils {

    private static final String DATABASE_URL = "jdbc:sqlite:D:\\0a_project\\simulation\\simuserver\\rainfall.db"; // SQLite数据库路径
    private static final String CREATE_TABLE_SQL =
            "CREATE TABLE IF NOT EXISTS rainfall (" +
                    "    id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "    station_name TEXT NOT NULL," +
                    "    rainfall REAL NOT NULL," +
                    "    longitude REAL NOT NULL," +
                    "    latitude REAL NOT NULL," +
                    "    datetime TEXT NOT NULL" +
                    ");";

    private static final String INSERT_DATA_SQL =
            "INSERT INTO rainfall (station_name, rainfall, longitude, latitude, datetime) " +
                    "VALUES (?, ?, ?, ?, ?);";

    public static void main(String[] args) {
        // CSV文件路径
        String csvFilePath = "D:\\0a_project\\model\\shp\\雨量站点数据\\雨量站包含坐标.csv";

        // 1. åˆ›å»ºSQLite数据库连接
        try (Connection conn = DriverManager.getConnection(DATABASE_URL)) {
            if (conn != null) {
                // èŽ·å–当前时间戳
                Timestamp timestamp = new Timestamp(System.currentTimeMillis());
                String tableName = "rainfall_" + timestamp.getTime();
                System.out.println("tableName = " + tableName);
                // 2. åˆ›å»ºè¡¨
                createTableIfNotExists(conn, tableName);

                // 3. è®¾ç½® SQLite è¿žæŽ¥çš„字符编码为 UTF-8
                try (Statement stmt = conn.createStatement()) {
                    stmt.execute("PRAGMA encoding = 'UTF-8';");  // è®¾ç½®SQLite编码为UTF-8
                }

                // 4. å¼€å§‹äº‹åŠ¡
                conn.setAutoCommit(false);

                // 5. è¯»å–CSV文件并插入数据
                readCsvAndInsertData(csvFilePath, conn, tableName);

                // 6. æäº¤äº‹åŠ¡
                conn.commit();

                System.out.println("数据成功插入到SQLite数据库！");
            }
        } catch (SQLException e) {
            System.err.println("SQLite连接失败: " + e.getMessage());
        }
    }

    /**
     * åˆ›å»ºè¡¨ï¼Œå¦‚果不存在的话
     */
    private static void createTableIfNotExists(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(CREATE_TABLE_SQL);
        }
    }

    private static void createTableIfNotExists(Connection conn, String tableName) throws SQLException {

        try (Statement stmt = conn.createStatement()) {
            String CREATE_TABLE_SQL =
                    "CREATE TABLE IF NOT EXISTS " + tableName + " (" +
                            "    id INTEGER PRIMARY KEY AUTOINCREMENT," +
                            "    station_name TEXT NOT NULL," +
                            "    rainfall REAL NOT NULL," +
                            "    longitude REAL NOT NULL," +
                            "    latitude REAL NOT NULL," +
                            "    datetime TEXT NOT NULL" +
                            ");";
            stmt.execute(CREATE_TABLE_SQL);
        }
    }

    /**
     * è¯»å–CSV文件并将数据插入到SQLite数据库
     *
     * @param csvFilePath CSV文件路径
     * @param conn        SQLite数据库连接
     */
    private static void readCsvAndInsertData(String csvFilePath, Connection conn, String tableName) {
        // ä½¿ç”¨ Apache Commons CSV è¯»å–CSV文件
        try (Reader reader = new InputStreamReader(Files.newInputStream(Paths.get(csvFilePath)), "GBK")) {
//        try (Reader reader = new InputStreamReader(Files.newInputStream(Paths.get(csvFilePath)), StandardCharsets.UTF_8)) {
            Iterable<CSVRecord> records = CSVFormat.DEFAULT
                    .withHeader("雨量站", "降雨量", "经度", "纬度", "datatime")
                    .withSkipHeaderRecord() // è·³è¿‡è¡¨å¤´
                    .parse(reader);

            String INSERT_DATA_SQL =
                    "INSERT INTO " + tableName + " (station_name, rainfall, longitude, latitude, datetime) " +
                            "VALUES (?, ?, ?, ?, ?);";

            // å‡†å¤‡SQL插入语句
            try (PreparedStatement pstmt = conn.prepareStatement(INSERT_DATA_SQL)) {
                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++;

                    // æ¯1000条数据执行一次批处理
                    if (count % batchSize == 0) {
                        pstmt.executeBatch();  // æ‰§è¡Œæ‰¹é‡æ’å…¥
                    }
                }
                // æ‰§è¡Œå‰©ä½™çš„批量插入
                pstmt.executeBatch();
            }
        } catch (IOException | SQLException e) {
            System.err.println("读取CSV或插入数据时出错: " + e.getMessage());
        }
    }
}