Spring Data JPA (H2 database) is returning DDL error during table creation

Question

I have a Spring boot application where I have H2 as database. I have just one entity which is User. When I run the application, I keep getting DDL errors when creating the table in memory. If I try to access the H2 console via the browser (localhost:8080/h2-console), it won't connect. I believe this is because the table wasn't created successfully.

So far, I have only added @Entity, @Id, @GeneratedValue annotations to my User entity. I even tried changing the fields name from (id,name) to (userId, userName) using @Column(name="user_id") / @Column(name="user_name") because I thought that id and name might be reserved words. However, I keep getting the same DDL errors

Error:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "DROP TABLE IF EXISTS USER[*] CASCADE "; expected "identifier"; SQL statement: drop table if exists user CASCADE [42001-206]

2022-01-21 14:28:13.618 WARN 20700 --- [ restartedMain] o.h.t.s.i.ExceptionHandlerLoggedImpl : GenerationTarget encountered exception accepting command : Error executing DDL "create table user (id integer not null, birth_date timestamp, name varchar(255), primary key (id))" via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table user (id integer not null, birth_date timestamp, name varchar(255), primary key (id))" via JDBC Statement

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE TABLE USER[*] (ID INTEGER NOT NULL, BIRTH_DATE TIMESTAMP, NAME VARCHAR(255), PRIMARY KEY (ID))"; expected "identifier"; SQL statement: create table user (id integer not null, birth_date timestamp, name varchar(255), primary key (id)) [42001-206] at org.h2.message.DbException.getJdbcSQLException(DbException.java:521) ~[h2-2.0.206.jar:2.0.206]

User class

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.validation.constraints.Past;
import javax.validation.constraints.Size;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;

@ApiModel(description="some description here")
@Entity
public class User {
    @Id
    @GeneratedValue
    private Integer id;
    
    @Size(min=2, message="Name should have at least 2 characters")
    @ApiModelProperty(notes="Name should have at least 2 characters")
    private String name;
    
    @Past
    @ApiModelProperty(notes="Birthdate should be in the past")
    @Column(name="birth_date")
    private Date birthDate;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getBirthDate() {
        return birthDate;
    }

    public void setBirthDate(Date birthDate) {
        this.birthDate = birthDate;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", birthDate=" + birthDate + "]";
    }

    public User() {}
    
    public User(Integer id, String name, Date birthDate) {
        super();
        this.id = id;
        this.name = name;
        this.birthDate = birthDate;
    }
}

pom.xml dependency

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <!-- <version>2.6.2</version> -->
    <version>2.5.2</version>
    <relativePath /> <!-- lookup parent from repository -->
</parent>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.0.206</version>
    <scope>runtime</scope>
</dependency>

application.properties

logging.level.org.springframework = info
spring.jackson.serialization.write-dates-as-timestamps=false
management.endpoints.web.exposure.include=*

spring.security.user.name=someusername
spring.security.user.password=somepassword

spring.jpa.show-sql=true
spring.h2.console.enabled=true

Immolate

After reviewing the dependencies in pom.xml, I find out that the reason it is failing is because of an unrecognized h2 version. <version>2.0.206</version> Removing that <version> solved it. – Immolate

Answer 1

i think table name "USER" is reserved. it would work if you change it to "_USER" or "CUSTOM_USER" by using @Table(name="") annotation.

Tragicomedy

It is. You can also add double-quotes or back-tics to escape. This makes it so you don't have to rename an existing table. – Papillote

try: 'spring.jpa.properties.hibernate.auto_quote_keyword=true' – Sybilsybila

Yes indeed, "User" is a reserved word. We should change the name of the class to something like "Users", or define a specific name in our Entity class. – Clubman

Answer 2

Update: I think there are several factors that may affect this. Dialects - backticks doesn't seem to work for me anymore on org.hibernate.dialect.H2Dialect or org.hibernate.dialect.PostgreSQL10Dialect.

However, escaped double quotes works for com.h2database:h2:1.4.200, but not on com.h2database:h2:2.1.210, which seems to support what you are seeing when removing your versioning (defaulting to the lower transitory hibernate version).

---

I am also seeing this when I run your code on hibernate version 2.1.210. You are using a reserved word from the Keywords / Reserved Words. There are 3 solutions I have previously used.

It is a common problem, especially with generic table names e.g. group, order, case, etc..

If you are interesting in keeping the name, you can use either or the two:

@Table(name=""user"")

Or,

@Table(name="`user`") - may not work

Or, use the globally quoted identifiers property:

hibernate.globally_quoted_identifiers=true

Using these will allow you to escape SQL reserved words - . I only have a one table with this problem, so the ticks work for me. You can verify the output is correct by turning on the sql prepared statements and formatting:

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true

It would be a nice feature to request to be able to have a property for something like this under the validate:

hibernate.hbm2ddl.auto=validate

If you are interested in submitting a bug, you can create a test case on their site and submit it enter link description here.

What is strange to me is why removing your version from the maven pom worked... that is strange behavior. This could easily be prevalidated through adding this functionality to the validate property and checking the classloader and looping over the table name().

Nitrogenize

I created a feature request: hibernate.atlassian.net/browse/HHH-15079. I may pick it up myself next week. – Nitrogenize

Thank you for all the notes and answer. I appreciate it. I can use that as future reference. So far, changing the h2 version made it work. – Immolate

You're welcome! spring.jpa.properties.hibernate.format_sql=true is also really helpful to see formatted queries and not just blobs of text in the console. – Nitrogenize

As a side note, check out lambok - this will help remove all of your boiler plate code from your entities with the @ Data and @ AllArgsConstructor @ NoArgsConstructor annotations. Very fun. – Nitrogenize

hibernate.globally_quoted_identifiers=true will also quote tablenames and potentially introduce more errors.... 'spring.jpa.properties.hibernate.auto_quote_keyword=true' is a better solution with a proper list of registered keywords (in your dialect) – Sybilsybila

Answer 3

The user keyword is reserved in later versions of H2.

For me this worked using H2 v2.1.212 to get around the issue:

@Table(name = "\"user\"")

but this also worked (from a gtree's answer):

@Table(name = "`user`")

Papillote

Answer 4

I think there got a default table as "user" so you have to change the table name to "users" or any other name. This worked for me :- @Table(name = "users")

Arlberg

This worked for me too. Looks like there is a different way to escape – Maui

Actually adding this @Table(name = "user") on User Entity class too worked for me – Maui

Answer 5

You are most likely getting the error because you are using a new version of H2. The keywords present in the below mentioned address are reserved and if used will throw compilation error.

http://www.h2database.com/html/advanced.html#keywords

Refrain from using them, and you are good to go!

Illailladvised