Spring Boot Hibernate Syntax Error in SQL Statement

Question

I've modified the Spring Boot JPA Data example (https://github.com/spring-guides/gs-accessing-data-jpa.git) slightly adding an Order entity and a corresponding one to many mapping to it from the customer. When I run the example there are several Syntax Error in SQL Statement lines logged by Hibernate. I'm trying to figure out why? I've pasted the code for the entities and the console output from the application below.

package hello;

import java.util.List;

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

@Entity
public class Customer {

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private long id;
private String firstName;
private String lastName;
@OneToMany(mappedBy="customer")
private List<Order> orders;


protected Customer() {}

public Customer(String firstName, String lastName) {
    this.firstName = firstName;
    this.lastName = lastName;
}

@Override
public String toString() {
    return String.format(
            "Customer[id=%d, firstName='%s', lastName='%s']",
            id, firstName, lastName);
}

}

And a corresponding Order entity:

package hello;

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

@Entity
public class Order {

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private long id;

protected Order() {}

double amount;

@ManyToOne
Customer customer;
}

And the console output:

    .   ____          _            __ _ _
     /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
    ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
     \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
      '  |____| .__|_| |_|_| |_\__, | / / / /
     =========|_|==============|___/=/_/_/_/
     :: Spring Boot ::       (v1.1.10.RELEASE)

    2015-01-16 08:56:53.116  INFO 27810 --- [           main] hello.Application                        : Starting Application on MKI with PID 27810 (/home/ole/Documents/workspace-sts-3.6.3.RELEASE/gs-accessing-data-jpa-complete/target/classes started by ole in /home/ole/Documents/workspace-sts-3.6.3.RELEASE/gs-accessing-data-jpa-complete)
    2015-01-16 08:56:53.149  INFO 27810 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@5d59e6f1: startup date [Fri Jan 16 08:56:53 CST 2015]; root of context hierarchy
    2015-01-16 08:56:54.253  INFO 27810 --- [           main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
    2015-01-16 08:56:54.269  INFO 27810 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
      name: default
      ...]
    2015-01-16 08:56:54.320  INFO 27810 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {4.3.7.Final}
    2015-01-16 08:56:54.322  INFO 27810 --- [           main] org.hibernate.cfg.Environment            : HHH000205: Loaded properties from resource hibernate.properties: {hibernate.connection.charSet=UTF-8, hibernate.dialect=org.hibernate.dialect.H2Dialect, hibernate.show_sql=true, hibernate.export.schema.delimiter=;, hibernate.bytecode.use_reflection_optimizer=false, hibernate.ejb.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy}
    2015-01-16 08:56:54.322  INFO 27810 --- [           main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
    2015-01-16 08:56:54.462  INFO 27810 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
    2015-01-16 08:56:54.499  INFO 27810 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
    2015-01-16 08:56:54.591  INFO 27810 --- [           main] o.h.h.i.ast.ASTQueryTranslatorFactory    : HHH000397: Using ASTQueryTranslatorFactory
    2015-01-16 08:56:54.751  INFO 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export
    Hibernate: alter table order drop constraint FK_m6q2ofkj1g5aobtb2p00ajpqg if exists
    2015-01-16 08:56:54.752 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table order drop constraint FK_m6q2ofkj1g5aobtb2p00ajpqg if exists
    2015-01-16 08:56:54.753 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Syntax error in SQL statement "ALTER TABLE ORDER[*] DROP CONSTRAINT FK_M6Q2OFKJ1G5AOBTB2P00AJPQG IF EXISTS "; expected "identifier"; SQL statement:
    alter table order drop constraint FK_m6q2ofkj1g5aobtb2p00ajpqg if exists [42001-176]
    Hibernate: drop table customer if exists
    Hibernate: drop table order if exists
    2015-01-16 08:56:54.753 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: drop table order if exists
    2015-01-16 08:56:54.753 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Syntax error in SQL statement "DROP TABLE ORDER[*] IF EXISTS "; expected "identifier"; SQL statement:
    drop table order if exists [42001-176]
    Hibernate: create table customer (id bigint generated by default as identity, first_name varchar(255), last_name varchar(255), primary key (id))
    Hibernate: create table order (id bigint generated by default as identity, amount double not null, customer_id bigint, primary key (id))
    2015-01-16 08:56:54.756 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: create table order (id bigint generated by default as identity, amount double not null, customer_id bigint, primary key (id))
    2015-01-16 08:56:54.756 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Syntax error in SQL statement "CREATE TABLE ORDER[*] (ID BIGINT GENERATED BY DEFAULT AS IDENTITY, AMOUNT DOUBLE NOT NULL, CUSTOMER_ID BIGINT, PRIMARY KEY (ID)) "; expected "identifier"; SQL statement:
    create table order (id bigint generated by default as identity, amount double not null, customer_id bigint, primary key (id)) [42001-176]
    Hibernate: alter table order add constraint FK_m6q2ofkj1g5aobtb2p00ajpqg foreign key (customer_id) references customer
    2015-01-16 08:56:54.757 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table order add constraint FK_m6q2ofkj1g5aobtb2p00ajpqg foreign key (customer_id) references customer
    2015-01-16 08:56:54.757 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Syntax error in SQL statement "ALTER TABLE ORDER[*] ADD CONSTRAINT FK_M6Q2OFKJ1G5AOBTB2P00AJPQG FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER "; expected "identifier"; SQL statement:
    alter table order add constraint FK_m6q2ofkj1g5aobtb2p00ajpqg foreign key (customer_id) references customer [42001-176]
    2015-01-16 08:56:54.757  INFO 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000230: Schema export complete
    2015-01-16 08:56:55.085  INFO 27810 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
    2015-01-16 08:56:55.102  INFO 27810 --- [           main] hello.Application                        : Started Application in 2.286 seconds (JVM running for 2.604)
    Hibernate: insert into customer (id, first_name, last_name) values (null, ?, ?)
    Hibernate: insert into customer (id, first_name, last_name) values (null, ?, ?)
    Hibernate: insert into customer (id, first_name, last_name) values (null, ?, ?)
    Hibernate: insert into customer (id, first_name, last_name) values (null, ?, ?)
    Hibernate: insert into customer (id, first_name, last_name) values (null, ?, ?)
    Hibernate: select customer0_.id as id1_0_, customer0_.first_name as first_na2_0_, customer0_.last_name as last_nam3_0_ from customer customer0_
    Customers found with findAll():
    -------------------------------
    Customer[id=1, firstName='Jack', lastName='Bauer']
    Customer[id=2, firstName='Chloe', lastName='O'Brian']
    Customer[id=3, firstName='Kim', lastName='Bauer']
    Customer[id=4, firstName='David', lastName='Palmer']
    Customer[id=5, firstName='Michelle', lastName='Dessler']

    Hibernate: select customer0_.id as id1_0_0_, customer0_.first_name as first_na2_0_0_, customer0_.last_name as last_nam3_0_0_ from customer customer0_ where customer0_.id=?
    Customer found with findOne(1L):
    --------------------------------
    Customer[id=1, firstName='Jack', lastName='Bauer']

    Hibernate: select customer0_.id as id1_0_, customer0_.first_name as first_na2_0_, customer0_.last_name as last_nam3_0_ from customer customer0_ where customer0_.last_name=?
    Customer found with findByLastName('Bauer'):
    --------------------------------------------
    Customer[id=1, firstName='Jack', lastName='Bauer']
    Customer[id=3, firstName='Kim', lastName='Bauer']
    Hibernate: select customer0_.id as id1_0_, customer0_.first_name as first_na2_0_, customer0_.last_name as last_nam3_0_ from customer customer0_
    Hibernate: delete from customer where id=?
    Hibernate: delete from customer where id=?
    Hibernate: delete from customer where id=?
    Hibernate: delete from customer where id=?
    Hibernate: delete from customer where id=?
    2015-01-16 08:56:55.258  INFO 27810 --- [           main] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@5d59e6f1: startup date [Fri Jan 16 08:56:53 CST 2015]; root of context hierarchy
    2015-01-16 08:56:55.259  INFO 27810 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown
    2015-01-16 08:56:55.260  INFO 27810 --- [           main] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
    2015-01-16 08:56:55.261  INFO 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export
    Hibernate: alter table order drop constraint FK_m6q2ofkj1g5aobtb2p00ajpqg if exists
    2015-01-16 08:56:55.261 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: alter table order drop constraint FK_m6q2ofkj1g5aobtb2p00ajpqg if exists
    2015-01-16 08:56:55.261 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Syntax error in SQL statement "ALTER TABLE ORDER[*] DROP CONSTRAINT FK_M6Q2OFKJ1G5AOBTB2P00AJPQG IF EXISTS "; expected "identifier"; SQL statement:
    alter table order drop constraint FK_m6q2ofkj1g5aobtb2p00ajpqg if exists [42001-176]
    Hibernate: drop table customer if exists
    Hibernate: drop table order if exists
    2015-01-16 08:56:55.263 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000389: Unsuccessful: drop table order if exists
    2015-01-16 08:56:55.263 ERROR 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : Syntax error in SQL statement "DROP TABLE ORDER[*] IF EXISTS "; expected "identifier"; SQL statement:
    drop table order if exists [42001-176]
    2015-01-16 08:56:55.263  INFO 27810 --- [           main] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000230: Schema export complete

Thoughts?

TIA, - Ole

Solution 1

Try change your Order entity please:

package hello;

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

@Entity
@Table(name = "order_table")
public class Order {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private long id;

    protected Order() {}

    double amount;

    @ManyToOne
    Customer customer;
}

Explanation:

Pay attention on @Table annotation. Using this annotation I've specified table name as order_table. In your case by default hibernate tried to generate table order. ORDER is service word in any sql. Exception appeared because hibernate was generating *** statement for the order table but db expected table name not service word order.

Solution 2

I had the exact same problem with an Order entity, solved defining explicitly the table name.

@Entity
@Table(name = "order_table")
class Order(
        @Id
        @GeneratedValue(generator = "system-uuid")
        @GenericGenerator(name = "system-uuid", strategy = "uuid2")
        val orderId: String,
        val customerId: String,
        ...
)

Comments

[Marc B]

order is undoubtedly a reserved word. I'll bet you a shiny penny that if you rename the table to something else, e.g. xorder, it'll start working.

[rogerdpack]

looks like we ran into same issue today naming a field "group" oops, thanks @MarcB

[MattC]

I got this when adding a new field 'order'. Changed it to 'executionOrder' and all is good. Thanks.