DbUnit fails to clean-insert: foreign key constraint

Question

I'm asking and answering this question for future reference, because I think I've found a decent solution to a common problem with DbUnit. I hope it helps out someone, somewhere down the line.

I'm using DbUnit 2.5.0 and TestNG 6.8.8. My use case is for a part of a database which comprises 3 entities. There is a ServiceUser which holds a foreign key to both an Administrable and a UserGroup.

I followed most of the code example from http://city81.blogspot.com/2011/03/testing-jpa-entities-using-dbunit.html

public abstract class AbstractDatabaseTest {
    protected EntityManager em; // initialized elsewhere
    private IDatabaseConnection connection;
    private IDataSet dataset;

    @BeforeClass
    private void setupDatabaseResource() throws Exception {
        // using Hibernate
        connection = new DatabaseConnection(((SessionImpl) (em.getDelegate())).connection());
        connection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new HsqldbDataTypeFactory());

        // full database export
        IDataSet fullDataSet = connection.createDataSet();

        final String datasetPath = String.format("%s%s", RESOURCE_FOLDER, "Testing.xml");
        FlatXmlDataSet.write(fullDataSet, new FileOutputStream(datasetPath));

        FlatXmlDataSetBuilder flatXmlDataSetBuilder = new FlatXmlDataSetBuilder();
        flatXmlDataSetBuilder.setColumnSensing(true);
        dataset = flatXmlDataSetBuilder.build(new FileInputStream(datasetPath));
    }

    @AfterMethod
    public void cleanDB() throws Exception {
        em.getTransaction().begin();
        DatabaseOperation.CLEAN_INSERT.execute(connection, dataset);
        em.getTransaction().commit();
    }
}

The result of this is the following XMLDataSet (data omitted):

<dataset>
  <administrable/>
  <serviceuser/>
  <usergroup/>
</dataset>

When TestNG executes the @AfterMethod, I get the following Exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException
Cannot delete or update a parent row: a foreign key constraint fails (`testing_db`.`serviceuser`, CONSTRAINT `FK_gyylcfbhpl2ukqs5rm7sq0uy8` FOREIGN KEY (`userGroup_id`) REFERENCES `usergroup` (`id`))

Answer 1

The problem is that the XMLDataSet I created above does not know the foreign key constraints in the tables, and creates the table list alphabetically. The CLEAN_INSERT operation, however, takes the list of tables and traverses it in reverse order, and it requires that foreign key references (here: ServiceUser.userGroup_id) be deleted before the referenced entity (here: UserGroup).

I found this information through Unitils doesn't work and http://forum.spring.io/forum/spring-projects/data/12868-dbunit-test-fails-mysql-server-hates-me?p=337672#post337672

Some minor digging into the DbUnit documentation led to a corrected approach to creating the dataset, which checks for foreign key dependencies and tries to order the entities appropriately:

IDataSet fullDataSet = new FilteredDataSet(new DatabaseSequenceFilter(connection), connection.createDataSet());

Result:

<dataset>
  <administrable/>
  <usergroup/>
  <serviceuser/>
</dataset>

This will CLEAN_INSERT correctly.

Disclaimer: I have not come across circular dependencies or self-referencing foreign keys, and I have not tested that this fix can handle them.