15

My application uses JPA (1.2), Spring (3.1.2), Spring Data (1.1.0) and Hibernate (4.1.7). DataBase : Oracle10g

We have enable second level caching. It's working fine with entity but it's creating issues on named query caching.

The issue is: If named query has same where clause but different select statement, then whatever the first query execute it's giving the same result for the second query also.

Like my first query(countRelease) is 

select count(r) from Release r where r.type in 
(select c.contentTypeId from ContentType c where c.parentContentTypeId is NULL)
order by r.validityStart

and second query(findRelease)is 

select r from Release r where r.type in 
(select c.contentTypeId from ContentType c where c.parentContentTypeId is NULL)   
order by r.validityStart

If first query is run first then count will come and after that if I run second query then also count will come it should give me the list of release entity.

If I remove query cache it's working fine and if I make some changes in second query where clause then also it's working fine but I dont need to do that.

How we can solve this issue?

My Java code 

@Query(name="findRelease")
@QueryHints({@QueryHint(name = "org.hibernate.cacheRegion", value ="cvodrelease"),@QueryHint(name = "org.hibernate.cacheable", value ="true") })
public List<Release> findRelease();

@Query(name="countRelease")
@QueryHints({@QueryHint(name = "org.hibernate.cacheRegion", value ="cvodrelease"),@QueryHint(name = "org.hibernate.cacheable", value ="true") })
public Long  countOfRelease(Date today);

Cache Configuration 

<property name="hibernate.cache.region.factory_class" value="org.hibernate.cache.ehcache.EhCacheRegionFactory"/>
<property name="hibernate.cache.use_query_cache" value="true"/>
<property name="hibernate.cache.use_second_level_cache" value="true"/>
<property name="hibernate.cache.provider_class" value="net.sf.ehcache.hibernate.EhCacheProvider" /> 
<property name="hibernate.cache.provider_configuration_file_resource_path" value="ehcache.xml" />

<bean id="cacheManager" class="org.springframework.cache.ehcache.EhCacheCacheManager"  p:cacheManager-ref="ehcache"/>

<bean id="ehcache" class="org.springframework.cache.ehcache.EhCacheManagerFactoryBean" p:configLocation="ehcache.xml"  p:shared="true"/>
Ranu Jain
  • 577
  • 4
  • 11
  • Does any one have the solution for the above issue – Ranu Jain Feb 19 '13 at 06:24
  • Does any one one have the solution for the above issue... or its bug – Ranu Jain Feb 20 '13 at 04:19
  • The `QueryKey` class seems to store the query string, so this is indeed strange. I would put a breakpoint on `QueryKey.generateQueryKey()` and try to track down why its `queryString` argument is the same for the two different queries. – zagyi Mar 05 '13 at 11:58
  • @Ranu Jain It's better for you to post java code for reviewing. code tells it all. as i know. second-level cache won't affect query result. it's other problem. – Henry Leu Mar 05 '13 at 12:12
  • There is not java code as such it has only method name with @namedQuery because we are using JPA Repository – Ranu Jain Mar 05 '13 at 13:00
  • There is java code that uses the named queries... – Glen Best Mar 11 '13 at 06:53
  • Perhaps this is obvious, but are you sure your queries have unique names? Be aware that two queries with the same name, in different defined in different classes, will be treated as the same query. – Niel de Wet Mar 11 '13 at 07:28
  • Yes my query has differnet name. I am 100% sure – Ranu Jain Mar 11 '13 at 11:21

3 Answers3

3

JPA 1.0 standard had no caching included (and JPA 1.2 doesn't exist).

JPA 2.0 standard introduced caching - including the Shared Cache (a "first level cache" for each EntityManagerFactory instance) and the Application cache (second level cache for ALL EntityManagerFactor instances). Also each PersistenceContext for each EntityManager instance acts as its own lowest level cache - the "zero level cache".

What this means is that your behaviour is all specific to Hibernate 4.1.7 and has nothing to do with any standard or any other product.

Caching is not used when the the data cache does not have any cached data for an id in a query result.

That's a direct quote from Apache OpenJPA docs, not Hibernate or JPA spec. You can ignore, but seems it would be true for Hibernate.

Queries that result in projections of custom field types or BigDecimal or BigInteger fields are not cached.

That's a direct quote from Oracle Kodo JPA docs, not Hibernate or JPA spec. Might be wise to ignore this.

The query cache does not cache the state of the actual entities in the cache. It caches identifier values and results of value type. Therefore, always use the query cache in conjunction with the second-level cache for those entities which should be cached as part of a query result cache. .

That's a direct quote from Hibernate 4.1 docs. So you can follow this advice - as long as you take it in context: it's saying to include the second-level cache if you want to cache entities returned from queries. If you don't want to cache entire entity objects, but just want to cache the results of NamedQueries containing primitive data types (projections), then the first level cache is all you need.

My suggestion:

  1. I think the problem might be that COUNT(r) returns a BigInteger to java, which cannot be cast to Object for caching. You can call addScalar("count", Hibernate.LONG) on the query to tell hibernate to use a different type - LONG. See blog.pfa-labs.com/2009/12/caching-raw-sql-count-with-hibernate.html plus Is/Can Hibernate's Second-Level Cache be Used for COUNT() operations?

  2. The query cache should be able to handle this. The second level cache should only be needed for entity objects.

  3. Be very careful you understand the read/write behaviour of the objects you are trying to cache - and ensure the number of reads is much greater than the number of writes. Otherwise caching could give no benefit or even slow things down and cause data inconsistencies.

  4. Be aware that some JDBC drivers also cache data - if yours is it will affect JPA results and JPA won't even know about it.

From Mike Keith's "Pro JPA 2": Most [JDBC] drivers cache connections and statements. Some caches also keep track of table or column state that is essentially transparent to the JPA provider, but that nonetheless can offer some savings in terms of not having to go to the database to get data on every call. This is generally feasible in the driver only if it is known that either the data is read-only or the driver controls database access exclusively.

JDBC caching, if available, should be controllable via configuration settings specific to the driver.

EDIT:

In the first query, "order by r.validityStart" does nothing - you could remove it, and all will work.

Community
  • 1
  • 1
Glen Best
  • 22,769
  • 3
  • 58
  • 74
  • 1
    JPA 1.2 doesn't exist. JPA 1.0 does, JPA 2.0 does, JPA2.1 will – DataNucleus Mar 11 '13 at 08:18
  • Thanks. Corrected version JPA 1.2 -> 1.0. – Glen Best Mar 11 '13 at 08:31
  • Thanks for your answer but still it will not solved my issue. My count query give me the result in Long only but count query and object select query has same where condtion so its always give the result whosoever run first and its throw the exception for other query. – Ranu Jain Mar 11 '13 at 11:26
  • My pleasure, will see if I can do more. IF there's no bug in your code (can't confirm this, because you've only included JPQL strings & no java query invocation code), then there MUST be a bug in Hibernate/selected cache provider - no matter how you have configured your caches, they should never return an illegal result - would always be a bug. My suggestions: (1) include your full code - it can't hurt, it helps us reproduce your problem and find the bug (2) Let us know the DB are you using - MySQL? Oracle? (3) Let us know what cache provider are you using - hibernate's inbuilt cache? – Glen Best Mar 12 '13 at 03:35
  • (4) what happens when you query DB to see compiled/cached query plans? E.g. in Oracle SELECT * FROM V$SQL WHERE SQL_TEXT LIKE '%select c.contentTypeId from ContentType c where c.parentContentTypeId is NULL%'; (5) what happens when you change the order queries are executed? – Glen Best Mar 12 '13 at 03:40
  • Also check result cache settings. For Oracle: SELECT name, value, isdefault FROM v$parameter WHERE name LIKE 'result_cache%'; (6) Try with only first level cache, then try with only second level cache. Note first query should only ever be cached by first level cache (primitive data types in projection); second query should only ever be cached by second level cache (entity object returned) – Glen Best Mar 12 '13 at 03:50
  • I have updated my question with all details. In Oracle its giving correct result. And if reverse the order then whoso ever run first that result come for second query to... – Ranu Jain Mar 12 '13 at 06:02
1

The query cache maintains the results where query along with parameters combined constitutes the key and value as the identifier.

From documentation:

  • Caching is not used when the the data cache does not have any cached data for an id in a query result.

  • Queries that result in projections of custom field types or BigDecimal or BigInteger fields are not cached.

  • Note that the query cache does not cache the state of the actual entities in the result set; it caches only identifier values and results of value type. The query cache should always be used in conjunction with the second-level cache.

It's preferable to fetch whole object, rather than fields in the query.

Possibly it's neglecting the select part of the query & caching the result. The later part is same for both the queries, hence yielding the same result. You can try altering the query execution order & observe the result.

Community
  • 1
  • 1
Nayan Wadekar
  • 11,444
  • 4
  • 50
  • 73
  • I am not fetching the single single field. AS mentioed in Query first query is fetching count and second query fetching the entity object. Hense worth i cannot combine the queries....Please provide me the solution... – Ranu Jain Feb 21 '13 at 10:37
  • @RanuJain For individual field I meant not fetching whole object, here it's count. I don't understand why you need count query. You can execute 2nd query & can get list size as count from its result. – Nayan Wadekar Feb 22 '13 at 04:14
0

I believe your problem doesn't relate to second-level cache - it's something else. The cache itself can not change the expected result.

To be even surer, you may try the following code to clear the second level cache before start the second query:

session.setCacheMode(CacheMode.IGNORE); // session here is the SessionFactory

If the problem still persists, then it's clear that Second level cache is not the culprit.

Hoàng Long
  • 10,746
  • 20
  • 75
  • 124
  • Without second level cache everything works fine when we enable second level cacehe then only this issue occured. So hense worth somthing wrong in second level cache only – Ranu Jain Mar 12 '13 at 09:27
  • @RanuJain: maybe it, or maybe something that second level cache accidentally trigger. I mean that second level cache is not the direct culprit for your problem. – Hoàng Long Mar 12 '13 at 09:58