Hibernate 3.x
OSCache 2.1.3

Requirement:
I am using Query Cache with Second level Cache and it has made things difficult.
I am fetching values from a relatively static table. But when the new rows
get added to this table, I need to fetch these new rows[inserted manually using sqlplus].
May be after the refresh period.

Problem:
Second Level Cache gets expired after configured 'Refresh Period". But the Query Level
Cache is still active [do not know if it could expire automatically like 2nd level Cache],
and retains the query and the primaryIds of the fetched rows. A query retrieves rows from
database but onlt for these cached primaryIds . The newly created Ids are not fetched?

What are the possible workarounds for this problem?

Thanks.

rajiv65 ,

In general, understand that what you are doing defeats the purpose of a cache.

If possible, insert those rows using Hibernate, instead of some external method.
Alternatively, don't cache the queries but retrieve only IDs from your HQL/Criteria, and load the rest of the bean from the (cached) session, using load().

Thanks Gonzalo!
What you are saying makes sense.
Say, I drop the Query Cache and use just the Second Level Cache. Can I load the complete table during startup and then mark it for refresh, say after every 24 hours? If yes, could you tell me how exactly to achieve this in Hibernate? If I remove the query Cache and a mark the query for CacheMode.NORMAL, I can see "puts" happening in Second Level Cache but I did not see any "hits". Everytime, the query will fetch the data from database and while trying to add it to second level cache[during two phase load], it would say entity already exists in cache. So , why it is not reading the second level Cache?

If I do not see this happening through hibernate, I was thinkking may be I will bypass hibernate and directly use OScache for this purpose. Any suggestions?

Rajiv,

What you saw makes sense. The query caché just stores a query, its parameters, and the corresponding list of return values.
In order to obtain "hits", you have to use session.get() or session.load()

Generally speaking, caching the queries is not very useful, and even less in a case like yours, where you do SQL changes outside of Hibernate's scope.

Configuration alone, by way of expiration times, won't help you here, if you don't also apply any of the strategies I mentioned before.
As a general rule, caching is only useful when your queries return only keysets (ids) and you get the whole beans from the cache through load() or get().



This "load" method, which should be called after every "keyset", query will inteligently get things from either the caché or the databae itself, and load it in the caché for further use, you don't have to do anything.

I would complement this strategy by adding some external URL that invokes a "cache clearing" method, to be called manually after every time you do those command-line insertions.

Internally, the only thing that cache-clearing request would do, is to call "evict" on the type of beans you are caching.



That takes care of both the session and the underlying cached values.

And no, don't try to use the a caching library outside Hibernate. What for, if these techniques make Hibermate do all the work for you?

Thanks for your patience and help!



This does not get me anything as I am still hitting the database every time defying the purpose of Cache.
While I am making a trip to DB to get Ids, I might as well get associated 2 columns. [Do you think I am saving anything?].[table with 2000 rows and 3 columns including Id column]

And, I cannot control the sql statments doing updates Not from application. Database is managed by DB admin and they add new rows using their own scripts.

I guess 2nd Level Cache is just not suitable in this scenario. Do you think otherwise?

If there is no much difference between getting a just keyset and retrieving the whole bean, then the using the cached objects retrievable by load() and get() might not make much sense.

I believe you can still benefit from the cached results returned by the queries, provided that:

- your parameters don't change very often
- you clear the cache (via evict) right after the time you suppose those external SQL inserts happen.

But don't count on the statistics reflecting anything of this, I am not sure they are useful for non get()/load() operations.
Instead, check if an SQL is generated at all between 2 identical query calls with the same parameters. That would be your proof that query caching is working at all.