Howto create a DB field with a reserved name using Hibernate and JPA

I stumbled upon this problem when I switched from a SQLServer 2008 database to a MySQL database 5.5 using Hibernate in Version 3.6.6 with JPA implementation 2.0.

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`")

and when using JPA 2.0 the syntax was standardized you have to escape the column name like this

@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="&quot;customer&quot;"/>"

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.

Iterating over result list from a JPA query

Sometimes when using EJB 3.x, it is necessary to use JPA QL or SQL native queries to fetch objects from the database. It might be used to achieve better performance or to just fetch a particular set of attributes of the object but not the complete object with all its dependencies.

There's one thing you have to keep in mind when doing this: The result list containing the object will be an array of objects!

"The SELECT clause queries more than one column or entity, the results are aggregated in an object array (Object[]) in the java.util.List returned by getResultList( )"

Working example:

 Query query = manager.createQuery("SELECT v1.bitbit, v1.numnum, v1.someTime, t1.username, t1.anotherNum FROM MasatosanTest t1 JOIN MasatoView v1 ON v1.username = t1.username;");  
   
   List results = query.getResultList( ); // Fetches list containing arrays of object  
   
   Iterator it = results.iterator( );  
   
   while (it.hasNext( )) {  
   
     Object[] result = (Object[])it.next(); // Iterating through array object   
   
     Boolean first = (Boolean) result[0]; // Fetching the field from array  
   
     /* Likewise for all the fields, casting accordingly to the sequence in SELECT query*/  
   
   }  
   
   

There's is even the possibility to avoid casting completely by using a constructor expression with the appropriate arguments in the SELECT section:

 SELECT new org.somepackage.XEntity(x.a, x.b) FROM XEntity x  

Remember to declare the appropriate constructor.

The code fragments and the solution in this blog post was taken from this question on stackoverflow.

Spring, JPA and using managed datasources with Tomcat

I was struggling for a few weeks now to get managed datasources in Tomcat6 to work with Spring and JPA. Well, finally it seems I have found a solution (I cannot prove it, because I did not implement it yet, but it looks good to me so far).
I have found two blogs covering this issue you might want to check out:

1. http://turgayzengin.blogspot.com/2007/09/multiple-persistence-units-with-spring.html
2. http://asrijaffar.blogspot.com/2007/02/spring-jpa-tomcat.html