SQL Error: 1064, SQLState: 42000 while creating new entity

Question

I am working on a Spring mvc web application in which I need to create a town using Hibernate. Following is my town model.

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

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

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

Answer 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

The problem is in the generated query, due to the usage of desc since it's a reserved word in MySQL.

Possible solutions:

1. Change the name of your column to description. Do similar with name.
2. Change the configuration in MySQL to support these kind of names for columns in queries.

3. 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`")
   

IMO while option 3 is a quick and dirty solution, I find option 1 as the best solution for future usage of the database.

Answer 2

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;

Answer 3

desc is a mysql reserve word.Check

Solution:

Do

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

Add square brackets [] .

OR

Change the name of column

Source

Answer 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.

Answer 5

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"