SaveResult.java

package heyingzhe;

import java.io.*;
import java.sql.*;

public class SaveResult {
    private String dbURL = "jdbc:mysql://localhost:3306"; //数据库链接
    private String dbUserName = "heyingzhe"; //mysql用户名
    private String dbPassword = "1234"; //mysql密码
    private String dbName = "result"; //数据库名字
    private String filePath; //文件路径
    private String tableName; //表的名字

    // 把数组保存在指定路径下，保存为asc文件格式
    public void saveArrayToAscFile(double[][] data, String directory, String ascName) throws IOException {
        int nrows = data.length;            // 行数
        int ncols = data[0].length;         // 列数
        double xllcorner = 116.39779761748; // 假设左下角的 X 坐标
        double yllcorner = 25.398380600171; // 假设左下角的 Y 坐标
        double cellsize = 0.0020154416431345; // 单元格大小
        double nodataValue = -9999;         // 无效数据值

        // 创建文件写入对象
        BufferedWriter writer = new BufferedWriter(new FileWriter(directory + "\\" + ascName));

        // 写入元数据
        writer.write("ncols         " + ncols + "\n");
        writer.write("nrows         " + nrows + "\n");
        writer.write("xllcorner     " + xllcorner + "\n");
        writer.write("yllcorner     " + yllcorner + "\n");
        writer.write("cellsize      " + cellsize + "\n");
        writer.write("NODATA_value  " + nodataValue + "\n");

        // 写入数组数据
        for (int i = 0; i < nrows; i++) {
            // 创建当前行的字符串
            StringBuilder line = new StringBuilder();
            for (int j = 0; j < ncols; j++) {
                line.append(data[i][j]);
                if (j < ncols - 1) {
                    line.append(" "); // 数据间用空格隔开
                }
            }
            writer.write(line.toString() + "\n"); // 写入行数据并换行
        }

        // 关闭文件写入器
        writer.close();
    }

    // 设置SQL数据库属性（用户名，密码）
    public void setMySQLProperty(String dbUserName, String dbPassword) {
        this.dbUserName = dbUserName;
        this.dbPassword = dbPassword;
    }

    // 创建数据库
    public void createDatabase(String dbName) throws SQLException, ClassNotFoundException {
        String url = dbURL + "/" + dbName;
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(dbURL, dbUserName, dbPassword);
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS " + dbName);
        this.dbName = dbName;
        stmt.close();
        conn.close();
    }

    // 创建表
    public void createTable(String dbName, String tableName) throws SQLException, ClassNotFoundException, IOException {
        String url = dbURL + "/" + dbName;
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, dbUserName, dbPassword);
        Statement stmt = conn.createStatement();

        // 创建SQL语句
        StringBuilder sql = new StringBuilder("CREATE TABLE IF NOT EXISTS " + tableName + " (rowID INT, colID INT, value DOUBLE)");

        // 执行SQL语句
        stmt.execute(sql.toString());

        stmt.close();
        conn.close();
    }

    public void checkIfTableExists(String tableName, Connection conn) throws SQLException {
        String query = "SELECT COUNT(*) FROM information_schema.tables " +
                "WHERE table_schema = DATABASE() AND table_name = ?";
        try (PreparedStatement stmt = conn.prepareStatement(query)) {
            stmt.setString(1, tableName);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    int count = rs.getInt(1);
                    if (count == 0) {
                        throw new SQLException("Table " + tableName + " does not exist.");
                    } else {
                        System.out.println("Table " + tableName + " exists.");
                    }
                }
            }
        }
    }

    //把数组结果保存到MySQL数据库中
    public void saveArrayToMySQL(double[][] data, String tableName) throws IOException, SQLException, ClassNotFoundException {
        String url = dbURL + "/" + dbName;
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, dbUserName, dbPassword);

        // 使用 PreparedStatement 执行插入操作
        String sql = "INSERT INTO " + tableName + " (rowID, colID, value) VALUES (?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        // 禁用自动提交，使用手动提交批量操作
        conn.setAutoCommit(false);

        int batchSize = 10000;  // 每批次插入1000条记录，可以根据需要调整
        int count = 0;

        // 遍历数据数组，准备批量插入
        for (int i = 0; i < data.length; i++) {
            for (int j = 0; j < data[i].length; j++) {
                pstmt.setInt(1, i);  // 设置 rowID
                pstmt.setInt(2, j);  // 设置 colID
                pstmt.setDouble(3, data[i][j]);  // 设置 value 为 double 类型

                pstmt.addBatch();  // 将当前的插入操作添加到批处理中

                // 如果已达到批次大小，则执行批量插入
                if (++count % batchSize == 0) {
                    pstmt.executeBatch();  // 执行批量插入
                }
            }
        }

        // 执行剩余的批量操作（如果有未执行的）
        pstmt.executeBatch();  // 执行最后一次批量插入

        // 提交事务
        conn.commit();

        // 关闭资源
        pstmt.close();
        conn.close();
    }


    //把数组结果保存到MySQL数据库中
    public void saveArrayToMySQL(int[][] data, String tableName) throws IOException, SQLException, ClassNotFoundException {
        String url = dbURL + "/" + dbName;
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, dbUserName, dbPassword);

        // 使用 PreparedStatement 执行插入操作
        String sql = "INSERT INTO " + tableName + " (rowID, colID, value) VALUES (?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        // 禁用自动提交，使用手动提交批量操作
        conn.setAutoCommit(false);

        int batchSize = 10000;  // 每批次插入1000条记录，可以根据需要调整
        int count = 0;

        // 遍历数据数组，准备批量插入
        for (int i = 0; i < data.length; i++) {
            for (int j = 0; j < data[i].length; j++) {
                pstmt.setInt(1, i);  // 设置 rowID
                pstmt.setInt(2, j);  // 设置 colID
                pstmt.setInt(3, data[i][j]);  // 设置 value

                pstmt.addBatch();  // 将当前的插入操作添加到批处理中

                // 如果已达到批次大小，则执行批量插入
                if (++count % batchSize == 0) {
                    pstmt.executeBatch();  // 执行批量插入
                }
            }
        }

        // 执行剩余的批量操作（如果有未执行的）
        pstmt.executeBatch();  // 执行最后一次批量插入

        // 提交事务
        conn.commit();

        // 关闭资源
        pstmt.close();
        conn.close();
    }
}