Spring Boot makes it easy to develop Spring applications with its pre-configured components. However, we often encounter the JdbcSQLSyntaxErrorException: Table not found error when using the H2 database. This error typically occurs due to configuration issues or incorrect SQL queries. In this article, we will explore some common causes of this error and suggest solutions to resolve it.
1. Understanding the Error
The JdbcSQLSyntaxErrorException: Table not found error indicates that the H2 database cannot locate the specified table. This issue often arises during development when the database schema is not properly set up or the application fails to recognize the existing tables.
Also, there could be a timing mismatch when Spring Boot attempts to execute SQL queries before JPA creates the necessary tables. This often occurs if we are using a data.sql script for populating initial data.
2. Reproducing the Error
2.1 Practical Example
Here is a walkthrough of a practical example to demonstrate how to reproduce the JdbcSQLSyntaxErrorException: Table not found error.
Configure application.properties
application.properties
spring.datasource.url=jdbc:h2:mem:testdb spring.datasource.driverClassName=org.h2.Driver spring.datasource.username=sa spring.datasource.password=password spring.h2.console.enabled=true spring.jpa.hibernate.ddl-auto=none
Create a JPA Entity
Product.java
@Entity
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
public Product(Long id, String name) {
this.id = id;
this.name = name;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
Create a Repository Interface
ProductRepository.java
public interface ProductRepository extends JpaRepository<Product, Long> {
}
Adding Sample Data with data.sql
To input sample data into the application using the H2 database, we create a data.sql file in the src/main/resources directory. This file allows us to pre-populate the database with initial data when the application starts. Here is an example data.sql script to insert sample data into the Product table:
data.sql
-- Insert sample data into product table INSERT INTO product (id, name) VALUES (6, 'Adidas'); INSERT INTO product (id, name) VALUES (20, 'Vans');
This script is supposed to add two entries to the Product table, ensuring that when the application starts, these records are available for querying.
Running the Application
After setting up the Product entity and the ProductRepository interface and then running the application, we will encounter the JdbcSQLSyntaxErrorException: Table not found error. The error log will resemble the following:
Error Log Output
This error log output indicates that the table named PRODUCT could not be found in the H2 database, causing the application to fail to run correctly.
3. Fixing the Error
One solution is to use the schema.sql file to create the table automatically. To do this, we need to create a schema.sql file in the src/main/resources directory with the following content:
schema.sql
-- Create product table
CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(255)
);
Restarting the application will create the table automatically, and we will no longer encounter the JdbcSQLSyntaxErrorException: Table not found error exception.
3.1 Some Other Common Causes and Solutions
3.1.1 Spring Boot Deferred Initialization
In some cases, the database initialization may need to be deferred to ensure other beans or resources are available. An example is when the schema is not initialized in time for certain operations, resulting in the error.
Solution
Use the spring.jpa.defer-datasource-initialization property in application.properties to defer the initialization of the DataSource.
spring.jpa.defer-datasource-initialization=true
4. Conclusion
In this article, we explored a few aspects of resolving the JdbcSQLSyntaxErrorException: Table not found error in Spring Boot applications using the H2 database. We showed how to produce and fix the JdbcSQLSyntaxErrorException “Table not found” exception in Spring Boot with H2. We also saw how to use the schema.sql file to create the table automatically.
5. Download the Source Code
This article covered the Spring Boot H2 JdbcSQLSyntaxErrorException: Table not found error.
You can download the full source code of this example here: Spring Boot H2 jdbcsqlsyntaxerrorexception table not found
