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
}
}
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
@Sqland@SqlConfigare equivalent to those supported byScriptUtilsandResourceDatabasePopulatorbut are a superset of those provided by the<jdbc:initialize-database/>XML namespace element.
Bonus
You can mix this approach with other @Sql declarations.
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() { }
}
@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 {
}
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;
}
[...]
}
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.
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:
