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

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.