Spring Boot with in memory database fails

Question

I'm trying to test my Spring Boot application with an embedded database h2. As for dev and prod, I will be using a MySQL database.

I would like to have different application.yml and schema.sql file for each mode.

The project structure is:

src
--main
----resources
------application.yml
------schema.sql
--test
----resources
------application-test.yml
------schema-test.sql

This is my RespositoryTest :

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
@DataJpaTest
public class IUserRepositoryTest {
@Autowired
private TestEntityManager entityManager;
@Autowired
private IUserRepository userRepository;
@Test
public void Should_ReturnEmployee_ForExistingEmail() {
 User oneUser=new User("john","doe","[email protected]");
 entityManager.persist(oneUser);
 List<User> userList=userRepository.findUserByEmail("[email protected]");
 assertThat(userList).isNotEmpty();
 assertThat(userList.get(0)).isNotNull();
 assertThat(userList.get(0).getEmail()).isEqualTo("[email protected]");
}

This is my test/resources/application-test.yml:

spring: 
 profiles: test
 datasource:
 url: jdbc:h2:mem:test;INIT=create schema IF NOT EXISTS mydb;DB_CLOSE_DELAY=-1
 platform: h2
 username: sa
 password:
 driverClassName: org.h2.Driver
 jpa:
 hibernate:
 ddl-auto: create-drop
 properties:
 hibernate:
 default-schema: mydb
 dialect: org.hibernate.dialect.H2Dialect

This is my test/resources/schema-test.sql:

CREATE SCHEMA IF NOT EXISTS MYDB

As for my main/resources/application.yml:

logging:
 level:
 org.springframework.web: DEBUG
 org:
 hibernate:
 SQL: DEBUG
spring:
 jpa:
 hibernate:
 ddl-auto: update
 properties:
 hibernate:
 dialect: org.hibernate.dialect.MySQL5Dialect
 datasource:
 url: jdbc:mysql://localhost:3306/mydb
 username: root
 password: toor
 database:
 driverClassName: com.mysql.jdbc.Driver 

When I run my app as a spring boot one, the main application.yml is used and all is good, but when I run my tests, I get this error:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
 Caused by: org.h2.jdbc.JdbcSQLException: Schema "MYDB" not found; SQL statement

Which causes all my tests to fail.

When I try to use this project structure:

src
--main
----resources
------application.yml
------schema.sql
--test
----resources
------application.yml
------schema.sql

The test succed but when I run my app as a spring boot, the test/resources/application.yml is the one being used instead of the main one.

Answer 1

Your tests are running with the "default" profile so it will only load the "default" configurations with no suffix (i.e. -test).

Try adding @ActiveProfiles("test") to your test class to enable the test profile.

• Lucy101: Still no luck, same error; should I put application-test in main/resources ?
• Joe Chiavaroli: Apologies I think I misunderstood your question at first. Try using schema-h2.sql and schema-mysql.sql for your test / main schema files respectively.
• Lucy101: It seems to be working, thank you! the working combination is: schema-h2.sql +application-test.yml + @ActiveProfiles("test") in my tests.