Fixing Spring Boot H2 Exception: “Schema not found”

1. Overview

H2 is an open-source SQL database often used for testing purposes in the Java community. Its in-memory nature doesn’t persist anything to the disk and that makes it very fast.

We may encounter an error message “Schema not found” when we integrate it with Spring Boot. In this tutorial, we’ll explore the cause of it and look into two different approaches to resolve it.

2. Understanding the Cause

The default schema of H2 is PUBLIC. If we map a JPA entity class not using the PUBLIC schema, we must ensure the schema is created on H2. Spring Boot reports an error message “Schema not found” when the target schema doesn’t exist.

To mimic the scenario, let’s create the following entity class and repository in a Spring Boot application. The @Table annotation specifies the table mapping details that the entity maps to the student table within the test schema:

@Entity
@Table(name = "student", schema = "test")
public class Student {
    @Id
    @Column(name = "student_id", length = 10)
    private String studentId;

    @Column(name = "name", length = 100)
    private String name;

    // constructor, getters and setters
}

public interface StudentRepository extends JpaRepository<Student, String> {
}

Next, we start the Spring Boot application and access the repository. We’ll encounter an exception thrown indicating the schema doesn’t exist. We can verify this with an integration test:

@ExtendWith(SpringExtension.class)
@SpringBootTest(classes = SampleSchemaApplication.class)
class SampleSchemaApplicationIntegrationTest {
    @Autowired
    private StudentRepository studentRepository;

    @Test
    void whenSaveStudent_thenThrowsException() {
        Student student = Student.builder()
          .studentId("24567433")
          .name("David Lloyds")
          .build();

        assertThatThrownBy(() -> studentRepository.save(student))
          .isInstanceOf(InvalidDataAccessResourceUsageException.class);
    }
}

We’ll see the following error message in the console upon the test execution:

org.hibernate.exception.SQLGrammarException: could not prepare statement [Schema "TEST" not found; SQL statement:
select s1_0.student_id,s1_0.name from test.student s1_0 where s1_0.student_id=? [90079-214]] [select s1_0.student_id,s1_0.name from test.student s1_0 where s1_0.student_id=?]

3. Schema Creation via Database URL

To tackle this issue, we must create the corresponding schema when the Spring Boot application starts. There are two different ways to do it.

The first approach is to create the database schema when we establish the database connection. The H2 database URL allows us to execute DDL or DML commands when a client connects to the database via the INIT property. In a Spring Boot application, we could define the spring.datasource.url properties in the application.yaml:

spring:
  jpa:
    hibernate:
      ddl-auto: create
  datasource:
    driverClassName: org.h2.Driver
    url: jdbc:h2:mem:test;INIT=CREATE SCHEMA IF NOT EXISTS test

The initialization DDL creates the schema if it doesn’t exist. Notably, this approach is a dedicated approach for the H2 database, and may not work on other databases.

This approach creates the schema via the database URL without explicitly creating the tables. We rely on automatic schema creation in Hibernate by setting the properties spring.jpa.hibernate.ddl-auto to create in the YAML file.

4. Schema Creation via Initialization Script

The second approach is generic and can also apply to other databases. We create all database components including the schema and tables by an initialization script.

Spring Boot initializes the JPA persistence unit before executing the initialization scripts. Thus, we explicitly disable the automatic schema generation of Hibernate in the application.yaml as our initialization script takes care of it:

spring:
  jpa:
    hibernate:
      ddl-auto: none

If we don’t disable it by changing the ddl-auto from create to none, we encounter the exception of “Schema TEST not found” during the application startup. The schema hasn’t been created yet during the JPA persistence unit initialization.

Now, we can put the schema.sql that creates the test schema and the student table in the resources folder:

CREATE SCHEMA IF NOT EXISTS test;

CREATE TABLE test.student (
  student_id VARCHAR(10) PRIMARY KEY,
  name VARCHAR(100)
);

Spring Boot looks for the DDL script schema.sql in the resources folder to initialize the database during the application startup by default.

5. Conclusion

A “Schema not found” exception is a common issue during Spring Boot application startup integrating with the H2 database. We can avoid these exceptions by ensuring the schema is created through database URL configurations or initialization scripts.