Correct me if I am wrong.

I gather @Where can be used at class level for a permanent where for all fetches. I mean something along the lines of
@Entity
@Where(condition = "not expiryDate is null"
public class Item
{
@Id
@Column
Integer id;

@Column(name = "EXPIRY_DATE")
Date expiryDate
}

@Filter can be used at field or method level to filter a Collection with @FilterDef for same defined at class level. This filter can enabled and disable and also the parameter values be changes during runtime.

@FilterDef(name="expiryDateFilter", @ParamDef(name="now", type="java.util.Date"))
public class Inventory
{
@Id
@Column
Integer id;

@Filter(name="expiryDateFilter", condition = "expiryDate > :now")
Collection<Item> items;
}

enable/disable the filter using hibernate session. This is my understanding on these features.

1. But I am unable to get @Where working. Could someone please provide a working sample ?
2. Is the filter defined by @Filter used during the fetch from database or later when the collection is accessed using getItems() in this example ?

Both @Where and @Filter can be used on classes and collections.



The example you have seems to use HQL in the condition. You must use SQL both in @Where and @Filter.



It is used in the database query that fetches the items, but if you collection is lazy this usually doesn't happen until you access the collection. Eg. getItems().iterator(), getItems().add(...), etc.

Though I used HQL in this example, my intent was SQL. Thanks for pointing it out.

Could I still get a working sample of @Where ?

@Where(clause = "not upper(USERNAME) like 'SYS%'")

seems to work for me.

Thanks nordborg.