H2 Database Tables can't be created for DataJpaTests

Question

My Springboot project uses MariaDB and JPA works fine in normal mode. When I tried to run my DataJpa JUNIT Tests, there was an error. "The Table Company could not be found (the database is empty)".

This is my application.properties File for my normal database.

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mariadb://localhost:3306/app
spring.datasource.username=xxx
spring.datasource.password=xxx
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.jpa.show-sql= true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDBDialect
spring.jpa.properties.hibernate.format_sql=true
spring.data.rest.base-path=/app

This is my application-test.properties File for my test database.

# must actually be named application-test.properties ;-)
# Enable H2 console
spring.h2.console.enabled=true

# H2 console path
spring.h2.console.path=/h2-console

# Configure H2 database
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb;MODE=MYSQL;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username=sa
spring.datasource.password=

# Configure JPA and Hibernate
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true

These are my JUNIT Tests.

package ch.wiss.unternehmensliste.repository;

import ch.wiss.unternehmensliste.model.Company;
import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.Order;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;
import org.springframework.boot.test.autoconfigure.orm.jpa.TestEntityManager;
import org.springframework.test.annotation.Rollback;

import java.util.List;
import java.util.Optional;

@DataJpaTest
class CompanyRepositoryTest {
    @Autowired
    private TestEntityManager entityManager;
    @Autowired
    CompanyRepository companyRepository;

    /**
     * JUnit test for saving a Company
     */

    @Test
    public void saveCompanyTest(){
        Company company = new Company("Microsoft", "www.microsoft.com", "Zürich");
        companyRepository.save(company);

        Assertions.assertThat(company.getId()).isGreaterThan(0);
    }
    
}

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Apr 19 '23 at 07:48
Hello, please check if your are running the test with a test profile? It should be: Dspring.profiles.active=test Because the properties file is named application-test.properties — Mar-Z, Apr 19 '23 at 08:27
You haven't activated the `test` profile so it will ignore your properties. Add `@ActiveProfiles("test")` to your test. — M. Deinum, Apr 19 '23 at 08:29
Actually you shouldn't need that, at is will automatically create an in-memory DB and use that instead of the real one (it will automatically repace the existing database)> — M. Deinum, Apr 19 '23 at 08:46

score 0 · Accepted Answer · answered Apr 19 '23 at 09:22

The configuration spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDBDialect in the application.properties causes the DataJPATest framework to create the tables with engine=InnoDB extension. H2 cannot handle this and fails to create the required tables, therefore the error message "Table not found, database empty". The initial error is only visible on top of the full test output.

Just uncomment the line spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDBDialect in the application properties. In my case, both test and regular app work without that line.

H2 Database Tables can't be created for DataJpaTests

1 Answers1