Search code examples
javaspring-bootjunitspring-boot-testjava-resources

Reading.sql file from custom location other than resouces folder using @sql spring boot test

Hi I have written test case using spring data jpa test . Test case are running fine when i put data.sql and schema.sql file inside test/resources folder even though without using @Sql annotation because of default behaviour of spring boot test.

But my requirement is that i have one folder which is parallel to main and test folder i.e integrationTest where my data-h2.sql and schema-h2.sql file is residing . Problem is that I am not able to read these sql file using @Sql Annotation. how to give the path so that i can read sql file from any given custom location

Below is the folder structure and code for reference

enter image description here

Code

@DataJpaTest
@Sql(scripts={"/integrationTest/schema-h2.sql", "/integrationTest/data-h2.sql"})
public class AbcRepositoryTest extends AbstractTestNGSpringContextTests {

}

Error

08:44:45.329 [Test worker] WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 90079, SQLState: 90079 08:44:45.329 [Test worker] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Schema "TEST" not found; SQL statement:

Solution

  • Found the solution for the question i have posted after struggling 3 to 4 hour we need to use SqlScriptsTestExecutionListener in order to read @Sql Scripts from custom location of your choice and put DirtiesContext so that @Sql script run for each of your test case.

    @DataJpaTest
@TestExecutionListeners(listeners = { SqlScriptsTestExecutionListener.class })
@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)
@Sql(scripts = {"file:src/integrationTest/resources/schema-h2.sql","file:src/integrationTest/resources/data-h2.sql"})
public class AbcRepositoryTest extends AbstractTestNGSpringContextTests {
}

    UsefulLink help to solve the issue

    https://github.com/spring-projects/spring-framework/issues/18929

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

    Using annotation @Sql, is it possible to execute scripts in Class level before Method level?