0

Consider the query

INSERT INTO order (id, key, value) VALUES (?, ?, ?);

There are MySQL syntax errors in the above query as order and key are keywords in MySQL. So the solution would be to add backticks in the query.

INSERT INTO `order` (id, `key`, value) VALUES (?, ?, ?);

Hibernate is dumb enough not to do this automatically. According to this answer by adding the following property to the hibernate configuration file solves the problem with the table name.

<property name="hibernate.globally_quoted_identifiers">true</property>

But this only adds backticks to the database name and the table name. Problem is still there with the column name.

How can I add backticks to all the column names?

  • Hibernate version : 3.6.10.Final
  • MySQL version : 5.7
  • hibernate.dialect : org.hibernate.dialect.MySQL5Dialect
  • JDK version : 1.8 (if that has to do with anything)
Roshana Pitigala
  • 8,437
  • 8
  • 49
  • 80

2 Answers2

0

I have found a way to add backticks to the column names in hibernate.

  1. Open the xml file (TableName.hbm.xml) relative to the pojo file which has been mapped.

  2. Find the property for the relative column.

    <property name="key" type="string">
    <column name="key" />
</property>

  3. Surround the column name with square brackets.

    <property name="key" type="string">
    <column name="[key]" />
</property>

But this is a painstaking process as You'll have to do this to all the columns one by one for all the MySQL keywords. The best way is to avoid adding MySQL keywords as your database, table or column name.

References :

Roshana Pitigala
  • 8,437
  • 8
  • 49
  • 80
0

The best solution is:

  1. choose the right dialect class (e.g. if it is mysql8.0, use MySQL8Dialect; mysql5.7, use MySQL57Dialect...)
  2. configure hibernate.auto_quote_keyword=true (default:false)

The correct dialect class will register all key words of the corresponding mysql version. the #2 will enable the quote for key words in all statements (DDL, select, insert...).

Yao
  • 21
  • 1