Importing a (mysql) database dump programmatically through Java

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

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 2

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

}