Синтаксическая ошибка Spring Boot Hibernate в инструкции SQL

Question

Синтаксическая ошибка Spring Boot Hibernate в инструкции SQL

Я изменил пример данных Spring Boot JPA (https://github.com/spring-guides/gs-accessing-data-jpa.git ) незначительное добавление объекта заказа и соответствующего ему множества отображений от клиента. Когда я запускаю пример, в строках инструкций SQL, записанных Hibernate, есть несколько синтаксических ошибок. Я пытаюсь понять, почему? Я вставил код для сущностей и вывод консоли из приложения ниже.

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

}



}

И соответствующий порядок сущность:

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;

}

И вывод консоли:

    .   ____          _            __ _ _

     /\ / ___'_ __ _ _(_)_ __  __ _    

    ( ( )___ | '_ | '_| | '_ / _` |    

     \/  ___)| |_)| | | | | || (_| |  ) ) ) )

      '  |____| .__|_| |_|_| |___, | / / / /

     =========|_|==============|___/=/_/_/_/

     :: 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

Мысли?

ТИА,
- Ole

558 2

spring-boot spring hibernate jpa spring-data-jpa

2 ответов:

Comments

Ничего не найдено.

Sergii · Accepted Answer · 2017-07-26 12:32:09

Попробуйте изменить свою сущность Order пожалуйста:
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;
}
Пояснение:

Обратите внимание на аннотацию @Table. Используя эту аннотацию, я задал имя таблицы как order_table. В вашем случае по умолчанию hibernate попытался сгенерировать таблицу order. ORDER - служебное слово в любом sql. Исключение возникло из-за того, что hibernate генерировал оператор *** для таблицы order, но db ожидал имя таблицы, а не служебное слово order.

paucls · Accepted Answer · 2018-07-13 16:10:55

У меня была точно такая же проблема с сущностью Order, решенная путем явного определения имени таблицы.
@Entity
@Table(name = "order_table")
class Order(
        @Id
        @GeneratedValue(generator = "system-uuid")
        @GenericGenerator(name = "system-uuid", strategy = "uuid2")
        val orderId: String,
        val customerId: String,
        ...
)