Filter is affecting my second level cache

tim.taylor · **Joined:** Tue Mar 24, 2009 1:09 pm **Posts:** 14

Hi,

I have an interesting issue which is starting to bug me a bit.

I added a filter to a collection, the target table has a status and deleted column which are common across the DB. I added a filter to remove deleted or invalid status records from the collection. Only problem is it screws my second level cache and I get no cache entries when the filter is used. When its not it caches correctly.

Does anyone know if there has been/ is an issue with the way filrers interact with the cache?

I think I could understand why things go wrong, since the association is being made more complicated rather than a straight join on a primary-key/foriegn-key relationship. If this is the case then is there a work around?

Regards

Tim

<set
name="content"
lazy="false"
order-by="ORDER_NO">

<cache usage="read-only"/>

<key column="PARENTOID"/>
<one-to-many
class="com.foo.content.impl.hibernate.TopicReference"/>

<filter name="statusFilter" condition="STATUS='1' AND DELETED='0'"/>
</set>

<filter-def name="statusFilter">
</filter-def>

littypreethkr · **Posted:** Wed Mar 25, 2009 7:52 am

Try using the "where" attribute of the set instead of filter. Like:

Code:

<set name="content" lazy="false" order-by="ORDER_NO" where="STATUS='1' AND DELETED='0'">

    <cache usage="read-only"/>

    <key column="PARENTOID"/>
    <one-to-many class="com.foo.content.impl.hibernate.TopicReference"/>
</set> 

Plz note that the "where" should be having the condition in native SQL and not HQL.

tim.taylor · **Joined:** Tue Mar 24, 2009 1:09 pm **Posts:** 14

It worked?

You smooth operator you :)

I'd really like to understand why the filter didnt work though. Im lucky in that I dont need to filter with params they can be hardcoded for now, thus Im ok to use the 'where' clause on the set.

I originally used a filter beacuse it was a many-to-many via a link table that has the deleted and status columns. The where clause wont work in that situation. I had to change things and explicitly map the link table because there were other values I need. (the database is not a good one and is in need of some normalization, it breaks loads of relational model rules).

Can anyone explain why the filter affected the cache and the where clasue doesnt?

Regards and thanks for the help

Tim

littypreethkr · **Posted:** Thu Mar 26, 2009 2:18 am

Which is the version of hibernate that you are using? Coz there is a reported issue that the query cache is not used when any filters are enabled. See this ticket:
http://opensource.atlassian.com/project ... se/HHH-864

But this issue got fixed in 3.1beta2