spring - How to execute @Sql before a @Before method

Question

I'm trying to combine the follow annotations:

org.springframework.test.context.jdbc.Sql and org.junit.Before

Like the follow code:

@Test
@Sql(scripts = "dml-parametro.sql")
public void testData(){
    Iterable<Parametro> parametros = parametroService.findAll();
    List<Parametro> parametrosList = Lists.newArrayList(parametros);

    Assert.assertThat(parametrosList.size(), Is.is(1));
}

@Before
public void beforeMethod() {
    JdbcTestUtils.deleteFromTables(jdbcTemplate, "PARAMETRO");
}

The code in the method @Before is running after then the script "dml-parametro.sql" in the @Sql annotation.

Is it right to do this?

For solution this, I'm using @After in place than @Before, but I'd like to cdelete tables before the test execution, not after.

I wouldn't like to use @SqlConfig. I'm not using transacional scope on test level, so i need to clean my tables in every test method. If every test method need to clean tables, i would like to do this in @Before method. I wouldn't like to do this in every test method with @SqlConfig. I think the behavior of @Sql to be execute before than @Before is wrong.

Answer 1

By default, any SQL scripts executed via @Sql will be executed before any @Before methods. So the behavior you are experiencing is correct, but you can change the execution phase via the executionPhase attribute in @Sql (see example below).

If you want to execute multiple scripts, that is also possible via @Sql.

So if you have a clean-up script named clean-parametro.sql that deletes from the PARAMETRO table, you could annotate your test method like the following (instead of invoking JdbcTestUtils.deleteFromTables() in your @Before method).

@Test
@Sql({"dml-parametro.sql", "clean-parametro.sql"})
public void test() { /* ... */ }

Of course, if dml-parametro.sql inserts values into the PARAMETRO table, then it likely does not make sense to immediately delete those values in the clean-up script.

Please note that @Sql and @SqlConfig provide multiple levels of configuration for script execution.

For example, if you want to create tables before your test and clean up after your test, you could do something like this on Java 8:

@Test
@Sql("create-tables.sql")
@Sql(scripts = "clean-up.sql", executionPhase = AFTER_TEST_METHOD)
public void test() { /* ... */ }

Or use @SqlGroup as a container on Java 6 or Java 7:

@Test
@SqlGroup({
    @Sql("create-tables.sql"),
    @Sql(scripts = "clean-up.sql", executionPhase = AFTER_TEST_METHOD)
})
public void test() { /* ... */ }

If your tests are @Transactional and you'd like to clean up committed database state, you can instruct Spring to execute your clean-up SQL script in a new transaction like this:

@Test
@Sql("insert-test-data.sql")
@Sql(
  scripts = "clean-up.sql",
  executionPhase = AFTER_TEST_METHOD,
  config = @SqlConfig(transactionMode = ISOLATED)
)
public void test() { /* ... */ }

I hope this clarifies things for you!

Cheers,

Sam (author of the Spring TestContext Framework)

---

Notes:

• AFTER_TEST_METHOD is statically imported from ExecutionPhase
• ISOLATED is statically imported from TransactionMode