Why do I get SQL syntax error ? - expected "identifier"?-CodePudding

I am trying to follow through this tutorial about Spring Boot and I am currently at the JPA section and I've ran into a problem that I cannot solve. Where I am standing currently is I've built the entity, I've got all the dependencies I need for the DB (H2 Database), I've built the controller and the services if that matters for anything here and I get an error when I run my application.

Trying to run this:

insert into 'user'(id,name,birthdate)
values (1,sysdate(), 'Mason');

The error I am getting is this:

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/Users/jsalv/Desktop/tutorial/target/classes/data.sql]: insert into 'user'(id,name,birthdate) values (1,sysdate(), 'Mason'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "insert into [*]'user'(id,name,birthdate) values (1,sysdate(), 'Mason')"; expected "identifier"; SQL statement:
insert into 'user'(id,name,birthdate) values (1,sysdate(), 'Mason') [42001-212]

This is my pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.in28minutes</groupId>
    <artifactId>tutorial</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>tutorial</name>
    <description>Some tutorial</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-hateoas</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

And this is the entity that I am trying to work with:

package com.in28minutes.tutorial.user;

import com.sun.istack.NotNull;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.validation.constraints.Past;
import javax.validation.constraints.Size;
import java.util.Date;
@Entity
public class User {
    @Id
    @GeneratedValue
    private Integer id;
    @Size(min = 2)
    private String name;
    @Past
    private Date birthDate;

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

    public User() {

    }

    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  
                '}';
    }
}

I've looked into a few solutions here on SO, one of which was putting the user in brackets because it's an SQL specific keyword, that didn't work, kept getting the same error. Then I extended the syntax a little bit, looking at this example.

I do not understand where I am going wrong.

CodePudding user response：

According to the documentation h2 documentation,

user is a reserved keyword which could not be used, unless surrounded with double quotes .

Try with

 insert into "user"(id,birthdate,name)
     values (1,sysdate(), 'Mason');

However I would strongly advice of renaming your entity into something that is not a reserved keyword in sql standard. H2 may avoid this with double quottes, but H2 is mostly used as a test database. Your real database may be from another vendor where it fails again, or even worse you switch database vendor some years down the road and the next vendor does not support this reserved keyword with some workaround and then you are doomed for major migrations.

If you insist on this solution take a read first here and also here.

CodePudding user response：

 insert into 'user'(id,birthdate,name)
     values (1,sysdate(), 'Mason');

I guess the right query is