5

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.

7
  • @英語は苦手 I changed that, now the error that I get is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "user" not found (this database is empty); SQL statement: Commented Jun 24, 2022 at 10:56
  • '\'' should be "\" Commented Jun 24, 2022 at 10:56
  • user needs to be specified as "USER" or "user" depending on case of column, but this database is empty message clearly indicates that there are no tables in your database, you need to create them first. Commented Jun 24, 2022 at 11:01
  • @Stultuske I don't think that's the problem. I've looked into it, changed it, still get the this database is empty error. Isn't it supposed to create the DB here, knowing that it's an in-memory DB ? Commented Jun 24, 2022 at 11:01
  • 'user' is a string literal with the value user, it is not the name of a table. Use user or maybe "user" or "USER". Commented Jun 24, 2022 at 11:15

2 Answers 2

7

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.

Sign up to request clarification or add additional context in comments.

1 Comment

Well explained!
-1
 insert into 'user'(id,birthdate,name)
     values (1,sysdate(), 'Mason');

I guess the right query is

6 Comments

I changed that, still get the same error.
please see comments to your question regarding table name and separator (I guess).
'user' is a string literal with the value user, not a table name, so the query is not "right".
in such "strange" databases everything is possible
@Sergey H2 is not a "strange" database.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.