SQL错误：1064，SQLState：42000创建新实体时

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

4 个解决方案

#1

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 desc since it's a reserved word in MySQL.

问题在于生成的查询,由于使用了desc,因为它是MySQL中的保留字。

Possible solutions:

Change the name of your column to description. Do similar with name.

将列的名称更改为说明。与名称相似。

Change the configuration in MySQL to support these kind of names for columns in queries.

更改MySQL中的配置以支持查询中列的这些类型的名称。

Change the name of the column in the fields to append ` character (referenced from Creating field with reserved word name with JPA):

更改字段中列的名称以附加`字符(从带有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是未来数据库使用的最佳解决方案。

#2

You can also use square brackets or double quotes to escape column name.

您还可以使用方括号或双引号来转义列名。

@Column(name="[desc]")
private String townDesc;

@Column(name="\"desc\"")
private String townDesc;

#3

desc is a mysql reserve word.Check

desc是一个mysql保留字。检查

Solution:

 @Column(name="[desc]")
               ^    ^

Add square brackets [] .

添加方括号[]。

Change the name of column

更改列的名称

Source

#4

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.

我有同样的问题,但我已经命名了我的一个列'order'。当然,这也是一个保留词。容易犯错。

#1