In this short tutorial, we’ll shed light on how to fix org.h2.jdbc.JdbcSQLSyntaxErrorException: Table not found when working with Spring Boot and H2.

First of all, we’re going to explain the main cause leading H2 to throw the exception. Furthermore, we’ll showcase how to reproduce it and solve it in practice.

Understanding JdbcSQLSyntaxErrorException

Before getting into the nitty-gritty, let’s endeavor to grasp the exception and try to understand it.

Without further ados,** JdbcSQLSyntaxErrorException denotes that there is something wrong with the SQL syntax**. In other words, H2 somehow can’t understand and interpret the provided SQL query.

Typically, the stack trace Table not found says it all. It means that H2 can’t find the specified table because it doesn’t exist.

In general, one of the most common reasons that can lead to the exception is forgetting to specify the correct table name in an SQL query.

However, we need to note that the exception may also occur even if we’re using a table that does exist when working with Hibernate. So, let’s illustrate this in practice.

Practical Example

Now that we identified the underlying cause behind the exception, let’s delve into the details and explore the practical implementation.

First, let’s create a simple Spring Boot application:

    
        @SpringBootApplication
        public class TableNotFoundExceptionApplication {
            public static void main(String... args) {
                SpringApplication.run(TableNotFoundExceptionApplication.class, args);
            }
        }

For instance, let’s assume that our SQL table is employee. Spring Boot offers a convenient way to seed tables with data. This is a very handy feature if we want to initialize our database at startup.

To do so, we need to create a basic SQL script with the name data.sql, and Spring Boot will pick it up automatically:

    
        INSERT INTO employee(id, first_name, last_name, salary) VALUES (1, 'Alex', 'Jones', 4600);
        INSERT INTO employee(id, first_name, last_name, salary) VALUES (2, 'Nicole', 'Diaz', 3450);
        INSERT INTO employee(id, first_name, last_name, salary) VALUES (3, 'Elizabeth', 'Collins', 4600);
        INSERT INTO employee(id, first_name, last_name, salary) VALUES (4, 'Bryce', 'Cruz', 3450);

In short, the employee table is defined by an id, first_name, last_name and a salary.

Next, let’s create an entity class to represent our table. For example, let’s consider the EmployeeEntity class:

    
        @Entity
        @Table("employee")
        public class EmployeeEntity {

            @Id
            private long id;
            @Column(name = "first_name")
            private String firstName;
            @Column(name = "last_name")
            private String lastName;
            @Column(name = "salary")
            private double salary;

            public long getId() {
                return id;
            }
            public void setId(long id) {
                this.id = id;
            }
            public String getFirstName() {
                return firstName;
            }
            public void setFirstName(String firstName) {
                this.firstName = firstName;
            }
            public String getLastName() {
                return lastName;
            }
            public void setLastName(String lastName) {
                this.lastName = lastName;
            }
            public double getSalary() {
                return salary;
            }
            public void setSalary(double salary) {
                this.salary = salary;
            }
        }

As shown above, we used JPA annotations to map the table employee to the class entity EmployeeEntity. The @Entity annotation marks the class as an entity and @Table designates the exact name of the table.

Additionally, @Id denotes that the field id denotes the primary key, and @Column binds each field to the corresponding column.

Here, we’re going to use Spring Data JPA to store and retrieve data from the employee table. So, let’s create a custom JPA repository that extends the built-in JpaRepository interface:

    
        @Repository
        public interface EmployeeRepository extends JpaRepository<EmployeeEntity, Long> {
        }

As shown above, just by writing this line of code, we automatically inherit a set of CRUD methods from JpaRepository without implementing them. By leveraging these built-in methods, we can streamline the process and save a considerable amount of time and effort.

Another important point to mention here is that Spring Boot auto-configures H2 database connection with the username sa and an empty password.

So, let’s define the H2 connection credentiels in application.yaml:

    
        spring:
          datasource:
            url: jdbc:h2:mem:mydb
            username: sa
            password: password
            driverClassName: org.h2.Driver
          jpa:
            database-platform: org.hibernate.dialect.H2Dialect

Now that everything is set up, let’s start our Spring Boot application and see what happens:

    
        [main] ERROR o.s.boot.SpringApplication - Application run failed
        org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' 
        defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: 
        Failed to execute SQL script statement #1 of file [data.sql]: INSERT INTO "employee" VALUES (1, 'Alex', 'Jones', 4600)
        ...
        Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "employee" not found (this database is empty); 
        SQL statement:
        INSERT INTO "employee" VALUES (1, 'Abderrahim', 'Azhrioun') [42104-214]
            at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
            at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
            at org.h2.message.DbException.get(DbException.java:223)
            at org.h2.message.DbException.get(DbException.java:199)
            at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8385)
            at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8369)
            at org.h2.command.Parser.readTableOrView(Parser.java:8358)
            at org.h2.command.Parser.readTableOrView(Parser.java:8328)
            at org.h2.command.Parser.parseInsert(Parser.java:1632)
            at org.h2.command.Parser.parsePrepared(Parser.java:814)
            at org.h2.command.Parser.parse(Parser.java:689)
            at org.h2.command.Parser.parse(Parser.java:661)
            at org.h2.command.Parser.prepareCommand(Parser.java:569)
            at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:631)
            at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:554)
            at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116)
            at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:237)
            at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223)
            at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
            at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
            at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:261)
            ... 28 common frames omitted

As we can see in the logs, Spring Boot fails with JdbcSQLSyntaxErrorException during startup, indicating that there is an issue with the “employee” table: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table “employee” not found.

Typically, the problem here doesn’t stem from the fact that the table “employee” is not present. However, the root cause of the exception comes from Hibernate being initialized after the execution of the data.sql file. As a result, Hibernate can’t locate the “employee” table.

Fixing JdbcSQLSyntaxErrorException: Table not found

By default, Spring Boot uses schema.sql to create the schema and data.sql to populate and seed the tables with data.

However, according to the documentation, Spring Boot loads and executes those SQL scripts before Hibernate initialization.

So, data source initialization takes place before any JPA entities are created. Nonetheless, we can tell Hibernate to defer data source initialization after the creation of the JPA entities.

To do so, all we need to do is just add spring.jpa.defer-datasource-initialization property to application.yaml: 

    
        spring.jpa.defer-datasource-initialization=true

That way, Hibernate will create SQL tables before the execution of any SQL-based script.

Conclusion

To sum it up, we explored what causes Spring Boot to throw org.h2.jdbc.JdbcSQLSyntaxErrorException: Table not found when working with H2 database.

In the process, we explained the main cause of the exception. Then, we demonstrated how to produce and fix it using practical examples.