Hi all,
fairly new to hibernate so please be kind!

In short, have query caching switched on, and am creating and running named queries, expecting a second invocation of the same named query with the same parameter to hit the cache. However, the cache is 'missed' (looks like cache is keyed on SQL) ?

Am using ehcache 1.7.0 Core and hibernate core 3.3.1 GA

It appears as if the SQL generated for the second invocation has different column alias names:

debug from org.hibernate.hql.ast.QueryTranslatorImpl:

e.g. first invocation of HQL query:
HQL: FROM net.x.cdm.C c where c.cName = :name
SQL: select c0_.c_id as c1_3_, c0_.cName as cName3_ from schema.c c0_ where c0_.cName=?

Latter invocation:
HQL: FROM net.x.cdm.C c where c.cName = :name
SQL: select c0_.c_id as c1_8_, c0_.cName as cName3_ from schema.c c0_ where c0_.cName=?

Note that each invocation creates the query afresh (as have different EntityManagers, different threads - no racing though)

final Query qu = em.createNamedQuery("getCByName");

and am defining getCByName as an annotation on the entity net.x.cdm.C
(And also tried in orm.xml).

Is there any way to either 'influence' the HQL->SQL translation to be deterministic in column naming, or to have the standard query cache to key on HQL rather than the generated SQL??!?

Thanks for any help in advance,

Michael Beale

Hi,
having scoured documentation and forums, I still haven't found anything on this. Would really appreciate any help!

Regards,
Michael Beale
Software Developer
Thrupoint/Ubiquity Software

Hey Michael,

have you turned on the cache for you particular HQL query?

query.setCacheable(true)

Hi there,
thanks for the response. Yes, I have caching enabled. Am using JPA and have:

qu.setHint("org.hibernate.cacheRegion", "query.n.x.cache.cdm0");
qu.setHint("org.hibernate.cacheable", true);

Between the 2 queries, I dump the cache and see that the query results are definitely cached.

If I run the query one straight after another, then I can see from ehcache/hibernate debug that the query cache is being hit.

If the second query is invoked in a different classloader/app, then debug suggests that they cache is interrogated but the key isn't found (for the reason explained previously, seemingly that the SQL generated in this case is different in having different column aliases and the SQL seems to be used as they key).

Michael

After a bit of digging in hibernate translation code, I noticed that the SessionFactory is used in methods such as hql/ast/SqlGenerator.out
:

protected void out(AST n) {
050: if (n instanceof Node) {
051: out(((Node) n).getRenderText(sessionFactory));
052: } else {
053: super .out(n);
054: }
055: }

(Not finished digging yet...)

Taking a stab in the dark, is the session factory used in some manner to generate SQL? My two apps each have their own EntityManagerFactory (as they can point at different dbs), although they are used within the same JVM.

I know that documentation suggests that an EMF is coupled to its own 2nd level cache but I am using a singleton cache manager and this appears to be the only thing which isn't working as expected e.g. the entity cache is correctly being shared between the two applications; the different generated SQL appears to be the only thing stopping the shared query cache from working also...

Anyone know if and how the sessionfactory is used in the generation of SQL from HQL??

Michael Beale