org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement in SpringBoot with h2 and JPA

Question

While running spring boot with h2 database and JPA i am getting below error.

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440) [hibernate-core-5.2.17.Final.jar:5.2.17.Final]

It is caused due to below one

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE EXCHANGE_VALUE (ID INTEGER NOT NULL, CONVERSION_MULTIPLE DECIMAL(19,2), FROM[*] VARCHAR(255), PORT INTEGER NOT NULL, TO VARCHAR(255), PRIMARY KEY (ID)) "; expected "identifier"; SQL statement:
create table exchange_value (id integer not null, conversion_multiple decimal(19,2), from varchar(255), port integer not null, to varchar(255), primary key (id)) [42001-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) ~[h2-1.4.197.jar:1.4.197]
    at org.h2.message.DbException.getSyntaxError(DbException.java:217) ~[h2-1.4.197.jar:1.4.197]

My hibernate class

import java.math.BigDecimal;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="Exchange_Value")
public class ExchangeValue {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private int id; 
    private String from;
    private String to;
    private BigDecimal conversionMultiple;
    private int port;

    public ExchangeValue() {

    }

    public ExchangeValue(String from, String to, BigDecimal conversionMultiple) {
        super();
//      this.id = id;
        this.from = from;
        this.to = to;
        this.conversionMultiple = conversionMultiple;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }   
}

application.properties is below

spring.application.name=currency-exchange-service
server.port=8000
spring.jpa.hibernate.ddl-auto= create-drop

Just want to know as to what i am missing in the code tried adding spring.jpa.hibernate.ddl-auto= create-drop but it did not helped.

missing part is @column

this_is_om_vm
– this_is_om_vm

2018-10-22 04:29:08 +00:00
Commented Oct 22, 2018 at 4:29 — this_is_om_vm
– this_is_om_vm, Commented Oct 22, 2018 at 4:29
What is FROM[*] ?

Ori Marko
– Ori Marko

2018-10-22 04:32:50 +00:00
Commented Oct 22, 2018 at 4:32 — Ori Marko
– Ori Marko, Commented Oct 22, 2018 at 4:32

Ganesh · Accepted Answer · 2019-06-10 09:00:02Z

30

@shubh.. Your Entity Field names are matching with SQL reserved keywords,

So try to change the field names otherwise use name attribute with @Column Annotation (which gives alias names to the DATABASE)

    @Column(name="valueFrom") 
    private String from;

    @Column(name="valueTo") 
    private String to;

    private BigDecimal conversionMultiple;
    private int port;

edited Jun 10, 2019 at 9:00

answered Oct 22, 2018 at 5:41

Ganesh

6,0663 gold badges26 silver badges43 bronze badges

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

1 Comment

Jan Over a year ago

Had the same problem with a attribute/column named value which is also a reserved keyword.

Khairul Bashar Lemon · Accepted Answer · 2018-10-22 04:41:02Z

3

Your Entity Field name from was matched with database reserved word from, change the field name to another, or add a @Column annotation on that field. Like:

...

@Column(name = "_from")
private String from;

...

answered Oct 22, 2018 at 4:41

Khairul Bashar Lemon

6778 silver badges9 bronze badges

2 Comments

shubh Over a year ago

Thanks this worked. I like stack overflow too much few things that u forget people can observe.

superevil Over a year ago

Happened to me as well using 'release'

S Debasish Nayak · Accepted Answer · 2019-01-21 09:50:03Z

0

I faced the same issue. I did the mistake in giving the schema name in mysql database.

In spring.properties -> (spring boot application)
spring.datasource.url=jdbc:mysql://localhost:3306/db_microservice

Here instead of "db_microservice" I Have given the name as "db-microservice". So don't use "-". And this solved my issue.

answered Jan 21, 2019 at 9:50

S Debasish Nayak

2633 silver badges3 bronze badges

Comments

Subbre · Accepted Answer · 2019-06-22 13:59:06Z

0

@ganesh045's response isnt actually true because hibernate's table creation query works like this: CREATE TABLE <your_table> 'password' for example to each of your attributes. Adding the `` to the attribute will make SQL read it NOT as reserved keyword. It also does the same when it queries for your select clauses inside JpaRepository. It will make a call such as this: SELECT 'attribute' FROM <your_table>. Your problem is most likely that you specified your schema name as kebab-case or anything which is not camelCase and snake_case. Also, with this approach you can have a table called User with attribute's username and password which actually also are MYSQL reserved keywords.

edited Jun 22, 2019 at 13:59

answered Jun 22, 2019 at 13:50

Subbre

733 bronze badges

Comments

jfk · Accepted Answer · 2022-02-11 10:21:52Z

0

Adding to others observation, I got the same error when a column name contains hyphen.

Error

 @Column(name = "COST-CENTER")
   private String costCenter;

Fixed

  @Column(name = "COST_CENTER")
   private String costCenter;

answered Feb 11, 2022 at 10:21

jfk

5,50741 silver badges37 bronze badges

Comments

DOĞUŞ İZGİ · Accepted Answer · 2022-11-01 12:58:30Z

0

in my problem, database name was 'user'. that's why was giving a error. I changed database name, bug fixed.

answered Nov 1, 2022 at 12:58

DOĞUŞ İZGİ

911 silver badge5 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement in SpringBoot with h2 and JPA

6 Answers 6

1 Comment

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

2 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related