How can Spring's test annotation @Sql behave like @BeforeClass?

Question

How can I tell the @Sql annotation to run only once for the class, and not for each @Test method?

Like having the same behaviour as @BeforeClass?

@org.springframework.test.context.jdbc.Sql(
     scripts = "classpath:schema-test.sql",
     executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD
)
public class TestClass {
      @Test
      public void test1() {
        //runs the @Sql script
      }

      @Test
      public void test2() {
        //runs the @Sql script again
      }
}

Mages

Are you interested only on the response for Junit <=4 or you want it for Junit 5 too? – Magdau

always most recent version. – Mages

Answer 1

For JUnit 5, the straight forward clean solution:

@MyInMemoryDbConfig
//@Sql(value = {"/appconfig.sql", "/album.sql"}) -> code below is equivalent but at class level
class SomeServiceTest {
    @BeforeAll
    void setup(@Autowired DataSource dataSource) {
        try (Connection conn = dataSource.getConnection()) {
            // you'll have to make sure conn.autoCommit = true (default for e.g. H2)
            // e.g. url=jdbc:h2:mem:myDb;DB_CLOSE_DELAY=-1;MODE=MySQL
            ScriptUtils.executeSqlScript(conn, new ClassPathResource("appconfig.sql"));
            ScriptUtils.executeSqlScript(conn, new ClassPathResource("album.sql"));
        }
    }
    // your @Test methods follow ...

but when your database connections are not configured with autoCommit = true you'll have to wrap all in a transaction:

@RootInMemoryDbConfig
@Slf4j
class SomeServiceTest {
    @BeforeAll
    void setup(@Autowired DataSource dataSource,
            @Autowired PlatformTransactionManager transactionManager) {
        new TransactionTemplate(transactionManager).execute((ts) -> {
            try (Connection conn = dataSource.getConnection()) {
                ScriptUtils.executeSqlScript(conn, new ClassPathResource("appconfig.sql"));
                ScriptUtils.executeSqlScript(conn, new ClassPathResource("album.sql"));
                // should work without manually commit but didn't for me (because of using AUTOCOMMIT=OFF)
                // I use url=jdbc:h2:mem:myDb;DB_CLOSE_DELAY=-1;MODE=MySQL;AUTOCOMMIT=OFF
                // same will happen with DataSourceInitializer & DatabasePopulator (at least with this setup)
                conn.commit();
            } catch (SQLException e) {
                SomeServiceTest.log.error(e.getMessage(), e);
            }
            return null;
        });
    }
    // your @Test methods follow ...

Why clean solution?

Because according to Script Configuration with @SqlConfig:

The configuration options provided by @Sql and @SqlConfig are equivalent to those supported by ScriptUtils and ResourceDatabasePopulator but are a superset of those provided by the <jdbc:initialize-database/> XML namespace element.

Bonus

You can mix this approach with other @Sql declarations.

Magdau

Thanks! This was a simple enough solution... it worked well for me! – Honk

Answer 2

Edit: As of Spring 6.1, around October 2023, there are 2 new execution modes: BEFORE_TEST_CLASS and AFTER_TEST_CLASS

If you're using a version before that, my original answer follows:

You can't do that out-of-the-box. The @Sql annotation only has two modes - BEFORE_TEST_METHOD and AFTER_TEST_METHOD.

The listener responsible for executing these scripts, SqlScriptsTestExecutionListener, does not implement before or after-class methods.

---

To work around this, I'd implement my own TestExecutionListener, wrapping the default SqlScriptsTestExecutionListener. You can then declare on your test to use the new listener rather than the old ones.

public class BeforeClassSqlScriptsTestExecutionListener implements TestExecutionListener
{    
    @Override
    public void beforeTestClass(final TestContext testContext) throws Exception
    {
        // Note, we're deliberately calling beforeTest*Method*
        new SqlScriptsTestExecutionListener().beforeTestMethod(testContext);
    }

    @Override
    public void prepareTestInstance(final TestContext testContext) { }

    @Override
    public void beforeTestMethod(final TestContext testContext) { }

    @Override
    public void afterTestMethod(final TestContext testContext) { }

    @Override
    public void afterTestClass(final TestContext testContext) { }
}

Your test would then become:

@TestExecutionListeners(
    listeners = { BeforeClassSqlScriptsTestExecutionListener.class },
    /* Here, we're replacing more than just SqlScriptsTestExecutionListener, so manually
       include any of the default above if they're still needed: */
    mergeMode = TestExecutionListeners.MergeMode.REPLACE_DEFAULTS
)
@org.springframework.test.context.jdbc.Sql(
    scripts = "classpath:schema-test.sql",
    executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD
)
public class MyTest
{
    @Test
    public void test1() { }

    @Test
    public void test2() { }
}

Tercel

(This is untested because I don't have a Spring context or database to test it on, but I believe it should work) – Tercel

A nice idea! I did try this and unfortunately it failed due to detail within SqlScriptsTestExecutionListener which required a TEST_METHOD to exist. It doesn't at the beforeClass level and so it fails. An alternative along the same lines is to use ScriptUtils.executeSqlScript directly within the listener rather than delegating to SqlScriptsTestExecutionListener - a bit of an extension to the answer from @adrhc – Diarrhea

As of Spring Framework 6.1: BEFORE_TEST_CLASS, AFTER_TEST_CLASS - doc – Adaliah

Answer 3

@BeforeAll and @BeforeClass annotations require 'static' methods. So it does not work. What about @PostConstruct in configuration file? It works for me fine.

@TestConfiguration
public class IntegrationTestConfiguration {

@Autowired
private DataSource dataSource;

@PostConstruct
public void initDB() throws SQLException {
    try (Connection con = dataSource.getConnection()) {
        ScriptUtils.executeSqlScript(con, new ClassPathResource("data.sql"));
    }
}
}

@ContextConfiguration(classes = {IntegrationTestConfiguration.class})
public class YourIntegrationTest {

}

Elodiaelodie

Answer 4

An alternative would be to do a setup and tear down for each method if it does not result in unacceptable test run time.

@Sql({"/setup.sql"}, executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
@Sql({"/teardown.sql"}, executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)

Another way would be to place test data in the following files as explained by https://www.baeldung.com/spring-boot-data-sql-and-schema-sql:

• src/test/resources/schema.sql
• src/test/resources/data.sql

Unhelm

Answer 5

This code throws an IllegalStateException (Spring 5.0.1) because of the getTestMethod() method in DefaultTestContext.java:

public final Method getTestMethod() {
    Method testMethod = this.testMethod;
    Assert.state(testMethod != null, "No test method");
    return testMethod;
}

When calling the beforeTestClass method through your proposed implementation, the textContext does not contain a valid testMethod (which is normal at this stage):

public class BeforeClassSqlScriptsTestExecutionListener implements TestExecutionListener {

    @Override
    public void beforeTestClass(TestContext testContext) throws Exception {
        new SqlScriptsTestExecutionListener().beforeTestMethod(testContext);
    }
}

When the code responsible of running SQL scripts (in the SqlScriptsTestExecutionListener) is executed, a valid testMethod is required:

Set<Sql> sqlAnnotations = AnnotatedElementUtils.getMergedRepeatableAnnotations(
            testContext.getTestMethod(), Sql.class, SqlGroup.class);

I ended up using this workaround:

@Before
public void setUp() {
    // Manually initialize DB as @Sql annotation doesn't support class-level execution phase (actually executed before every test method)
    // See https://jira.spring.io/browse/SPR-14357
    if (!dbInitialized) {
        final ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
        resourceDatabasePopulator.addScript(new ClassPathResource("/sql/[...].sql"));
        resourceDatabasePopulator.execute(dataSource);
        dbInitialized = true;
    }
    [...]
}

Youthful

Answer 6

For JUnit 5 I second the solution by adrhc.

For Junit 4, you can do:

@Autowired
private DataSource database;

private static boolean dataLoaded = false;

    @Before
    public void setup() throws SQLException {
        if(!dataLoaded) {
            try (Connection con = database.getConnection()) {
                ScriptUtils.executeSqlScript(con, new ClassPathResource("path/to/script.sql"));
                dataLoaded = true;
            }
        }
    }

(Again, assuming your connection has autoCommit=true, see post by adrhc.)

If you intend to run your tests in parallel then you'll want to make the method synchronized.

Klina

Answer 7

You can make a config class to execute whatever you want on startup of Spring Boot application with @EventListener. In this method, you can execute the SQL scripts from your resources folder:

@Configuration
public class SqlConfig {

    @Autowired
    private DataSource dataSource;

    @EventListener
    public void onApplicationEvent(@NonNull ContextRefreshedEvent event) {
        try (Connection conn = dataSource.getConnection()) {
            ScriptUtils.executeSqlScript(conn, new ClassPathResource("/script1.sql"));
            ScriptUtils.executeSqlScript(conn, new ClassPathResource("/script2.sql"));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

Then, in your test class, you can configure when to restart the application with @DirtiesContext.

You can run Spring Boot application once for all tests:

@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_CLASS)
class FinalizarRegraStepTest {

    @Test
    void myTest() {
        ...    
    }

}

Or run Spring Boot application before each test:

@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD) 
class FinalizarRegraStepTest {

    @Test
    void myTest() {
        ...    
    }

}

Snail