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.