1. Overview
In this short tutorial, we’ll take a close look at the exception org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement expected “identifier”.
First, we will begin by elucidating the leading cause of the exception. Then, we’ll illustrate, using a practical example, how to reproduce it and, finally, how to solve it.
2. The Cause
Before jumping to the solution, let’s understand the exception first.
Typically, H2 throws JdbcSQLSyntaxErrorException to signal a syntax error in an SQL statement. Therefore, the “expected identifier” message indicates that SQL expects a suitable identifier, and we failed to give one.
The most common cause of this exception is using reserved keywords as identifiers.
For example, using the keyword table to name a specific SQL table will lead to JdbcSQLSyntaxErrorException.
Another reason would be missing or misplacing keywords in SQL statements.
3. Reproducing the Exception
As developers, we frequently use the word “user” to denote the table that handles users. Unfortunately, it’s a reserved keyword in H2.
So, to reproduce the exception, we’ll pretend to use the keyword “user”.
Hence, first, let’s add a basic SQL script to initialize and seed the H2 database with data:
INSERT INTO user VALUES (1, 'admin', 'p@ssw@rd');
INSERT INTO user VALUES (2, 'user', 'userpasswd');
Next, we’ll create an entity class to map the user table:
@Entity
public class User {
@Id
private int id;
private String login;
private String password;
// standard getters and setters
}
Please note that @Entity is a JPA annotation that identifies a class as an entity class.
Furthermore, @Id denotes the field that maps the primary key in the database.
Now, if we run the main application, Spring Boot will fail with the following:
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer'
...
nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "INSERT INTO [*]user VALUES (1, 'admin', 'p@ssw@rd')"; expected "identifier"; SQL statement:
INSERT INTO user VALUES (1, 'admin', 'p@ssw@rd') [42001-214]
...
As we can see in the logs, H2 complains about the insert query because the keyword user is reserved and cannot be used as an identifier.
4. The Solution
To fix the exception, we need to make sure that we’re not using SQL-reserved keywords as identifiers.
Alternatively, we can escape them using a delimiter. H2 supports double quotes as the standard identifier delimiter.
So first, let’s double-quote the keyword user:
INSERT INTO "user" VALUES (1, 'admin', 'p@ssw@rd');
INSERT INTO "user" VALUES (2, 'user', 'userpasswd');
Next, we’ll create a JPA repository for our entity User:
@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
}
Now, let’s add a test case to confirm that everything works as expected:
@Test
public void givenValidInitData_whenCallingFindAll_thenReturnData() {
List<User> users = userRepository.findAll();
assertThat(users).hasSize(2);
}
As shown above, findAll() does its job and doesn’t fail with JdbcSQLSyntaxErrorException.
Another workaround to avoid the exception would be appending NON_KEYWORDS=user to JDBC URL:
spring.datasource.url=jdbc:h2:mem:mydb;NON_KEYWORDS=user
That way, we tell H2 to exclude the user keyword from the list of reserved words.
In case we’re using hibernate, we can set the hibernate.globally_quoted_identifiers property to true.
spring.jpa.properties.hibernate.globally_quoted_identifiers=true
As the property name implies, hibernate will automatically quote all database identifiers.
With that being said, we don’t need to manually escape table or column names when using @Table or @Column annotations.
In a nutshell, here are some important key points to consider:
- Make sure to use proper SQL keywords and place them in the correct order
- Avoid using reserved keywords as identifiers
- Double-check any special characters that are not allowed in SQL
- Ensure to properly escape or quote any reserved keyword
5. Conclusion
In this short article, we explained in detail the causes behind the exception org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement expected “identifier”. Then, we showcased how to produce the exception and how to fix it.
As always, the full source code of the examples is available over on GitHub.
