The goal
Generating a MySQL database schema or updating a database schema automatically using Hibernate's hbm2ddl.auto feature.The problem
Using SQLServer for a long time everything worked fine. When switching to MySQL the schema generation failed. In particular, one single table was not created and I couldn't find a hint in Hibernate's log statements.The solution
My persistence.xml file uses the auto update of a database schema using this property<property name="hibernate.hbm2ddl.auto" value="update" />
So far so good. Nothing special here.
I changed the Hibernate dialect to use the MySQL dialect (org.hibernate.dialect.MySQLDialect) and when deploying the application it generates the database schema and it should populate the database with default values. When populating starts it immediately fails with the following Hibernate error message:
"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 'interval) values (0, '10', 9, 4, 7, 1)' at line 1"
After a few minutes it came to my mind that the column name "interval" might be a keyword in MySQL. So googling around I found out that I was right. "interval" is a keyword in MySQL (but not in SQLServer).
Ok, reason found. But what is the best way to fix this issue?
Stackoverflow is your friend :-)
Just read this answer from Stackoverflow and you are done.
To summarize shortly:
With Hibernate as JPA 1.0 provider you have to annotate the entity field with enclosing backticks like this:
@Column(name="`interval`") @Column(name="\"interval\"")This is mentioned in chapter 2.13 (Naming of database objects) of the JPA 2.0 specification:
"It is possible to specify on a per-name basis that a name for a database object is to be inter-
preted 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=""customer""/>"
Using the first (but deprecated) solution 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.
The second solution is the modern and standardized way of forcing your JPA 2.0 and above provider to quote the identifier.
