Hibernate, MySQL and table named "Repeat" - strange behaviour.

Question

I am having a strange problem. But first the 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 use Hibernate / JPA. Everything runs fine with HSQL and Derby, so my BO, DAO and unit tests must be ok. When testing with MySQL, I get this error:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select repeat0_.id as id8_, repeat0_.id_repeatType as id2_8_, repeat0_.id_trainingSet as id3_8_ from repeat repeat0_];

However, changing

<table name="repeat"/>

to

<table name="repeatt"/>

solves the problem with MySQL.

What is wrong? Is "repeat" a reserved keyword or is this a bug in Hibernate's JPA implementation?

Thank & Cheers Er

Answer 1

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

JPA 1.0 doesn't define a standard way to handle that so you'll have to use Hibernate solution which relies on backticks. From the Hibernate Reference Guide:

5.4. SQL quoted identifiers

You can force Hibernate to quote an identifier in the generated SQL by enclosing the table or column name in backticks in the mapping document. Hibernate will use the correct quotation style for the SQL Dialect. This is usually double quotes, but the SQL Server uses brackets and MySQL uses backticks.

<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 would work in orm.xml too.

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

2.13 Naming of Database Objects

...

To specify delimited identifiers, one of the following approaches must be used:

• It is possible to specify that all database identifiers in use for a persistence unit be treated as delimited identifiers by specifying the <delimited-identifiers/> element within the persistence-unit-defaults element of the object/relational xml mapping file. If the <delimited-identifiers/> element is specified, it cannot be overridden.
• It is possible to specify on a per-name basis that a name for a database object is to be interpreted as a delimited identifier as follows:
  • Using annotations, a name is specified as a delimited identifier by enclosing the name within double quotes, whereby the inner quotes are escaped, e.g., @Table(name="\"customer\"").
  • When using XML, a name is specified as a delimited identifier by use of double quotes, e.g., <table name="&quot;customer&quot;"/>

If you are using JPA 2.0, I'd recommend to use the portable solution.

Answer 2

Yes, repeat is a reserved word in MySQL

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html