Spring4 JUnit tests : Load SQL to a H2 db

Question

I'm trying to write tests for a Spring Boot (Spring 4) Application.

My Junit test class is configured like this to allow autowired.

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = SpringApp.class)
public class MyServiceTest {
...

My src/main/resources/application.properties is like this

spring.jpa.database=POSTGRESQL
spring.jpa.show-sql=false
spring.jpa.hibernate.ddl-auto=update

spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost/mydb
spring.datasource.username=BNF0016779
spring.datasource.password=

In test context, src/test/resources/application.properties is just empty.

In can query the db as usual, creating objects...

But I'd like to create a data init sql.

To begin with a strange behavior, It seems that Spring loads any "schema.sql" in classpath. Something like the following is not required ?

//This is not required to execute schema.sql
@Configuration
public class DatabaseTestConfig {
    @Bean
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
            .setType(EmbeddedDatabaseType.H2)
            .addScript("classpath:schema.sql")
            .build();
    }
}

Then, I can't create any Table from this SQL. Always receive org.h2.jdbc.JdbcSQLException: Table "MY_TABLE" already exists; SQL statement:

H2 is supposed to be a in-memory DB, no keeping data between two startup ! Why do I receive these errors ?

Any ideas ? Thanks

Answer 1

Spring Boot will in fact execute a file named schema.sql in the root of the classpath by default. Furthermore, Spring Boot will also automatically create an embedded database for your application unless you instruct it otherwise. Consult the "Initialize a database using Spring JDBC" section of the Spring Boot reference manual for details.

H2 is supposed to be a in-memory DB, no keeping data between two startup !

Yes and no.

If Spring Boot creates an embedded H2 database for you, yes it will be in-memory.

However, the database is actually a bean in the ApplicationContext (just like any other Spring-managed component). Thus it lives as long as the ApplicationContext lives, and the Spring TestContext Framework caches contexts between tests: that's one of its main features. In other words, the embedded database will not be recreated between tests (unless you annotate your test classes or test methods with @DirtiesContext). Consult the Context caching section of the Spring Framework reference manual for details.

Regards,

Sam (author of the Spring TestContext Framework)