在Java应用程序中导出MySQL表为SQL脚本的教程

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

mysqldump -u root -pmysecretpassword mydatabase mytable > mytable_backup.sql

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

public class MySQLTableExporter {

    public static void main(String[] args) {
        String username = "root";
        String password = "mysecretpassword"; // 实际应用中不应硬编码密码
        String databaseName = "mydatabase";
        String tableName = "mytable";
        String outputFilePath = "mytable_backup.sql";

        // 构建mysqldump命令
        // 注意：在Windows上，可能需要mysqldump.exe的完整路径
        // 在Linux/macOS上，确保mysqldump在PATH中或提供完整路径
        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(command);

            // 读取命令的错误输出流，以便捕获错误信息
            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 '" + tableName + "' exported successfully to " + outputFilePath);
            } else {
                System.err.println("Error exporting MySQL table. Exit code: " + exitCode);
                System.err.println("Error output:
" + errorOutput.toString());
            }

        } catch (IOException | InterruptedException e) {
            System.err.println("Failed to execute mysqldump command: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version> <!-- 使用适合你的版本 -->
</dependency>

import java.sql.*;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class JDBCSQLExporter {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "mysecretpassword"; // 实际应用中不应硬编码密码

    public static void main(String[] args) {
        String tableName = "mytable";
        String outputFilePath = "mytable_data_backup.sql";

        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT * FROM " + tableName)) {

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<String> columnNames = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(metaData.getColumnName(i));
            }

            try (FileWriter writer = new FileWriter(outputFilePath)) {
                writer.write("-- Exporting data for table: " + tableName + "
");
                writer.write("USE " + connection.getCatalog() + ";
"); // 写入USE语句

                while (resultSet.next()) {
                    StringBuilder insertSql = new StringBuilder("INSERT INTO ");
                    insertSql.append(tableName).append(" (");

                    // 构建列名部分
                    for (int i = 0; i < columnNames.size(); i++) {
                        insertSql.append(columnNames.get(i));
                        if (i < columnNames.size() - 1) {
                            insertSql.append(", ");
                        }
                    }
                    insertSql.append(") VALUES (");

                    // 构建值部分
                    for (int i = 1; i <= columnCount; i++) {
                        Object value = resultSet.getObject(i);
                        if (value == null) {
                            insertSql.append("NULL");
                        } else if (value instanceof String || value instanceof Date || value instanceof Timestamp) {
                            // 对字符串、日期时间类型进行转义和加引号
                            insertSql.append("'").append(escapeSqlString(value.toString())).append("'");
                        } else {
                            // 其他类型（数字等）直接输出
                            insertSql.append(value.toString());
                        }
                        if (i < columnCount) {
                            insertSql.append(", ");
                        }
                    }
                    insertSql.append(");
");
                    writer.write(insertSql.toString());
                }
                System.out.println("MySQL table data for '" + tableName + "' 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 escapeSqlString(String s) {
        if (s == null) {
            return "";
        }
        return s.replace("'", "''"); // 简单转义单引号
    }
}