Java中导出MySQL表为SQL脚本的两种方法

mysqldump -u [用户名] -p[密码] [数据库名] [表名] > [输出文件路径.sql]

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.IOException;

public class MySQLTableExporter {

    public static void main(String[] args) {
        String username = "your_username";
        String password = "your_password"; // 注意：在命令行中直接暴露密码存在安全风险
        String databaseName = "your_database";
        String tableName = "your_table";
        String outputFilePath = "D:/exported_table.sql"; // 导出SQL文件的路径

        // 构造mysqldump命令
        // 在Windows下，如果mysqldump不在系统PATH中，需要提供完整路径，例如 "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump"
        // 在Linux/macOS下，通常在/usr/bin或/usr/local/bin
        String command = String.format("mysqldump -u %s -p%s %s %s > %s",
                                       username, password, databaseName, tableName, outputFilePath);

        try {
            System.out.println("Executing command: " + command);
            Process process = Runtime.getRuntime().exec(new String[]{"cmd.exe", "/c", command}); // Windows
            // Process process = Runtime.getRuntime().exec(command); // Linux/macOS

            // 读取进程的错误输出，以便调试
            BufferedReader errorReader = new BufferedReader(new InputStreamReader(process.getErrorStream()));
            String line;
            StringBuilder errorOutput = new StringBuilder();
            while ((line = errorReader.readLine()) != null) {
                errorOutput.append(line).append("
");
            }

            int exitCode = process.waitFor(); // 等待进程执行完毕
            if (exitCode == 0) {
                System.out.println("MySQL table exported successfully to: " + outputFilePath);
            } else {
                System.err.println("Error exporting MySQL table. Exit code: " + exitCode);
                System.err.println("Error output:
" + errorOutput.toString());
            }

        } catch (IOException e) {
            System.err.println("IOException during mysqldump execution: " + e.getMessage());
            e.printStackTrace();
        } catch (InterruptedException e) {
            System.err.println("mysqldump process was interrupted: " + e.getMessage());
            Thread.currentThread().interrupt(); // 重新设置中断状态
        }
    }
}

import java.sql.*;
import java.io.FileWriter;
import java.io.IOException;

public class JDBCSQLExporter {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";

    public static void main(String[] args) {
        String tableName = "your_table";
        String outputFilePath = "D:/exported_data_jdbc.sql";

        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement();
             FileWriter writer = new FileWriter(outputFilePath)) {

            // 1. 获取表结构信息 (用于构建INSERT语句的列名)
            ResultSetMetaData metaData = stmt.executeQuery("SELECT * FROM " + tableName + " LIMIT 0").getMetaData();
            int columnCount = metaData.getColumnCount();
            String[] columnNames = new String[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                columnNames[i - 1] = metaData.getColumnName(i);
            }

            // 2. 查询所有数据
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);

            // 3. 遍历结果集并生成INSERT语句
            while (rs.next()) {
                StringBuilder insertStatement = new StringBuilder("INSERT INTO ");
                insertStatement.append(tableName).append(" (");

                // 添加列名
                for (int i = 0; i < columnCount; i++) {
                    insertStatement.append(columnNames[i]);
                    if (i < columnCount - 1) {
                        insertStatement.append(", ");
                    }
                }
                insertStatement.append(") VALUES (");

                // 添加值
                for (int i = 1; i <= columnCount; i++) {
                    Object value = rs.getObject(i);
                    if (value == null) {
                        insertStatement.append("NULL");
                    } else if (value instanceof String || value instanceof Date || value instanceof Timestamp) {
                        // 对字符串和日期类型的值进行转义处理，并用单引号括起来
                        insertStatement.append("'").append(escapeSql(value.toString())).append("'");
                    } else {
                        insertStatement.append(value.toString());
                    }
                    if (i < columnCount) {
                        insertStatement.append(", ");
                    }
                }
                insertStatement.append(");
");
                writer.write(insertStatement.toString());
            }

            System.out.println("Data exported successfully to: " + outputFilePath);

        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
            e.printStackTrace();
        } catch (IOException e) {
            System.err.println("File writing error: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 简单的SQL字符串转义函数
    private static String escapeSql(String value) {
        if (value == null) {
            return null;
        }
        return value.replace("'", "''"); // 简单的转义，实际生产中可能需要更全面的转义库
    }
}