Java SQL 错误：1064，SQLState：创建新实体时出现 42000
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28946567/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Error: 1064, SQLState: 42000 while creating new entity
提问by Vishal Suri
I am working on a Spring mvc web application in which I need to create a town using Hibernate. Following is my town model.
我正在开发一个 Spring mvc Web 应用程序，我需要在其中使用 Hibernate 创建一个城镇。以下是我的城镇模型。
@Entity
@Table(name="town")
public class TownModel {
@Id
@Column(name="townid")
@GeneratedValue
private Integer townId;
@Column(name="name")
private String townName;
@Column(name="desc")
private String townDesc;
@ManyToOne(optional = true)
@JoinColumn(name="districtid")
private DistrictModel districtModel;
}
I have another entity named district. Town is a part of district and a district can have multiple towns. Following is my district model:
我有另一个名为 District 的实体。城镇是地区的一部分，一个地区可以有多个城镇。以下是我的地区模型：
@Entity
@Table(name="district")
public class DistrictModel {
@Id
@Column(name="districtid")
@GeneratedValue
private Integer districtId;
@Column(name="name")
private String name;
@Column(name="desc")
private String description;
@OneToMany(mappedBy = "districtModel", fetch = FetchType.EAGER)
@Fetch(value = FetchMode.SUBSELECT)
private List<TownModel> townModelList;
}
I am using following Hibernate code for saving town:
我正在使用以下 Hibernate 代码来拯救城镇：
Session session = sessionFactory.getCurrentSession();
session.save(townModel);
But it shows error:
但它显示错误：
21:22:08,104 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--127.0.0.1-9090-1) SQL Error: 1064, SQLState: 42000
21:22:08,112 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--127.0.0.1-9090-1) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc, name) values (2, 'Test town desc.', 'Test town')' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc, name) values (2, 'Test town desc.', 'Test town')' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.7.0_45]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[na:1.7.0_45]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_45]
at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_45]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.Util.getInstance(Util.java:360) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077) ~[com.mysql.jdbc_5.1.5.jar:na]
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062) ~[com.mysql.jdbc_5.1.5.jar:na]
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493) ~[na:na]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:186) ~[hibernate-core-4.2.16.Final.jar:4.2.16.Final]
... 64 common frames omitted
采纳答案by Luiggi Mendoza
The error messages states:
错误消息指出：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc, name) values (2, 'Test town desc.', 'Test town')' at line 1
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的“desc, name) 值 (2, 'Test town desc.', 'Test town')' 附近使用的正确语法
The problem is in the generated query, due to the usage of descsince it's a reserved word in MySQL.
问题出在生成的查询中，desc因为它是 MySQL 中的保留字。
Possible solutions:
可能的解决方案：
- Change the name of your column to
description. Do similar withname. - Change the configuration in MySQL to support these kind of names for columns in queries.
Change the name of the column in the fields to append ` character (referenced from Creating field with reserved word name with JPA):
@Column(name="`desc`")
- 将列的名称更改为
description. 做类似的name。 - 更改 MySQL 中的配置以支持查询中列的这些类型的名称。
将字段中的列名称更改为附加 ` 字符（从使用 JPA 创建带有保留字名称的字段引用）：
@Column(name="`desc`")
IMO while option 3 is a quick and dirty solution, I find option 1 as the best solution for future usage of the database.
IMO 虽然选项 3 是一个快速而肮脏的解决方案，但我发现选项 1 是未来使用数据库的最佳解决方案。
回答by silentprogrammer
回答by Maro? Tyrpák
You can also use square brackets or double quotes to escape column name.
您还可以使用方括号或双引号来转义列名。
@Column(name="[desc]")
private String townDesc;
or
或者
@Column(name="\"desc\"")
private String townDesc;
回答by Daisy Day
I had the same problem but I'd named one of my columns 'order'. Of course, this is also a reserved word. Easy mistake to make.
我遇到了同样的问题，但我将其中一列命名为“订单”。当然，这也是一个保留字。容易犯错。
回答by Ma Zhiyuan
I add a table named 'order' in MySQL Workbench, it doesn't give me an error.
But when I execute the query by Spring JPA
It gives out the error.
That's because my table name "order"is a reserved word.
Just make a little change and the error has gone.
change table name from "order" to "orders"
我在 MySQL Workbench 中添加了一个名为“order”的表，它没有给我一个错误。
但是当我通过 Spring JPA 执行查询时，它给出了错误。
那是因为我的表名“order”是一个保留字。
只需稍作更改，错误就消失了。
将表名从“order”更改为“orders”
