How to execute sql script before and after each test method

Question

There is an @Sql annotation in spring which allows to execute sql code before and after the test method:

@Test
@Sql("init.sql")
@Sql(scripts = "clean.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
public void someTest()
{
}

However I have several test methods where I would like to provide the same clean environment like in the test above and I don't want to repeat for every test the same @Sql annotation. How to do it once for all methods? For example:

// JPA and Spring other test annotations
@Sql("init.sql")
@Sql(scripts = "clean.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
public class TestClass
{
  // init.sql executed before every test, clean.sql executed after every test
}

jwpol · Accepted Answer · 2020-07-05 18:33:02Z

8

Indeed when you place @Sql on the class, sql scripts will be executed before and after every test defined in that class, more specifically before @Before and after @After methods. So,

// JPA and Spring other test annotations
@Sql("init.sql")
@Sql(scripts = "clean.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
public class TestClass
{
  // init.sql executed before every test, clean.sql executed after every test
}

is going to work according to the @Sql definition:

@Target({ElementType.TYPE, ElementType.METHOD})
/// other annotations
public @interface Sql {
   //
}

answered Jul 5, 2020 at 18:33

jwpol

1,5251 gold badge15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shabar Over a year ago

Where should you plase the sql scripts in the above appraoch? Under test resuorce folder?

Shabar Over a year ago

If I were to run above sql in IntegrationTest folder, what more changes have to do?

crizzis · Accepted Answer · 2020-07-06 12:03:41Z

0

If my understanding is correct, you want to execute an init script to put the DB in a certain state, and then ensure the DB is back to that state before each test method, right?

The simplest solution is to use @Transactional, then. By default, Spring Boot will automatically roll back the test transaction of a @Transactional-annotated test, thus resetting the DB to the original state.

There are two downsides one should consider, though:

@Transactional means there will exist a transaction spanning the entire execution of the test method which the tested service methods will typically join. Hence, the test itself cannot be relied upon to verify the correctess of transactional boundaries in the production code (LazyInitializationExceptions may be covered by this 'outer' transacion, for example)
The persistence context will not flush unless necessary, meaning that some issues (e.g. DB constraint violations) will not surface. I tend to work around that issue using a last-chance flush like so:

  @After
  public void flushContext() {
    if (TransactionSynchronizationManager.isActualTransactionActive()) {
      entityManager.flush();
    }
  }

answered Jul 6, 2020 at 12:03

crizzis

10.8k2 gold badges32 silver badges54 bronze badges

1 Comment

jwpol Over a year ago

Answering the first question: yes this is exactly what I wanted to achieve. I use @DataJpaTest, so @Transactional is used anyway. The reason why I wanted to run init and clear scripts is because of sequences which cannot be rolled back, so in the init.sql and clean.sql I drop sequences and recreate them consequently to have completely clean state. Thanks for the first remark about @Transactional very good point.

Collectives™ on Stack Overflow

How to execute sql script before and after each test method

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related