This post shows how to truncate a table in MySQL before DbUnit loads data via @DatabaseSetup. Truncating the tables in the @Before method does not work because DBUnit loads the data first via @DatabaseSetup before delegating calls to a @Before method. Please be advised that it may be a bad idea to truncate all tables for each @Test method. However, it depends on project requirements. Some projects are okay with this technique, while others are not.
Requirements
- DbUnit 2.5.4
- MySQL
- Spring Boot 1.5.10.RELEASE
- Sprint Test DbUnit 1.2.1
Using Spring Test DbUnit
Using Spring Test DbUnit is straightforward. We use DbUnitTestExecutionListener to be able to use its annotations like @DatabaseSetup.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | ... @RunWith(SpringRunner.class) @SpringBootTest(classes = DemoApplication.class) @WebAppConfiguration @TestExecutionListeners({ DependencyInjectionTestExecutionListener.class, TransactionalTestExecutionListener.class, DbUnitTestExecutionListener.class }) public class DemoControllerIntTest { // Mocks and stuff @Before public void setUp() throws Exception { // This is invoked after the contents of TestData1.xml is loaded in to some tables } @Test @DatabaseSetup(value = "TestData1.xml", type = DatabaseOperation.CLEAN_INSERT) @ExpectedDatabase(value = "TestData1out.xml", assertionMode = DatabaseAssertionMode.NON_STRICT) public void testMe() throws Exception { // Truncating the tables from here does not work. This method is called after the @Before method. } } |
The file TestData1.xml contains the data we will load into some MYSQL tables before the @Before, and @Test methods run.
Solution – Truncate Table Before DBUnit Loads Data
The solution is to extend DbUnitTestExecutionListener. Therefore, we need to create a subclass of DbUnitTestExecutionListener. Then, we override the beforeTestMethod method wherein we truncate the table.
1 2 3 4 5 6 7 8 9 | public class DbUnitTestExecutionListenerX extends DbUnitTestExecutionListener { public void beforeTestMethod(TestContext testContext) throws Exception { EntityManagerFactory r = testContext.getApplicationContext().getBean(EntityManagerFactory.class); TruncateTablesUtil util = new TruncateTablesUtil(); util.truncate(r.createEntityManager()); super.beforeTestMethod(testContext); } } |
Then, the codes delegate calls to the TruncateTablesUtil.truncate(...) method, which looks like the following.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | // Notice the codes use SQL Delete. Thus, the transaction. public class TruncateTablesUtil { public void truncate(EntityManager entityManager) { List<String> tablesToDelete = Stream.of( "DELETE FROM `t1`;", "DELETE FROM `t2` where user_role_id > 1;") .collect(Collectors.toList()); entityManager.getTransaction().begin(); entityManager.flush(); entityManager.createNativeQuery("SET @@foreign_key_checks = 0;").executeUpdate(); entityManager.createNativeQuery("SET SQL_SAFE_UPDATES = 0;").executeUpdate(); tablesToDelete.forEach(stmt -> entityManager.createNativeQuery(stmt).executeUpdate()); entityManager.createNativeQuery("SET @@foreign_key_checks = 1;").executeUpdate(); entityManager.createNativeQuery("SET SQL_SAFE_UPDATES = 1;").executeUpdate(); entityManager.getTransaction().commit(); } } |
Then, we need to update our unit test as follows.
1 2 3 4 5 6 7 8 9 10 | ... @RunWith(SpringRunner.class) @SpringBootTest(classes = DemoApplication.class) @WebAppConfiguration @TestExecutionListeners({ DependencyInjectionTestExecutionListener.class, TransactionalTestExecutionListener.class, DbUnitTestExecutionListenerX.class }) public class DemoControllerIntTest { ... |
Note the use of DbUnitTestExecutionListenerX.class in @TestExecutionListeners. Although this is a solution, it is not the best way to meet the objective.
