Importing a (mysql) database dump programmatically

Question

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.

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.

Answer 1

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.

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

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.

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

Answer 2

Effective solution can be found here:

https://stackoverflow.com/a/1044837

This explains how to run any sql script over jdbc.

Answer 3

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:

Runtime rt = Runtime.getRuntime();
Process pr = rt.exec("mysql -p -h ServerName DbName < dump.sql");

Answer 4

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”);

}