Hibernate NamedQuery with a condition on inner List

Question

    class A{
// one to many mapping
    List<B> listOfB;
    //getter and setter;
    class B {
    String s;
    //getter and setter
    }

Now, when I get class A , it will return all the associated class Bs in listOfB. But I need certain condition on which of B should be in response. Like Get A such that listOfB contains all such B for which s = 'something'.

EDIT : currently this is not working:

select a from A a where a.listOfB IN (select b from a.listOfB b where b.s='something');

score 0 · Accepted Answer · answered Sep 14 '12 at 12:02

1) I assume that you mapped a one-to-many relation from A to B like A.listOfB = B.b

I think your query can be simplified as:

select a 
from A a, B b
where a.listOfB = b.b
and where b.s='something'

The equivalent Hibernate Criteria query would be:

List results =  sess.createCriteria(A.class)  
    .createCriteria("listOfB")
    .add(Restrictions.eq("s", "something"))
    .list();

2) If there is no relation mapped between these entities you can use an SQLRestriction over the field a.listOfB

List results = sess.createCriteria(A.class)
.add( Restrictions.sqlRestriction("{alias}.listOfB in (selec b from B where b.s = ?)), "something", Hibernate.STRING) )
   .list();

More examples here.

Your HQL query is invalid: b.b doesn't exist, and a list can't be compared with an entity. Moreover, this would not sole the OP's problem: he would still get the complete list of Bs in each A, and not just the Bs satisfying the criteria. — JB Nizet, Sep 14 '12 at 12:05

score 0 · Answer 2 · answered Sep 14 '12 at 12:02

You can do that with Hibernate, although the JPA spec says it should be forbidden. Indeed, when you get an A, its list of Bs should always be the complete list a Bs. Not a list that has been filtered by some query. I would avoid doing that because its dangerous. Rather, I would use a query like the following:

select a, b from A a inner join a.listOfB b where b.s = 'something'

Now you have all the As you're interested into, and all the Bs you're interested into:

A1, B1
A1, B2
A2, B7
A2, B8

If you really want to get As with partial list of Bs, use the following query. But you've been warned: it's not portable, and it's dangerous:

select a from A a inner join fetch a.listOfB b where b.s = 'something'

Hibernate NamedQuery with a condition on inner List

2 Answers2