How do I "filter" queries (where clause) on an entity by default?

Question

I have a hibernate entity in my play! project that is backed by a mysql database and queries using JPA.

What I now want to do is to add an "archive" facility to my app which deletes this entity as far as the user is concerned.

However there are still other entities which will reference it so I cannot do a delete on the entity , plus I want the ability to restore it from the archive.

However I do not want it to appear in lists or searches by default but of course still need the ability to access the data there on some occasions such as when the user is examining an item that references it.

There are a number of queries that reference the entity and I could add a WHERE clause to each of these but I am also using some of the built in query methods such as findAll().

I think it would be a lot cleaner if I could somehow add the where clause by default.

Is there any way to do this?

Check out this question, it may help: http://stackoverflow.com/questions/7700071/how-to-use-where-in-jpa-hibernate — Deco, Feb 09 '12 at 08:37
This might help as well: http://stackoverflow.com/questions/133143/llblgen-how-can-i-softdelete-a-entry#133609 — Pulkit Goyal, Feb 09 '12 at 08:43

score 6 · Answer 1 · edited Jan 06 '22 at 17:03

You have two solutions here:

If you are using Hibernate, You can use @Where(clause = "column to filer") on the entity bean.

@Where(clause = "isActive='false'")
public class Product {
    //...
    @Column
    private Boolean isActive;
}

Or If using EclipseLink JPA implementation then Use @AdditionalCriteria("column to filer") to define parameterized views on data.
```
@AdditionalCriteria("isActive='false'")
public class Product {
    //...
    @Column
    private Boolean isActive;
}
```

For more info:

score 0 · Answer 2 · answered Feb 10 '12 at 14:03

You can use the Hibernate @FilterDef and @Filter Annotations on your Entity. See the documentation. http://docs.jboss.org/hibernate/orm/4.0/manual/en-US/html/filters.html

You probably also want to create a interceptor/hook for the JPA Plugin that automatically enables your filter when a hibernate session is started... but for the beginning, a @Before Filter in your controller should do the trick.

How do I "filter" queries (where clause) on an entity by default?

2 Answers2

Linked