The easiest way of creating datasets for DbUnit tests is often to script out values from a real database. This can be done with a few lines of code in DbUnit itself (see DbUnit FAQs) but it’s easier to use a database tool that can export to DbUnit dataset files. Jailer is one such tool. In its own words:Jailer is a tool for database subsetting, schema and data browsing. It exports consistent, referentially intact row-sets from relational databases. It removes obsolete data without violating integrity. It is DBMS agnostic (by using JDBC), platform independent, and generates DbUnit datasets, hierarchically structured XML, and topologically sorted SQL-DML.
It’s pretty easy to set up and has the advantage that it can flexibly script target data as well as all associated data necessary to satisfy foreign key constraints.
Jailer can be downloaded from Sourceforge. Installation on Windows is simply a case of extracting the zip. The standard distribution contains a Windows exe file.
On starting Jailer, the only fiddly bit is connecting to your datasource. As it connects using JDBC, you’ll need to provide the driver jar for your database vendor. You’ll also need to specify the database URL, username and password. In my case, I used the MySQL Connector/J driver to connect to my MySQL Sakila (DVD rental store) sample database.
Jailer will export data from a single table and all data that is related to it by foreign key references. This can result in far more data being scripted than expected. In this example I wanted to script a single row from the actor table.
The diagram shows how it traversed the foreign key references. It found 26 films that my actor appeared in, then found the language of the film and all other films (and their actors) in that language. It also found all the times that the films were rented and all payments processed by the staff in the rental stores. After identifying 40000 rows to be exported, it gave up complaining of cyclic dependencies in the database.
The schema traversal is handy but often needs to be tweaked slightly before it will produce useful exports. On my second attempt, I restricted the associations.
Associations can be restricted by right clicking on the arrows on the star chart and selecting ‘Disable Association’. First, I disabled all associations with the inventory table. I’m not interested in the shop inventory or rentals. Just actors and films. I also pruned some of the two way associations. I’m interested in knowing all the categories of films that my actor appeared in. I’m not interested in knowing all the other films in that category. So I removed the link from category to film_category. I then tried the export again.
This time it has traversed only the associations that I’m interested in. My export contains all of the films that my actor appeared in and the categories and languages of those films. This gave a manageable 120 row DbUnit flat dataset.