Warning in SQL statement when creating table using Spring Data JPA and error when inserting into the table

Question

I created a table using Spring Data JPA, so that means I did not type any SQL code. Now I am getting a warning about a Syntax error in my SQL statement like this:

Syntax error in SQL statement "create table [*]user (id bigint not null, city varchar(255), fullname varchar(255), password varchar(255), phone_number varchar(255), state varchar(255), street varchar(255), username varchar(255), zip varchar(255), primary key (id))"; expected "identifier";

This is a section of my "User" domain for getting user information

@Entity
@Data
@NoArgsConstructor(access = AccessLevel.PRIVATE, force = true)
@RequiredArgsConstructor
public class User implements UserDetails{

    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    
    private final String username;
    private final String password;
    private final String fullname;
    private final String street;
    private final String city;
    private final String state;
    private final String zip;
    private final String phoneNumber;
    

Other methods are irrelevant because they just override the UserDetails interface boolean methods to return just "true"

Now I get the Syntax error exception Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException:. And when I run and navigate to my /register page on the web, I fill in the user details and submit for it to be inserted into the UserRepository:

import org.springframework.data.repository.CrudRepository;
import tacos1.User;

public interface UserRepository extends CrudRepository<User, Long> {

    User findByUsername(String username);
}

I get this error on the web:

There was an unexpected error (type=Internal Server Error, status=500).
could not prepare statement; SQL [insert into user (city, fullname, password, phone_number, state, street, username, zip, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)];

caused by:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "insert into [*]user (city, fullname, password, phone_number, state, street, username, zip, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)"; expected "identifier"; 

Answer 1

If you are using H2 database for your application, "User" is a reserved keyword as can be seen in the documentation.

A quick fix would be to rename your entity name to something like "Users" or you can add NON_KEYWORDS=user to your JDBC URL.

spring.datasource.url=jdbc:h2:mem:mydb;NON_KEYWORDS=user