Spring @Sql Annotations, possible to run once before all tests?

Question

Using Spring for integration tests I am able to populate a test DB running scripts like so...

@Test
@Sql({"/db/schema.sql", "/db/accountConfig.sql", "/db/functions/fnSomething.sql"})
public void verifySomething() {
   ...
}

However, I'd like to run all my .sql files only once before any test runs. Is there a JUnit 4 way to do this? It seems like @Sql only runs for methods with the @Test annotations.

I'm using Junit 4, Spring Boot, Java 15, Testcontainers.

Things I've tried...

• I've tried using @BeforeClass on a class my test classes extend but that seems to run after my tests.
• Testcontainers does have a init script function but it only takes one file, not ideal.
• I've also tried ScriptUtils.executeSqlScript but for some reason, test containers do not like that.

Here is my sample code that works with @Sql but does not work with ScriptUtils.executeSqlScript.

@ContextConfiguration(initializers = AbstractIntegrationTest.Initializer.class)
public abstract class AbstractIntegrationTest {

    @ClassRule 
    public static MSSQLServerContainer mssqlserver = new MSSQLServerContainer();

  
    public static class Initializer implements ApplicationContextInitializer<ConfigurableApplicationContext> {

        @Override
        public void initialize(ConfigurableApplicationContext configurableApplicationContext) {
            ConfigurableEnvironment environment = configurableApplicationContext.getEnvironment();
            Properties props = new Properties();
            props.put("spring.datasource.driver-class-name", mssqlserver.getDriverClassName());
            props.put("spring.datasource.url", mssqlserver.getJdbcUrl());
            props.put("spring.datasource.username", mssqlserver.getUsername());
            props.put("spring.datasource.password", mssqlserver.getPassword());

            environment
                .getPropertySources()
                .addFirst(new PropertiesPropertySource("myTestDBProps", props));      

            configurableApplicationContext.setEnvironment(environment);
    }
}

My test classes simply extend AbstractIntegrationTest. But using @Sql runs scripts for every test case. Does anyone have a suggestion for a better way to init SQL scripts? Tried flyway but it won't allow the creation of a DB from a script.

Intitule

Liquibase is great for setting up and managing test DB datasets. docs.liquibase.com/tools-integrations/springboot/… – Alysa

Answer 1

I recommend taking a look for database initializer beans that execute SQL scripts once on the Spring context initialization. Basically, there are two solutions depending on whether you use JDBC or R2DBC. Since you want to initialize multiple scripts, you should rather use CompositeDatabasePopulator. Remember also to import the correct classes as they have the same names but are from different packages, again, depending on JDBC/R2DBC.

For loading resources from the resources folder feel free to use either one of the choices:

• Resource resource = new ClassPathResource("sql/schema.sql"))
• @Value("classpath:sql/schema.sql") Resource resource

This solution is fairly flexible as you can define the initializer bean for the test context using @TestConfiguration (remember using this annotation is a bit tricky, so I refer you to this article: Quirks of Spring's @TestConfiguration that helped me a lot).

The solution should work for any solution having ConnectionFactory available including Test Containers.

JDBC

• Use org.springframework.jdbc.datasource.init.CompositeDatabasePopulator
• Use org.springframework.jdbc.datasource.init.ResourceDatabasePopulator

The initialization through calling populate must happen in @PostConstuct as Spring Boot automatically doesn't detect the populator. I recommend wrapping the following code snippet in a configuration class and include it for the test scope.

@Autowired
private DataSource dataSource;

@PostConstruct
public void initData() throws SQLException {

    var populator = new CompositeDatabasePopulator();
    populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("sql/schema.sql")));
    populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("sql/catalog.sql")));
    populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("sql/data.sql")));

    populator.populate(dataSource.getConnection());
}

R2DBC

• Use org.springframework.r2dbc.connection.init.CompositeDatabasePopulator
• Use org.springframework.r2dbc.connection.init.ResourceDatabasePopulator

You can use ConnectionFactoryInitializer that initializes all the populators added to this initializer once when Spring context is loaded.

@Bean
public ConnectionFactoryInitializer initializer(ConnectionFactory connectionFactory) {

    var populator = new CompositeDatabasePopulator();
    populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("sql/schema.sql")));
    populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("sql/catalog.sql")));
    populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("sql/data.sql")));


    var initializer = new ConnectionFactoryInitializer();
    initializer.setConnectionFactory(connectionFactory);
    initializer.setDatabasePopulator(populator);

    return initializer;
}

Longsome

I was unaware of these, thanks for the suggestions I will give them a try. – Intitule

Best of luck. Setting an abstract parent is a bit tricky and with connection to Spring context needs to experiment a lot. I hope my answer gives you at least a good start. I use an abstract class annotated with @Testcontainers with a static container annotated with @Container. In the same class I use @DynamicPropertySource to inject container database configuration into properties. – Longsome

Answer 2

You can go around this by adding a DELETE statement before the INSERT statement. Something like DELETE FROM table_name WHERE ID IN (Id1, Id2)

Crash

Answer 3

A bit late with the answer, but you might consider this:

• annotate the @Sql to the class instead of the method
• set the parameter executionPhase = Sql.ExecutionPhase.BEFORE_TEST_CLASS in the annotation.

With that parameter you can also have SQL executed after a single test or after the test class.

If you want to combine multiple annotations on class and method level, use additional @SqlMergeMode(SqlMergeMode.MergeMode.MERGE)

Binghi