Creating a Reliable Testbed for Play Framework with a MySQL Database

Recently I have been working with the Play framework, and my job was to create a reliable test base class that would handle cleanup of the database after every test. Originally I was trying to use DBUnit to make my life easy, but I was never able to get the filter to work correctly to create the required flat XML file/dataset that is required to dismantle and create the database. Eventually (after a few days of frustration) I went a more direct path with my solution of restoring the database to a known state.

Using built in MySQL commands I was able to create a dump of the MySQL database at the beginning of the class (using @BeforeClass) by calling the system command "mysqldump". In Java it is important to note that the act of piping information is not available with the Process class, so the normal command of

mysqldump -hHost -uUser -pPassword Schema > dump.sql

was not going to work. The real command should be

mysqldump -hHost -uUser -pPassword --result-file=dump.sql Schema

Same goes for restoration of the database (which I do in the @Before annotation). Piping is not legal, so instead I used

mysql -hHost -uUser -pPassword -e "source dump.sql" Schema

Hopefully those of you using the Play framework will find this useful to create a simple backup and restore procedure for testing purposes. I would recommend using DBUnit if at all possible, and I do plan on changing it if I have the time, but for now this runs fast enough for me, and is reliable.