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

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.

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;
}