CODEWITHSUNDEEP
javahibernateorm
Khue Vu
Khue Vu

Reputation: 3152

JPA QL for selecting non-owning side of ManytoMany relationship?

I have a ManytoMany relationship between A and B, where A is the owning side. I define the ManyToMany in class A:

@ManyToMany(....)
private Set<B> bs

But I don't want to expose set in B, so no @ManyToMany attribute defined in B (e.g Set as). It stems a problem when I want to select all B entity which an A instance is mapped to using JPA QL. I can not do: 

"SELECT b FROM B b JOIN b.as A WHERE A.id = :id"

I could have set fetch = Fetch.EAGER in the @ManyToMany properties and use A.getBs() to get related B. But I prefer not to use Fetch.EAGER. Any suggestion? Thanks

Upvotes: 0

Views: 1981

Answers (1)

Koh&#225;nyi R&#243;bert
Koh&#225;nyi R&#243;bert

Reputation: 10131

no @ManyToMany attribute defined in B (e.g Set as)

(I had to correct myself, because it seems omitting Set<A> as from B altogether won't raise an exception.)

If you only want to hide the Set<A> as in B, then you can declare it as private and use a bi-directional mapping (using the mappedBy property on the non-owning side of the relation). In this case the following query runs successfully:

EntityManager em = ...
String sql = "SELECT b FROM B b JOIN b.as a WHERE a.id = :id";
TypedQuery<B> tq = em.createQuery(sql, B.class);
tq.setParameter("id", 100);
for (B b : tq.getResultList())
  System.out.println(b);

(The example snippets are all based on the tables, data and entities found in the lower sections of the answer.)

It prints:

B{id=333, data=b}
B{id=999, data=bbb}

This JPQL query is the mapping of the following native SQL query:

SELECT b.id, b.data 
FROM a, b, a_has_b 
WHERE a.id = a_has_b.a_id 
AND b.id = a_has_b.b_id 
AND a_id = 100;

You basically want a uni-directional relation (by omitting the mappedBy from B—down below—or droppping Set<A> as). This way, however, you won't be able to execute a query like you've described. Just no way it'll work.

The persistence provider will bark at you if there is no Set<A> as in B (property could not be resolved—in case of Hibernate). If you only omit the mappedBy from the non-owning side, then the persistence provider won't know where is the other side of that relation. Either you use the mappedBy or create an inverse @JoinTable annotation in B too (mappedBy is there so that you don't have to the latter).

If you only have a uni-directional mapping from A towards B you can only fetch an A entity by its id and find all B entities that are associated with it, like this (just what you've described):

TypedQuery<A> tq = em.createQuery("SELECT a FROM A a WHERE id = :id", A.class);
tq.setParameter("id", 100);
for (A a : tq.getResultList())
  for (B b : a.bs)
    System.out.println(b);

This works for me without specifying fetch = Fetch.EAGER and prints the same stuff as before.

Beware that if Fetch.LAZY is in effect you'll receive errors if you try accessing lazily loaded entities after closing an EntityManager or (Hibernate) Session. You can't do anything about this: this is the way it's supposed to work.

EntityManager em = ...
// fetch your B instances
List<B> bs = ...
em.close();
for (B b : bs)
  for (A a : b.as)
    // *BOOM*
    System.out.println(a);

You can do two things to prevent BOOM from happening.

  1. Close your EntityManager or Session after you're done with your A objects and don't use them anymore. If you call b.as before em gets closed Hibernate (or any other persistence provider) will load A objects lazily from the database.
  2. On your B entity's @ManyToMany annotation change fetch to FetchType.EAGER. This way, when you fetch B objects from the database their Set<A> as property will be loaded by Hiberate too (further control can be practiced with different CascadeType settings—I think).

I propose that you use a bi-directional mapping instead (don't omit mappedBy) or make B the owning side (but the former would be much useful).

Tables

test.a

+-------+-------------+------+-----+---------+
| Field | Type        | Null | Key | Default |
+-------+-------------+------+-----+---------+
| id    | int(11)     | NO   | PRI | 0       |
| data  | varchar(45) | YES  |     | NULL    |
+-------+-------------+------+-----+---------+

test.b

+-------+-------------+------+-----+---------+
| Field | Type        | Null | Key | Default |
+-------+-------------+------+-----+---------+
| id    | int(11)     | NO   | PRI | 0       |
| data  | varchar(45) | YES  |     | NULL    |
+-------+-------------+------+-----+---------+

test.a_has_b

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a_id  | int(11) | NO   | PRI | 0       |       |
| b_id  | int(11) | NO   | PRI | 0       |       |
+-------+---------+------+-----+---------+-------+

Data

test.a

+-----+------+
| id  | data |
+-----+------+
| 100 | a    |
| 200 | aa   |
| 300 | aaa  |
+-----+------+

test.b

+-----+------+
| id  | data |
+-----+------+
| 333 | b    |
| 666 | bb   |
| 999 | bbb  |
+-----+------+

test.a_has_b

+------+------+
| a_id | b_id |
+------+------+
|  100 |  333 |
|  300 |  333 |
|  100 |  999 |
+------+------+

Entities

A

@Entity
@Table(schema = "test", name = "a")
public final class A {

  @Id
  public int id;

  @Basic
  public String data;

  @ManyToMany(targetEntity = B.class,
              cascade = CascadeType.ALL,
              fetch = FetchType.LAZY)
  @JoinTable(schema = "test",
             name = "a_has_b",
             joinColumns = @JoinColumn(table = "a",
                                       name = "a_id",
                                       referencedColumnName = "id"),
             inverseJoinColumns = @JoinColumn(table = "b",
                                              name = "b_id",
                                              referencedColumnName = "id"))
  public Set<B> bs = Sets.newLinkedHashSet();

  @Override
  public String toString() {
    return "A{id=" + id + ", data=" + data + "}";
  }
}

B

@Entity
@Table(schema = "test", name = "b")
public final class B {

  @Id
  public int id;

  @Basic
  public String data;

  // omitting mappedBy results in a uni-directional relationship
  @ManyToMany(targetEntity = A.class,
              cascade = CascadeType.ALL,
              fetch = FetchType.LAZY,
              mappedBy = "bs")
  public Set<A> as = Sets.newLinkedHashSet();

  @Override
  public String toString() {
    return "B{id=" + id + ", data=" + data + "}";
  }
}

Upvotes: 1

Related Questions