How to use database reserved keyword in Hibernate ?
In Hibernate, when you try to save an object into a table with any database reserved keyword as column name, you may hit the following error …
ERROR JDBCExceptionReporter:78 - 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 'Datadabase reserved keyword....
Reserved keyword “DESC”
In MySQL, “DESC” is the reserved keyword. Let see some examples to demonstrate how to use this reserved keyword in Hibernate.
Hibernate XML Mapping file
This is the default XML mapping file implementation for a table column, it will cause JDBCException…
<property name="desc" type="string" >
<column name="DESC" length="255" not-null="true" />
</property>
Solution
1. Enclose the keyword with square brackets [].
<property name="desc" type="string" >
<column name="[DESC]" length="255" not-null="true" />
</property>
2. Use single quote(‘) to enclose the double quotes (“)
<property name="desc" type="string" >
<column name='"DESC"' length="255" not-null="true" />
</property>
Hibernate Annotation
This is the default annotation implementation for a table column, it will cause JDBCException…
@Column(name = "DESC", nullable = false)
public String getDesc() {
return this.desc;
}
Solution
1. Enclose the keyword with square brackets [].
@Column(name = "[DESC]", nullable = false)
public String getDesc() {
return this.desc;
}
2. Use double quotes (“) to enclose it.
@Column(name = "\"DESC\"", nullable = false)
public String getDesc() {
return this.desc;
}
Conclusion
This same solution can also applied to the reserved keyword as table name.
Thank you so much. I also used it for table name
@Table(name = “[order]”)
This solution was very helpful. Because we have to support multiple database types, and multiple versions of our app, I was struggling to find a solution to a few table and column names that MySQL considers reserved words.
In my situation, I found the brackets worked fine around the column name, but not around the table name. I had to use the single-quote / double-quote solution (‘”table_name”‘). Perhaps this is because I’m running an old version of hibernate for this particular app.
Great, concise solution!!
thank u very much <3
This helped me thanks. My column name was Group, changed it to [Group]
in mysql it is not working i think. Because I have to generate a column with name schema and type.
Hi,
Thank you very much for the solution. It works.
But I have special case: I have B extends A. A has a field [range] which is a reserved word.
When I do in HQL: “select a from A a”, Hibernate transforms the HQL in
select ‘range’ from (select range from B union …with other child tables…) a. As you can see the range field used in the in the child query is not quoted.
For child tables does not work this solution. It might be a Hibernate bug.
What do you think?