Partner – DBSchema – NPI (tag = SQL)
announcement - icon

DbSchema is a super-flexible database designer, which can take you from designing the DB with your team all the way to safely deploying the schema.

The way it does all of that is by using a design model, a database-independent image of the schema, which can be shared in a team using GIT and compared or deployed on to any database.

And, of course, it can be heavily visual, allowing you to interact with the database using diagrams, visually compose queries, explore the data, generate random data, import data or build HTML5 database reports.

>> Take a look at DBSchema

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:

public class User {

    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:

public interface UserRepository extends JpaRepository<User, Integer> {

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

public void givenValidInitData_whenCallingFindAll_thenReturnData() {
    List<User> users = userRepository.findAll();


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:


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.

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.

Course – LSD (cat=Persistence)

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

res – Persistence (eBook) (cat=Persistence)
Comments are open for 30 days after publishing a post. For any issues past this date, use the Contact form on the site.