Hibernate, MySQL and a table named "Repeat" - strange behavior

Question

I have a strange problem. But first, orm.xml:

<entity class="de.test.businessobjects.Repeat"> <table name="repeat"/> <attributes> <id name="id"> <generated-value strategy="TABLE"/> </id> <many-to-one name="repeatType" fetch="LAZY"> <join-column name="id_repeatType"/> </many-to-one> <many-to-one name="trainingSet" fetch="LAZY"> <join-column name="id_trainingSet"/> </many-to-one> </attributes> </entity> 

I am using Hibernate / JPA. Everything works fine with HSQL and Derby, so my BO, DAO and unit tests should be fine. When testing with MySQL, I get this error:

org.springframework.dao.InvalidDataAccessResourceUsageException: cannot complete the request; SQL [select repeat0_.id as id8_, repeat0_.id_repeatType as id2_8_, repeat0_.id_trainingWrite as id3_8_ from repeat repeat00 _];

However change

 <table name="repeat"/> 

to

 <table name="repeatt"/> 

solves a problem with MySQL.

What's wrong? Is "repetition" a reserved keyword or is it a bug in the implementation of Hibernate JPA?

Thanks and greetings Er

Answer 1

SQL Reserved Words Checker tells me that "repeat" is a SQL reserved keyword with MySQL (and DB2), so you need to avoid it.

JPA 1.0 does not define a standard processing method, so you will need to use a Hibernate solution based on inverse outputs. From the Hibernate Reference Guide:

5.4. Identifiers with SQL Codes

You can force Hibernate to quote the identifier in the generated SQL including the table or column name in the backticks in the map document. Hibernate will use the correct quote style for SQL Dialect. These are usually double quotes, but SQL Server uses brackets and MySQL uses backreferences.

 <class name="LineItem" table="`Line Item`"> <id name="id" column="`Item Id`"/><generator class="assigned"/></id> <property name="itemNumber" column="`Item #`"/> ... </class> 

I assume this will work in orm.xml .

JPA 2.0 went further and defined a way to specify delimited identifiers:

2.13 Naming Database Objects

...

To specify delimited identifiers, you must use one of the following approaches:

• You can specify that all database identifiers used for the persistence unit are treated as <delimited-identifiers/> specifying the <delimited-identifiers/> element inside the persistence-unit-defaults element of the object-relational xml file mapping file. If the <delimited-identifiers/> element is specified, it cannot be overridden.
• You can specify for each name that the name for the database object should be interpreted as a separation identifier as follows:
  • Using annotations, the name is specified as a delimited identifier by enclosing the name in double quotation marks, resulting in internal quotation marks escaping, for example, @Table(name="\"customer\"") .
  • When using XML, the name is specified as a separator identifier using double quotation marks, for example, <table name="&quot;customer&quot;"/>

If you are using JPA 2.0, I would recommend using a portable solution.

Answer 2

Yes, repetition is a reserved word in MySQL

http://dev.mysql.com/doc/refman/5.7/en/keywords.html