Reputation: 1954
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
Upvotes: 16
Views: 22893
Reputation: 37
You are most likely getting the error because you are using a new version of H2. The keywords present in the below mentioned address are reserved and if used will throw compilation error.
http://www.h2database.com/html/advanced.html#keywords
Refrain from using them, and you are good to go!
Upvotes: 4
Reputation: 51
I think there got a default table as "user" so you have to change the table name to "users" or any other name.
This worked for me :-
@Table(name = "users")
Upvotes: 5
Reputation: 2778
The user
keyword is reserved in later versions of H2.
For me this worked using H2 v2.1.212 to get around the issue:
@Table(name = "\"user\"")
but this also worked (from a gtree's answer):
@Table(name = "`user`")
Upvotes: 6
Reputation:
Update: I think there are several factors that may affect this. Dialects - backticks doesn't seem to work for me anymore on org.hibernate.dialect.H2Dialect or org.hibernate.dialect.PostgreSQL10Dialect.
However, escaped double quotes works for com.h2database:h2:1.4.200, but not on com.h2database:h2:2.1.210, which seems to support what you are seeing when removing your versioning (defaulting to the lower transitory hibernate version).
I am also seeing this when I run your code on hibernate version 2.1.210. You are using a reserved word from the Keywords / Reserved Words. There are 3 solutions I have previously used.
It is a common problem, especially with generic table names e.g. group, order, case, etc..
If you are interesting in keeping the name, you can use either or the two:
@Table(name=""user"")
Or,
@Table(name="`user`") - may not work
Or, use the globally quoted identifiers property:
hibernate.globally_quoted_identifiers=true
Using these will allow you to escape SQL reserved words - . I only have a one table with this problem, so the ticks work for me. You can verify the output is correct by turning on the sql prepared statements and formatting:
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true
It would be a nice feature to request to be able to have a property for something like this under the validate:
hibernate.hbm2ddl.auto=validate
If you are interested in submitting a bug, you can create a test case on their site and submit it enter link description here.
What is strange to me is why removing your version from the maven pom worked... that is strange behavior. This could easily be prevalidated through adding this functionality to the validate property and checking the classloader and looping over the table name().
Upvotes: 7
Reputation: 321
i think table name "USER" is reserved. it would work if you change it to "_USER" or "CUSTOM_USER" by using @Table(name="") annotation.
Upvotes: 32