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.

Poleax

missing part is @column – Anastasius

What is FROM[*] ? – Jolin

Answer 1

@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;

Gubernatorial

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

Answer 2

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;

...

Froe

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

Happened to me as well using 'release' – Conde

Answer 3

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.

Idun

Answer 4

@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.

Jarrodjarrow

Answer 5

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;

Heaton

Answer 6

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

Ioab

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. – Putumayo