DataAcquisitionPlus/src/main/java/com/acquisition/config/KeyTableFile.java

package com.acquisition.config;

import org.springframework.stereotype.Service;

import java.io.File;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Service
public class KeyTableFile {
    // 生成keytable.sqlite3文件
    public void generateKeyTableFile(String dataFolderPath, List<List<Object>> configExcelData, int startCol, int endCol, int dbName) throws Exception {
        // 提取指定列范围的数据
        List<List<Object>> dataToWrite = new ArrayList<>();

        // 从configExcelData中获取第startCol到endCol列的数据
        for (List<Object> row : configExcelData) {
            // 只取指定范围的列
            List<Object> rowData = row.subList(startCol, endCol + 1); // 提取列范围的数据
            dataToWrite.add(rowData);
        }

        // 根据提取的数据生成sqlite3文件
        String keytableDir = dataFolderPath + "\\keytable";
        File directory = new File(keytableDir);
        if (directory.exists() && directory.isDirectory()) {
            File[] files = directory.listFiles();
            if (files != null) {
                for (File file : files) {
                    if (file.isFile()) {
                        file.delete();
                    }
                }
            }
        }
        File keytableDirFile = new File(keytableDir);
        if (!keytableDirFile.exists()) {
            keytableDirFile.mkdir(); // 创建 keytable 目录
        }
        String sqliteFilePath = null;
        if (dbName == 1) {
            sqliteFilePath = keytableDir + "\\keytable.sqlite3";
        } else if (dbName == 2) {
            sqliteFilePath = keytableDir + "\\gddlly.db";
        }

        // 写入SQLite数据库
        writeToSQLite(dataToWrite, sqliteFilePath, dbName);
    }


    // 写入SQLite数据库
//    /**
//     * 读取配置Excel文件并返回数据列表
//     *所有字段数据类型为整数
//     * @return 数据列表
//     * @throws Exception 读取文件时发生的异常
//     */
//    private void writeToSQLite(List<List<String>> data, String sqliteFilePath) {
//        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:" + sqliteFilePath)) {
//            // 获取列名（假设第一行是列名）
//            List<String> columnNames = data.get(0); // 第一行作为列名
//
//            // 创建表格
//            String createTableSQL = "CREATE TABLE IF NOT EXISTS key_table (";
//
//            // 检查每列的数据类型
//            for (int i = 0; i < columnNames.size(); i++) {
//                String columnType = "TEXT"; // 默认列类型为 TEXT
//
//                // 遍历当前列的所有数据来检查是否包含数字
//                for (int rowIdx = 1; rowIdx < data.size(); rowIdx++) { // 从第二行开始检查数据
//                    String cellValue = data.get(rowIdx).get(i).trim();
//                    // 如果列中任何数据是数字，将列的数据类型设置为 INTEGER
//                    if (cellValue.matches("-?\\d+(\\.\\d+)?")) { // 允许整数或浮动数字
//                        columnType = "INTEGER";
//                        break;
//                    }
//                }
//
//                createTableSQL += columnNames.get(i) + " " + columnType; // 使用原始列名和类型
//                if (i < columnNames.size() - 1) {
//                    createTableSQL += ", ";
//                }
//            }
//            createTableSQL += ");";
//
//            // 执行创建表格语句
//            try (Statement stmt = conn.createStatement()) {
//                stmt.execute(createTableSQL);
//            }
//
//            // 插入数据
//            StringBuilder insertSQL = new StringBuilder("INSERT INTO key_table VALUES (");
//            for (int i = 0; i < columnNames.size() - 1; i++) {
//                insertSQL.append("?, ");
//            }
//            insertSQL.append("?);"); // 最后一列不需要逗号
//
//            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL.toString())) {
//                for (int rowIdx = 1; rowIdx < data.size(); rowIdx++) { // 从第二行开始处理数据
//                    List<String> row = data.get(rowIdx);
//                    for (int colIdx = 0; colIdx < row.size(); colIdx++) {
//                        String value = row.get(colIdx).trim();
//
//                        // 判断值是否为数字并设定相应的数据类型
//                        if (value.matches("-?\\d+(\\.\\d+)?")) {
//                            // 如果是浮动数字，转换为整数
//                            try {
//                                double numericValue = Double.parseDouble(value);
//                                // 如果是浮动数字，四舍五入并转为整数
//                                if (numericValue % 1 != 0) {
//                                    pstmt.setInt(colIdx + 1, (int) Math.round(numericValue)); // 插入整数
//                                } else {
//                                    pstmt.setInt(colIdx + 1, (int) numericValue); // 插入整数
//                                }
//                            } catch (NumberFormatException e) {
//                                pstmt.setString(colIdx + 1, value); // 如果无法解析为数字，插入为字符串
//                            }
//                        } else {
//                            pstmt.setString(colIdx + 1, value); // 插入文本
//                        }
//                    }
//                    pstmt.addBatch(); // 添加批处理
//                }
//                pstmt.executeBatch(); // 执行批量插入
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }


//    /**
//     * 读取配置Excel文件并返回数据列表
//     *指定字段数据类型
//     * @return 数据列表
//     * @throws Exception 读取文件时发生的异常
//     */
//    private void writeToSQLite(List<List<String>> data, String sqliteFilePath) {
//        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:" + sqliteFilePath)) {
//            List<String> columnNames = data.get(0); // 第一行作为列名
//
//            // 创建表格（表名改为 key_table）
//            String createTableSQL = "CREATE TABLE IF NOT EXISTS key_table (";
//            for (int i = 0; i < columnNames.size(); i++) {
//                String colName = columnNames.get(i);
//                String columnType = "TEXT";
//
//                for (int rowIdx = 1; rowIdx < data.size(); rowIdx++) {
//                    String cellValue = data.get(rowIdx).get(i).trim();
//                    if (cellValue.matches("-?\\d+(\\.\\d+)?")) {
//                        if (colName.endsWith("valid") ||colName.endsWith("addr")) {
//                            columnType = "INTEGER";
//                        } else {
//                            columnType = "REAL";
//                        }
//                        break;
//                    }
//                }
//
//                createTableSQL += colName + " " + columnType;
//                if (i < columnNames.size() - 1) {
//                    createTableSQL += ", ";
//                }
//            }
//            createTableSQL += ");";
//
//            try (Statement stmt = conn.createStatement()) {
//                stmt.execute(createTableSQL);
//            }
//
//            // 插入数据
//            StringBuilder insertSQL = new StringBuilder("INSERT INTO key_table VALUES (");
//            for (int i = 0; i < columnNames.size() - 1; i++) {
//                insertSQL.append("?, ");
//            }
//            insertSQL.append("?);");
//
//            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL.toString())) {
//                for (int rowIdx = 1; rowIdx < data.size(); rowIdx++) {
//                    List<String> row = data.get(rowIdx);
//                    for (int colIdx = 0; colIdx < row.size(); colIdx++) {
//                        String colName = columnNames.get(colIdx);
//                        String value = row.get(colIdx).trim();
//
//                        if (value.matches("-?\\d+(\\.\\d+)?")) {
//                            try {
//                                double numericValue = Double.parseDouble(value);
//
//                                if (colName.endsWith("valid")) {
//                                    pstmt.setInt(colIdx + 1, (int) Math.round(numericValue));
//                                } else {
//                                    double rounded = Math.round(numericValue * 10.0) / 10.0;
//                                    pstmt.setDouble(colIdx + 1, rounded);
//                                }
//                            } catch (NumberFormatException e) {
//                                pstmt.setString(colIdx + 1, value);
//                            }
//                        } else {
//                            pstmt.setString(colIdx + 1, value);
//                        }
//                    }
//                    pstmt.addBatch();
//                }
//                pstmt.executeBatch();
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }


    //    /**
    //     * 读取配置Excel文件并返回数据列表
    //     *自动判断字段数据类型
    //     * @return 数据列表
    //     * @throws Exception 读取文件时发生的异常
    //     */
    public void writeToSQLite(List<List<Object>> data, String sqliteFilePath, int dbName) {
        if (data == null || data.isEmpty()) return;

        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:" + sqliteFilePath)) {
            List<Object> columnNamesRaw = data.get(0);
            List<String> columnNames = new ArrayList<>();
            for (Object obj : columnNamesRaw) {
                columnNames.add(obj != null ? obj.toString().trim() : "col");
            }

            List<String> columnTypes = new ArrayList<>();

            // 推断每列数据类型
            for (int col = 0; col < columnNames.size(); col++) {
                String type = "TEXT"; // 默认是文本

                for (int row = 1; row < data.size(); row++) {
                    if (col >= data.get(row).size()) continue;

                    Object value = data.get(row).get(col);
                    if (value == null) continue;

                    if (value instanceof Integer || value instanceof Long) {
                        type = "INTEGER";
                    } else if (value instanceof Double || value instanceof Float) {
                        type = "REAL";
                    } else if (value instanceof Boolean) {
                        type = "INTEGER"; // SQLite 没有 BOOLEAN 类型，用 INTEGER 表示 true/false
                    } else if (value instanceof Date) {
                        type = "TEXT"; // 或 "NUMERIC"，取决于是否存时间戳
                    } else {
                        type = "TEXT";
                        break; // 只要出现非数字，就默认整列是 TEXT
                    }
                }

                columnTypes.add(type);
            }

            // 创建表
            StringBuilder createTableSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS key_table (");
            if (dbName == 1) {
                createTableSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS key_table (");

            } else if (dbName == 2) {
                createTableSQL = new StringBuilder("CREATE TABLE IF NOT EXISTS point (");
            }

            for (int i = 0; i < columnNames.size(); i++) {
                createTableSQL.append(columnNames.get(i)).append(" ").append(columnTypes.get(i));
                if (i < columnNames.size() - 1) createTableSQL.append(", ");
            }
            createTableSQL.append(");");

            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL.toString());
            }

            // 插入数据
            StringBuilder insertSQL = new StringBuilder("INSERT INTO key_table VALUES (");
            if (dbName == 1) {
                insertSQL = new StringBuilder("INSERT INTO key_table VALUES (");
            } else if (dbName == 2) {
                insertSQL = new StringBuilder("INSERT INTO point VALUES (");
            }
            for (int i = 0; i < columnNames.size(); i++) {
                insertSQL.append("?");
                if (i < columnNames.size() - 1) insertSQL.append(",");
            }
            insertSQL.append(");");

            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL.toString())) {
                for (int rowIdx = 1; rowIdx < data.size(); rowIdx++) {
                    List<Object> row = data.get(rowIdx);
                    for (int colIdx = 0; colIdx < columnNames.size(); colIdx++) {
                        Object value = colIdx < row.size() ? row.get(colIdx) : null;
                        String type = columnTypes.get(colIdx);

                        if (value == null) {
                            pstmt.setNull(colIdx + 1, Types.NULL);
                        } else if (type.equals("INTEGER")) {
                            if (value instanceof Boolean) {
                                pstmt.setInt(colIdx + 1, ((Boolean) value) ? 1 : 0);
                            } else {
                                pstmt.setInt(colIdx + 1, ((Number) value).intValue());
                            }
                        } else if (type.equals("REAL")) {
                            pstmt.setDouble(colIdx + 1, ((Number) value).doubleValue());
                        } else if (type.equals("TEXT")) {
                            if (value instanceof Date) {
                                pstmt.setString(colIdx + 1, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) value));
                            } else {
                                pstmt.setString(colIdx + 1, value.toString());
                            }
                        } else {
                            pstmt.setObject(colIdx + 1, value);
                        }
                    }
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}