Fix Spring Boot H2 JdbcSQLSyntaxErrorException “Table not found”

1. Introduction

H2 provides a simple in-memory and lightweight database that Spring Boot can configure automatically, making it easy for developers to test data access logic.

Typically, org.h2.jdbc.JdbcSQLSyntaxErrorException, as the name indicates, is thrown to signal errors related to SQL syntax. Therefore, the message “Table not found” denotes that H2 fails to find the specified table.

So, in this short tutorial, we’ll learn how to produce and fix the H2 exception: JdbcSQLSyntaxErrorException: Table not found.

2. Practical Example

Now that we know the root cause behind the exception, let’s see how to reproduce it in practice.

2.1. H2 Configuration

Spring Boot configures the application to connect to the embeddable database H2 using the username sa and an empty password. So, let’s add these properties to the application.properties file:

spring.datasource.url=jdbc:h2:mem:mydb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

Now, let’s assume we have a table called person. Here, we’ll use a basic SQL script to seed the database with data. By default, Spring Boot picks up the data.sql file:

INSERT INTO "person" VALUES (1, 'Abderrahim', 'Azhrioun');
INSERT INTO "person" VALUES (2, 'David', 'Smith');
INSERT INTO "person" VALUES (3, 'Jean', 'Anderson');

2.2. Object Relational Mapping

Next, let’s map our table person to an entity. To do so, we’ll rely on JPA annotations.

For instance, let’s consider the Person entity:

@Entity
public class Person {

    @Id
    private int id;
    @Column
    private String firstName;
    @Column
    private String lastName;

    // standard getters and setters

}

Overall, the @Entity annotation indicates that our class is an entity that maps the person table. Moreover, @Id denotes that the id property represents the primary key and the @Column annotation allows binding a table column to an entity field.

Next, let’s create a JPA repository for the entity Person:

@Repository
public interface PersonRepository extends JpaRepository<Person, Integer> {
}

Notably, Spring Data JPA provides the JpaRepository interface to simplify the logic of storing and retrieving data.

Now, let’s try to start our Spring Boot application and see what happens. Looking at the logs, we can see that the application fails at startup with org.h2.jdbc.JdbcSQLSyntaxErrorException: Table “person” not found:

Error starting ApplicationContext. To display the condition evaluation report re-run your application with 'debug' enabled.
...
at com.baeldung.h2.tablenotfound.TableNotFoundExceptionApplication.main(TableNotFoundExceptionApplication.java:10)
...
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "person" not found (this database is empty); SQL statement:
...

What went wrong here is that the data.sql script is executed before Hibernate is initialized. This is why Hibernate doesn’t find the table person.

Typically, this is now the default behavior to align the script-based initialization with other database migration tools such as Liquibase and Flyway.

3. The Solution

Fortunately, Spring Boot provides a convenient way to address this limitation. We can simply set the property spring.jpa.defer-datasource-initialization to true in application.properties to avoid the exception:

spring.jpa.defer-datasource-initialization=true

That way, we override the default behavior and defer data initialization after Hibernate initialization.

Finally, let’s add a test case to confirm that everything works as expected:

@SpringBootTest(classes = TableNotFoundExceptionApplication.class)
class TableNotFoundExceptionIntegrationTest {

    @Autowired
    private PersonRepository personRepository;

    @Test
    void givenValidInitData_whenCallingFindAll_thenReturnData() {
        assertEquals(3, personRepository.findAll().size());
    }
}

Unsurprisingly, the test passes with success.

4. Conclusion

In this short article, we learned what causes H2 to throw the error JdbcSQLSyntaxErrorException: Table not found. Then, we saw how to reproduce the exception in practice and how to fix it.