annotation to filter results of a @OneToMany association

Question

I have parent/child relationship between two tables, and the corresponding mapping in my Java classes. The tables roughly look like that:

A (ref number, stuff varchar2(4000))
B (a_ref number, other number, foo varchar2(200))

and the Java code:

@Entity
class A {
    @Id
    @Column(name = "REF")
    private int ref;

    @OneToMany
    @JoinColumn(name = "A_REF", referencedName = "REF")
    private Set<B> bs;
}

@Entity
class B {
    @Id
    @Column(name = "A_REF")
    private int aRef;

    @Id
    @Column(name = "OTHER")
    private int other;
}

This works fine, but I'd like to add a filter on the rows that I retrieve from the child table. The query that is generated looks like:

select a_ref, other, foo from B where a_ref = ?

And I'd like it to be:

select a_ref, other, foo from B where a_ref = ? and other = 123

The additional filter would be only a column name and a hard-coded value. Is there a way to do that using hibernate annotations?

I've looked at @JoinFormula, but with that I always have to reference a column name from the parent table (in the name attribute of the JoinFormula).

Thanks in advance for any advice.

Curious, why not just pass it in as a parameter? `where a_ref = ? and other = ?`. The value passed in can be hardcoded if that is what you need to do (I would suggest using a configurable properties system though). — ADTC, Jul 18 '13 at 10:45

score 33 · Accepted Answer · answered Aug 03 '11 at 01:56

It is not supported by JPA but if you are using hibernate as JPA provider then you can use annotation @FilterDef and @Filter.

Hibernate Core Reference Documentation

Hibernate3 has the ability to pre-define filter criteria and attach those filters at both a class level and a collection level. A filter criteria allows you to define a restriction clause similar to the existing "where" attribute available on the class and various collection elements. These filter conditions, however, can be parameterized. The application can then decide at runtime whether certain filters should be enabled and what their parameter values should be. Filters can be used like database views, but they are parameterized inside the application.

Exemple

@Entity
public class A implements Serializable{
    @Id
    @Column(name = "REF")
    private int ref;

    @OneToMany
    @JoinColumn(name = "A_REF", referencedColumnName = "REF")   
    @Filter(name="test")
    private Set<B> bs;
}

@Entity
@FilterDef(name="test", defaultCondition="other = 123")
public class B implements Serializable{
    @Id
    @Column(name = "A_REF")
    private int aRef;

    @Id
    @Column(name = "OTHER")
    private int other;
}

Session session = entityManager.unwrap(Session.class);
session.enableFilter("test");
A a = entityManager.find(A.class, new Integer(0))
a.getb().size() //Only contains b that are equals to 123

Thanks, it works well. It's a bit annoying though that you have to enable it dynamically (I will always enable it). I'll live with it! — Xavier, Aug 03 '11 at 17:20
Sorry, it's only available since JPA 2.0. http://docs.oracle.com/javaee/6/api/javax/persistence/EntityManager.html#unwrap(java.lang.Class) — Joel Hudon, Apr 08 '13 at 00:33
maybe this is not correct, but from what I have read it seems to me that this is application filter, not addition to where condition, is it so? — Jan Hruby, Sep 27 '16 at 11:27

score 4 · Answer 2 · answered Jan 22 '20 at 14:21

Another solution is to use Hibernate's @Where: https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#pc-where

    @OneToMany
    @JoinColumn(name = "A_REF", referencedName = "REF")
    @Where(clause = "other = 123")
    private Set<B> bs;

score 2 · Answer 3 · answered Feb 26 '15 at 11:25

2

with JPA 1 you can use the provided solution but change unwrap to getDelegate to be like that

Session session = (Session)entityManager.getDelegate();

and it's going to work.

answered Feb 26 '15 at 11:25

MSaudi

73
7

Hans Wouters · Answer 4 · 2018-01-24T14:00:10.200

If I am understanding the question right, there is a way to do this with javax.persistence annotations (I used this on a ManyToOne myself, and tailored the answer from here):

@JoinColumns({
    @JoinColumn(name = "A_REF", referencedColumnName = "REF"),
    @JoinColumn(name = "B_TABLE_NAME.OTHER", referencedColumnName = "'123'")})
@OneToMany
private Set<B> bs;

Note the single quotes in the referencedColumnName, this is the value you are looking for.

More information here.

annotation to filter results of a @OneToMany association

4 Answers4

Linked