通过 Java 以编程方式导入 (mysql) 数据库转储
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1955268/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Importing a (mysql) database dump programmatically through Java
提问by Ali Salehi
How can I import a mysql database dump file (contains insert and create table statements) programmatically through a java program. I need this as the setup phase of a unit test.
如何通过 java 程序以编程方式导入 mysql 数据库转储文件（包含插入和创建表语句）。我需要将其作为单元测试的设置阶段。
Unfortunately this doesn't work:
不幸的是，这不起作用：
Connection conn = dbConnectionSource.getConnection();
Statement stmt = conn.createStatement();
stmt.execute(FileUtils.readFileToString(new File("./some-sql-file")));
conn.close();
Thanks, -A
谢谢
PS - In Rails, I used fixtures for filling a test database. I made rails rails create the underlying tables through setting the environment to test, anything similar in Java.
PS - 在 Rails 中，我使用夹具来填充测试数据库。我让 rails rails 通过设置环境来创建底层表来测试，任何类似于 Java 的东西。
采纳答案by jvilalta
You could start a new process from java and execute this command if you have access to the mysql executable wherever you are running the import. Something like this:
如果您可以在任何运行导入的地方访问 mysql 可执行文件，则可以从 java 启动一个新进程并执行此命令。像这样的东西：
Runtime rt = Runtime.getRuntime();
Process pr = rt.exec("mysql -p -h ServerName DbName < dump.sql");
回答by Heidarzadeh
Backup:
备份：
/******************************************************/
//Database Properties
/******************************************************/
String dbName = “dbName”;
String dbUser = “dbUser”;
String dbPass = “dbPass”;
/***********************************************************/
// Execute Shell Command
/***********************************************************/
String executeCmd = “”;
executeCmd = “mysqldump -u “+dbUser+” -p”+dbPass+” “+dbName+” -r backup.sql”;
}
Process runtimeProcess =Runtime.getRuntime().exec(executeCmd);
int processComplete = runtimeProcess.waitFor();
if(processComplete == 0){
out.println(“Backup taken successfully”);
} else {
out.println(“Could not take mysql backup”);
}
Restore:
恢复：
/******************************************************/
//Database Properties
/******************************************************/
String dbName = “dbName”;
String dbUser = “dbUser”;
String dbPass = “dbPass”;
/***********************************************************/
// Execute Shell Command
/***********************************************************/
String executeCmd = “”;
executeCmd = new String[]{“/bin/sh”, “-c”, “mysql -u” + dbUser+ ” -p”+dbPass+” ” + dbName+ ” < backup.sql” };
}
Process runtimeProcess =Runtime.getRuntime().exec(executeCmd);
int processComplete = runtimeProcess.waitFor();
if(processComplete == 0){
out.println(“success”);
} else {
out.println(“restore failure”);
}
回答by Roland Bouman
Personally I would disrecommend loading a regular SQL dump in this way, because you would need non-trivial code to parse or at least tokenize SQL.
我个人不推荐以这种方式加载常规 SQL 转储，因为您需要非平凡的代码来解析或至少标记 SQL。
I would recommend using CSV data dumps, you can load these with a the LOAD DATA INFILE syntax. See: http://dev.mysql.com/doc/refman/5.1/en/load-data.html
我建议使用 CSV 数据转储，您可以使用 LOAD DATA INFILE 语法加载它们。见：http: //dev.mysql.com/doc/refman/5.1/en/load-data.html
Of course, you would still need to ensure the target tables exist, but if you know you only have to parse table creation DDL stattemnts, that will drastically simplify your java code.
当然，您仍然需要确保目标表存在，但是如果您知道只需解析表创建 DDL stattemnts，这将大大简化您的 Java 代码。
Note that you can use mysqldump to extract CSV data from your database, see: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_tab
请注意，您可以使用 mysqldump 从数据库中提取 CSV 数据，请参阅：http: //dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_tab
回答by shandor
Effective solution can be found here:
可以在这里找到有效的解决方案：
https://stackoverflow.com/a/1044837
https://stackoverflow.com/a/1044837
This explains how to run any sql script over jdbc.
这解释了如何通过 jdbc 运行任何 sql 脚本。
