• My Hacks
This series describes some problems encountered developing ThruZero and the hacks I used as workarounds. There are many more hacks than described here (including the ones I'm not aware are hacks).

JPA getSingleResult()

Friday, January 4, 2013
George Norman
Sometime last year, when I started using JPA with Hibernate (and MySQL), I received the following exception:
MySQLSyntaxErrorException: 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 '2 textentity0_.TEXT_ENVELOPE_ID as TEXT1_0_, textentity0_.PATH as PATH0_, tex' at line 1

The line that blew up is highlighted below:

  1. StrBuilder hql = new StrBuilder(100);  
  2. hql.append(" FROM TextEnvelope textEnvelope ");  
  3. hql.append("  WHERE textEnvelope.entityPath.containerPath.path = :path ");  
  4. hql.append("    AND textEnvelope.entityPath.entityName = :entityName ");  
  5.   
  6. Query hqlQuery = entityManager.createQuery(hql.toString());  
  7. hqlQuery.setParameter("path", entityPath.getContainerPath().getPath());  
  8. hqlQuery.setParameter("entityName", entityPath.getEntityName());  
  9.   
  10. TextEnvelope result = hqlQuery.getSingleResult();  

Odd that this worked for another query that didn't return a TextEnvelope. On further inspection, I noticed the working query's mapping file didn't use a Hibernate component, whereas the problem query did.

  1. <hibernate-mapping package="com.thruzero.domain.model" default-access="field">  
  2.   <class name="TextEnvelope" table="TEXT_ENVELOPE">  
  3.     <id name="id" column="TEXT_ENVELOPE_ID" type="long">  
  4.       <generator class="increment" />  
  5.     </id>  
  6.   
  7.     <component name="entityPath" class="com.thruzero.common.core.support.EntityPath">  
  8.       <component name="containerPath" class="com.thruzero.common.core.support.ContainerPath">  
  9.         <property name="path" column="PATH" />  
  10.       </component>  
  11.       <property name="entityName" column="NAME" />  
  12.     </component>  
  13.   
  14.     <property name="data" type="string" column="DATA" />  
  15.   </class>  
  16. </hibernate-mapping>  

I also noticed that the component didn't cause a problem when getResultList() was used. So here's the hack I ended up with:

  1. TextEnvelope result = JpaUtils.getSingleResultHack(hqlQuery);  

Where JpaUtils.getSingleResultHack is implemented as follows:

  1. public static  T getSingleResultHack(Query hqlQuery) {  
  2.   T result = null;  
  3.   List hack = hqlQuery.getResultList();  
  4.   
  5.   if (!hack.isEmpty()) {  
  6.     if (hack.size() > 1) {  
  7.       throw new DAOException("ERROR: getSingleResultHack found more than one result.");  
  8.     }  
  9.     result = hack.get(0);  
  10.   }  
  11.   
  12.   return result;  
  13. }  

I'm not sure if this is a bug or a feature, but until I find a better work-around...

-Geo.