9

I've those 2 entities

Class A {
    @OneToMany(mappedBy="a")
    private List<B> bs;
}

Class B {

    @ManyToOne
    private A a;

    private String name;
}

1) I would like to construct a query that says get all A's that have at least one B with name ="mohamede1945"

2) I would like to construct a query that says get all A's that don't have any B with name = "mohamede1945"

Could anyone help me?

mohamede1945
  • 7,092
  • 6
  • 47
  • 61

2 Answers2

13

First of all, I think you can learn the answer by looking at this link and search for JOIN: http://download.oracle.com/docs/cd/E11035_01/kodo41/full/html/ejb3_langref.html

Second of all, here is my approach:

@Entity
@NamedQueries({
@NamedQuery(name="A.hasBName",query="SELECT a FROM A a JOIN a.b b WHERE b.name = :name"),
@NamedQuery(name="A.dontHasBName",query="SELECT a FROM A a JOIN a.b b WHERE b.name <> :name")
})
Class A { /* as you defined */ }

In you DAO, you can make the namedquery like this:

public List<A> findByHasBName( String name ){
    Query q = em.createNamedQuery("A.hasBName")
            .setParameter("name", name);
    try{
        return ( (List<A>) q.getResultList());
    } catch ( IndexOutOfBoundsException e){
        return null;
    }
}
illEatYourPuppies
  • 611
  • 6
  • 16
12

You can use the ANY and ALL constructs to filter the subquery. So something like

1. FROM A aEntity WHERE 'mohamede1945' = ANY (SELECT bEntity.name FROM aEntity.bs bEntity)

2. FROM A aEntity WHERE 'mohamede1945' <> ALL (SELECT bEntity.name FROM aEntity.bs bEntity)
Basanth Roy
  • 6,272
  • 5
  • 25
  • 25