SQL 语句中的 Spring Boot Hibernate 语法错误 [英] Spring Boot Hibernate Syntax Error in SQL Statement
本文介绍了SQL 语句中的 Spring Boot Hibernate 语法错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我修改了 Spring Boot JPA 数据示例 (https://github.com/spring-guides/gs-accessing-data-jpa.git) 稍微添加了一个 Order 实体和一个对应的从客户到它的一对多映射.当我运行该示例时，Hibernate 记录的 SQL 语句行中有几个语法错误.我想弄清楚为什么?我已经粘贴了实体的代码和下面应用程序的控制台输出.
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);
}
}
和一个对应的 Order 实体:
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;
}
和控制台输出:
. ____ _ __ _ _
/\ / ___'_ __ _ _(_)_ __ __ _
( ( )\___ | '_ | '_| | '_ / _` |
\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: 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
想法?
TIA，- 奥莱
推荐答案
请尝试更改您的 Order 实体:
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;
}
说明:
注意@Table 注释.使用此注释，我已将表名指定为 order_table.在您的情况下，默认情况下休眠尝试生成表 order.ORDER 是任何 sql 中的服务词.出现异常是因为 hibernate 正在为 order 表生成 *** 语句，但 db 预期的表名不是服务字 order.
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.
这篇关于SQL 语句中的 Spring Boot Hibernate 语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
