Error - Unsuccessful: drop table if exists user - Springboot and H2

Question

I am getting the following errors in Intellij whilst trying to connect to a H2 database.

Hibernate: create table user (id bigint not null, email varchar(255), name varchar(255), primary key (id))

2022-11-22 15:53:11.743 WARN 13376 --- [ main] o.h.t.s.i.ExceptionHandlerLoggedImpl : GenerationTarget encountered exception accepting command : Error executing DDL "create table user (id bigint not null, email varchar(255), name varchar(255), primary key (id))" via JDBC Statement

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

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table [*]user (id bigint not null, email varchar(255), name varchar(255), primary key (id))"; expected "identifier"; SQL statement:

create table user (id bigint not null, email varchar(255), name varchar(255), primary key (id)) [42001-214]

could not prepare statement; SQL [insert into user (email, name, id) values (?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

My Code:

Application.java

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Bean
    CommandLineRunner init(UserRepository userRepository) {
        return args -> {
            Stream.of("John", "Julie", "Jennifer", "Helen", "Rachel").forEach(name -> {
                User user = new User(name, name.toLowerCase() + "@domain.com");
                userRepository.save(user);
            });
            userRepository.findAll().forEach(System.out::println);
        };
    } //* for reference
}

The code from "@Bean" to the "//*" seems to be causing the error, as when deleted it has no errors when running. However unsure how to word it in order to not receive the error. Following this tutorial.

UserController.java

@RestController
@CrossOrigin(origins = "http://localhost:4200")
public class UserController {


    private final UserRepository userRepository;

    public UserController(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @GetMapping("/users")
    public List<User> getUsers() {
        return (List<User>) userRepository.findAll();
    }

    @PostMapping("/users")
    void addUser(@RequestBody User user) {
        userRepository.save(user);
    }
}

User.java

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
    private final String name;
    private final String email;

    public User() {
        this.name = "";
        this.email = "";
    }

    public User(String name, String email) {
        this.name = name;
        this.email = email;
    }

    public long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getEmail() {
        return email;
    }

    @Override
    public String toString() {
        return "User{" + "id=" + id + ", name=" + name + ", email=" + email + '}';
    }

application.properties

spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = update
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.H2Dialect

**What I have tried **

I have read gone through some previous questions here and adjusted the application.properties to include the dialect as most places have mention this as the issue.

As stated I have removed the marked code in Application.java and the it seems to be fine once that has been done so that area I feel is the issue.

Answer 1

After a lot of searching I found that if I removed this from the application properties

spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = update
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.H2Dialect

And Added:

spring.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.jpa.properties.hibernate.globally_quoted_identifiers_skip_column_definitions = true 

Answer 2

In your case, there are some reserved keywords in H2, and one of them is User. You are creating a user table with the word User, which is reserved in H2. Try changing the table name to something other than User, such as Users. Alternatively, when you are writing the database URL in application.properties, add ;NON_KEYWORD=USER. You can find other reserved words of H2 here.

Answer 3

Use int instead of long with:

  @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)

(not GenerationType.AUTO) and also make repository class as extends CrudRepository<User, Integer> as well(note that it is not primitive type int, it is class Integer).

And be sure that the @Id comes from:

javax.persistence.Id