How to cause SpringBoot/Hibernate to create a schema for unit testing?

Question

Environment

SpringBoot 2 with H2 as test dependency.

Production works

Jar is deployed to cloud. A DB2 service is configured with driver and connection details, and automatically bound to the java application. There's no configuration in the jar itself. There is the application.properties file but it's empty. This part works fine and I'm hoping that a solution exists which will not require me to create property files and profiles.

Local Unit Test crashes on 'schema xxx not found'

@Entity(table="Employee", schema="acme")
class Employee {
...
}

@RunWith(SpringRunner.class)
@DataJpaTest
public class EmployeeTest {
...
}

No data source configuration exists.
SpringBoot sees H2 dependency and selects Hibernate by default.
Hibernate sees the Entity definition and attempts to drop the table first.
The drop uses the schema name drop table acme.employee if exists. No schema has been created so process fails with JdbcSQLSyntaxErrorException: Schema "acme" not found.
I tried @TestPropertySource(properties ="jdbc:h2:testb;INIT=CREATE SCHEMA IF NOT EXISTS acme;") with no luck.

I've found issues like this on the web and potential solutions. But they go very far into Hibernate and/or Spring configuration files. I would really want to avoid this. It's only local unit test that fails so I'm hoping to find a solution that is contained within the test.

You can put schema.sql file in your classpath for schema creation schema-h2.sql. https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html#howto-initialize-a-database-using-spring-jdbc — Anant Goswami, Aug 02 '19 at 04:55
For db created by Hibernate then see: https://stackoverflow.com/questions/24278659/change-database-schema-used-by-spring-boot — Alan Hay, Aug 02 '19 at 08:01

score 1 · Answer 1 · answered Jun 28 '21 at 19:19

If you need a different behaviour for your tests, that's basically a different profile. Although you prefer not to define properties files, the solution below doesn't go too deep into configuration and allows you to be very explicit in your tests.

Create application-test.properties (or yml if you prefer) with:

spring.datasource.url = jdbc:h2:mem:testb;init=CREATE SCHEMA IF NOT EXISTS acme;

Select Spring profile in your test classes with the annotation @ActiveProfiles:

@SpringBootTest
@ActiveProfiles("test")
class MyTest {
    ...
}

Give it a go. H2 should create the schema just before Hibernate tries to create the tables.

score 1 · Answer 2 · answered Jun 28 '21 at 20:12

The absolute simplest means of doing this is to use h2 (or hsql as you are), and set ddl-auto to create-drop.

spring:
  datasource:
    driver-class-name: org.h2.Driver
    password:
    url: jdbc:h2:acme
    username: sa
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    hibernate:
      ddl-auto: create-drop

How to cause SpringBoot/Hibernate to create a schema for unit testing?

2 Answers2