Dealing with JPA's lack of addScalar method

If you are looking for a way to cache your native queries using the latest and the greatest (Hibernate 3.6.x, JPA 2.0 & ehcache) you are in for a nice surprise.

There is no easy way to specify a query result type in JPA, or maybe there is and I couldn't find one :)

The problem is twofold, because if you cannot specify a return type, you cannot cache your native query with ehcache. Now, you could say that maybe this is a sign, and you shouldn't cache your queries anyway, or you could just sweat the small stuff, like I do all the time, and find a solution to this tiny issue.

So, if you've made it this far, you are pretty much stuck with writing some sucky code, to cast back to the hibernate session, and set the info.

And now for some (scala) code:

val sql = "select distinct story as story from ...";
val q: Query = getEntityManager().createNativeQuery(sql);
//hello nasty hack
q.asInstanceOf[HibernateQuery].getHibernateQuery().asInstanceOf[SQLQuery].addScalar("story", StandardBasicTypes.LONG);
//next, caching  
q.setHint("org.hibernate.cacheable", true);
q.setHint("org.hibernate.cacheRegion", "query.getTopLinks");

The tick is that you cannot cast directly to SQLQuery, you have to cast to HibernateQuery, then call getHibernateQuery, then cast the result to SQLQuery.

Ugly(ish) but usable, just how I like it.

Also, notice the use of StandardBasicTypes.LONG, replacing the old Hibernate.LONG which is now deprecated.

ps. I've decided to write this after I've found this question on SO.