Importing a (mysql) database dump programmatically through Java

Question

Hi All, 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

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