Unable to initialize DB for every test method in spring boot

Question

I'm trying to set-up an integration test scenario for spring boot where a fresh H2 database is created and initialized with custom sql code for every test method.

From the docs i learned that all I have to do is add

@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)

to my test class.

I can see from the logs, that this indeed starts multiple application contexts instead of one.

But it seams, that this contexts are initialized before any test is run and actually live in the JVM at the same time. But I think they share one H2 instance. The first time the sql init script executes just fine, but then I get Table already exists errors, because it tries to create tables which are already there.

How can I make sure, that the tests, including the spring aplication context and H2 DB are fully seriazlied?

application.properties

server.port=8001
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa
spring.jpa.show-sql=true
spring.jpa.generate-ddl=false
spring.jpa.hibernate.ddl-auto=none
spring.datasource.schema=classpath*:h2/ddl/infop-schemas.sql, \
 classpath*:h2/ddl/infop-tables-fahrplan.sql, \
 classpath*:h2/ddl/infop-tables-import.sql, \
 classpath*:h2/ddl/infop-tables-stammdaten.sql, \
 classpath*:h2/ddl/infop-tables-statistik.sql, \
 classpath*:h2/ddl/infop-tables-system.sql, \
 classpath*:h2/ddl/infop-tables-utility.sql, \
 classpath*:h2/ddl/infop-sequences.sql, \
 classpath*:h2/ddl/infop-views.sql \
 classpath*:h2/dll/infop-constraints-system.sql \
 classpath*:h2/dll/infop-constraints-stammdaten.sql \
 classpath*:h2/dll/infop-constraints-statistik.sql \
 classpath*:h2/dll/infop-constraints-import.sql \
 classpath*:h2/dll/infop-constraints-fahrplan.sql

Test class:

@ExtendWith(SpringExtension.class)
@SpringBootTest(classes = {TestApplicationDao.class})
@ActiveProfiles("test")
@Transactional
@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)
public class ProtokollIntegrationTest {
 private static final Logger LOGGER = LoggerFactory.getLogger(ProtokollIntegrationTest.class);
 @Test
 public void thatMaxLaufnummerIsFound() {
 LOGGER.debug("thatMaxLaufnummerIsFound()");
 Optional<Protokoll> maxProtokollOpt = protokollRepository.findFirstByAuftragSchrittOrderByLaufnummerDesc(auftragSchritt);
 assertTrue(maxProtokollOpt.isPresent());
 assertEquals(new Integer(9), maxProtokollOpt.get().getLaufnummer());
 }
 @Test
 public void thatNoLaufnummerIsFound() {
 LOGGER.debug("thatNoLaufnummerIsFound()");
 AuftragSchritt as = new AuftragSchritt();
 as.setStatusCode(code);
 auftragSchrittRepository.save(as);
 Optional<Protokoll> maxProtokollOpt = protokollRepository.findFirstByAuftragSchrittOrderByLaufnummerDesc(as);
 assertFalse(maxProtokollOpt.isPresent());
 }
 @Test
 public void thatFindByAuftragSchrittWorksFine() {
 LOGGER.debug("thatFindByAuftragSchrittWorksFine()");
 List<Protokoll> protokollList = protokollRepository.findByAuftragSchritt(auftragSchritt);
 assertNotNull(protokollList);
 assertEquals(10, protokollList.size());
 }
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>com.compay.my-prj</groupId>
 <artifactId>my-prj-dao</artifactId>
 <version>0.2.0-SNAPSHOT</version>
 <parent>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-parent</artifactId>
 <version>2.1.4.RELEASE</version>
 </parent>
 <properties>
 <java.version>1.8</java.version>
 <maven.compiler.source>1.8</maven.compiler.source>
 <maven.compiler.target>1.8</maven.compiler.target>
 <maven.build.timestamp.format>yyyy-MM-dd HH:mm</maven.build.timestamp.format>
 <timestamp>${maven.build.timestamp}</timestamp>
 <junit.jupiter.version>5.4.2</junit.jupiter.version>
 <junit.platform.launcher.version>1.4.2</junit.platform.launcher.version>
 <my-prj.version>8.25.0</my-prj.version>
 </properties>
 <dependencies>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
 </dependency>
 <dependency>
 <groupId>org.apache.commons</groupId>
 <artifactId>commons-lang3</artifactId>
 <version>3.9</version>
 </dependency>
 <dependency>
 <groupId>com.compay.my-prj</groupId>
 <artifactId>my-prj-common-entity</artifactId>
 <version>${my-prj.version}</version>
 <classifier>hibernate</classifier>
 </dependency>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-test</artifactId>
 <scope>test</scope>
 <exclusions>
 <!-- wir werden junit5 verwenden (unten) -->
 <exclusion>
 <groupId>junit</groupId>
 <artifactId>junit</artifactId>
 </exclusion>
 </exclusions>
 </dependency>
 <dependency>
 <groupId>com.compay.my-prj</groupId>
 <artifactId>my-prj-dao-test</artifactId>
 <version>0.5.0</version>
 <scope>test</scope>
 </dependency>
 <dependency>
 <groupId>com.compay.my-prj</groupId>
 <artifactId>my-prj-common-entity-test</artifactId>
 <version>${my-prj.version}</version>
 <scope>test</scope>
 </dependency>
 <dependency>
 <groupId>javax.validation</groupId>
 <artifactId>validation-api</artifactId>
 <version>2.0.1.Final</version>
 <scope>test</scope>
 </dependency>
 <dependency>
 <groupId>org.junit.jupiter</groupId>
 <artifactId>junit-jupiter</artifactId>
 <version>${junit.jupiter.version}</version>
 <scope>test</scope>
 </dependency>
 <dependency>
 <groupId>org.junit.platform</groupId>
 <artifactId>junit-platform-launcher</artifactId>
 <version>${junit.platform.launcher.version}</version>
 <scope>test</scope>
 </dependency>
 <dependency>
 <groupId>com.h2database</groupId>
 <artifactId>h2</artifactId>
 <scope>test</scope>
 </dependency>
 </dependencies>
 <build>
 <plugins>
 <plugin>
 <groupId>org.apache.maven.plugins</groupId>
 <artifactId>maven-source-plugin</artifactId>
 <executions>
 <execution>
 <id>attach-sources</id>
 <goals>
 <goal>jar</goal>
 </goals>
 </execution>
 </executions>
 </plugin>
 <!-- für junit5 -->
 <plugin>
 <groupId>org.apache.maven.plugins</groupId>
 <artifactId>maven-surefire-plugin</artifactId>
 <version>2.22.2</version>
 </plugin>
 </plugins>
 </build>
</project>

Log ouput see https://1drv.ms/t/s!AnJdkNZlKN5ygVi72qB6wL1KOcpZ (sorry, it's to big for SO)

Answer 1

After all this turned out to be a missing dependency!

I did not have

 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-devtools</artifactId>
 <optional>true</optional>
 </dependency>

This causes autoconfiguration to go mad and doing things like intializing the application context in the wrong order and other weired things. To bad this issues no error or warning message at all.

So the learning is:

If you have @DirtiesContext in your code you must add spring-boot-devtools to your dependenies.

Answer 2

In normal JPA/Hibernate scenario where spring.jpa.hibernate.ddl-auto set to create-drop and DirtiesContext set to BEFORE_EACH_TEST_METHOD, it would have worked as jpa/hibernate will try to drop all the tables first and create all the tables again after each test case execution. Create/DROP scenario handled by hibernate(which is not happening in your case)

But according to your setting, i.e. DirtiesContext = BEFORE_EACH_TEST_METHOD and spring.datasource.schema = <multiple-sql-files>, tables are getting created by your scripts but not getting dropped. Because of which you are getting Table already exists error.

I would recommend you to add one more SQL file at the start which consists queries to drop all the created tables/views if exists. It sure will fix your issue.

• BetaRide: By setting up a new application context before every test method, spring should set-up a new in memory db for every test method. So there's no need to delete anything. The problem is that spring creates all necessary contexts before the first test method is hit and they all share the same db. If I add a clean-up script, the error would go away, but I would be left with a db that gets initialized 3 times and then the 3 tests are executed without clean-up in between.
• Abhijeet: @BetaRide your scripts will get re-executed after every test-case run because of which you are getting error. The process would be like Drop-Create-TC1Execute, DROP-CREATE-TC2Excute.... like so
• BetaRide: your scripts will get re-executed after every test-case run That's how it should be. But from setting a break point in org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript I can tell that my scripts are executed 3 times before any test method is called.
• Abhijeet: @BetaRide are you sure scripts are getting executed thrice before first test case? I think it should execute only twice before first test case execution according to the code.
• BetaRide: Yes, I'm sure. I have 3 test methods and DirtyContext is BEFORE_EACH_TEST_METHOD. So I think 3 is ok. The problem is that spring should recreate an application context, including a fresh in memory db just before a test method is executed. But instead it cretaes 3 app contexts right at the beginning and they all point to the same db.