How can I run @BeforeEach method BEFORE @Sql script for each test case in spring boot integration test?

Question

I created a sample application here if someone wants to replicate the issue - https://github.com/jainishan/spring-boot-tryouts/blob/main/src/test/java/com/samples/sample/RepositoryTestWithJDBC.java

The above test case is pretty much what I am trying to achieve.

My use case is simple - I need to truncate all tables in a generic way before the execution of each test case in my spring boot integration tests. I am using test containers in my setup and want to run a stored procedure to clear up all tables.

I wrote a stored procedure that runs fine in the DB but doesn't execute via @Sql annotation because of delimiter issues. I was not able to solve that problem - MYSQL procedure runs fine in DB but throwing errors while running within spring boot integration tests

Now I wrote a method to do it programatically using JDBC template - https://github.com/jainishan/spring-boot-tryouts/blob/main/src/test/java/com/samples/sample/config/BaseIntegrationTestJDBC.java#L24

I want to wire my integration tests in a way so that the method to clean up all tables run before the @Sql annotation. But the @Sql annotation always gets executed first and messes up the whole setup.

This sounds like a common requirement - to clean up tables right before each test case. I wonder how others are doing it ?

Answer 1

You can achieve that in the following way. First, create a JUnit extension, by implementing BeforeEachCallback, that would do the cleaning part:

public class DatabaseCleanerExtension implements BeforeEachCallback {
  private JdbcTemplate jdbcTemplate;

  @Override
  public void beforeEach(ExtensionContext extensionContext) {
    if (jdbcTemplate == null) {
      jdbcTemplate = SpringExtension.getApplicationContext(extensionContext).getBean(JdbcTemplate.class);
    }
    cleanDatabase();
  }

  private void cleanDatabase() {
    jdbcTemplate.execute("SET FOREIGN_KEY_CHECKS = 0");
    List<String> tableNames = jdbcTemplate.queryForList(
        "SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()", String.class);
    for (String tableName : tableNames) {
      jdbcTemplate.execute("TRUNCATE TABLE " + tableName);
    }
    jdbcTemplate.execute("SET FOREIGN_KEY_CHECKS = 1");
  }
}

Since extensions are not managed by Spring, jdbcTemplate cannot be autowired directly. However, it can be retrieved from the extension context using the line below, as explained in Sam's answer.

SpringExtension.getApplicationContext(extensionContext).getBean(JdbcTemplate.class)

Then create a new class and annotate it with @ExtendsWith(DatabaseCleanerExtension.class).

@ExtendsWith(DatabaseCleanerExtension.class)
public class DatabaseCleaner {}

Finally, modify the BaseIntegrationTestJDBC class as shown below:

@ActiveProfiles("it")
@AutoConfigureMockMvc
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public abstract class BaseIntegrationTestJDBC extends DatabaseCleaner {}

The catch is to put the @ExtendsWith(DatabaseCleanerExtension.class) annotation on the top of the superclass of the class annotated with @SpringBootTest. Otherwise, @Sql would still be executed before the beforeEach method defined in the extension.