1

Good day,

I have a schema as follows

A -> one to many -> B

B-> one to many -> C

what I am trying to do is get all the grandchildren ( entity C ) from the parent ( entity A ). I have searched for a while and got to this point on class A

@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinTable(
   name="B",
   joinColumns= @JoinColumn(name="a_id", referencedColumnName="id"),
   inverseJoinColumns=@JoinColumn(name="b_id", referencedColumnName="id")
)
@JsonManagedReference
public List<C> getC() {
   return C;
}

however when the entity A is called I always get duplicates with the same number of entries I have inside the database so if I have 1 C item and I add a new C item and try to get the grandchildren from A I will be getting the same C item twice

I am not sure what I am doing wrong here or if there is any other possibility of getting C from A in another way?

EDIT

based on @SternK comment I updated my code to be as follows which works however I feel it can be improved a little, any advise?

in class C repo i added a function which accepts an ID from class A and with 2 fetch joins as follows

@Query("Select p FROM C c join fetch c.b cb join fetch cb.a cba where cba.a_id = :aId")
List<C> getC(@Param("aId") UUID aId);

and switched the property to become transient and in the service I loop over the list of A Entities and use this function to populate the property however I feel this can be improved ? or is this the best I can get out of it?

note that with this approach I think am getting hitting the database too much based on the amount of queries I see in the debugger.

Essameldeen Youssef
  • 373
  • 2
  • 10

1 Answers1

1

You can not use the @JoinTable annotation for your case. Because this is assume that B should be a join table that contains foreign keys to A and C tables. But this is not possible in your case because the relationship between B and C is one-to-many, so, C contains foreign key to B, not vice versa.

So, the only way that I see here is explicitly map the table B to the separate entity and then fetch this relationship via two joining fetch.

SternK
  • 11,649
  • 22
  • 32
  • 46
  • thank you for your comment, it helped me improve it a little. if its not too much trouble could you please review the update in the question? – Essameldeen Youssef Nov 09 '20 at 01:38
  • Please read carefully [this](https://stackoverflow.com/a/30093606/6277104) Vlad Mihalcea's answer to the mentioned in my answer post. Usually you should *fetch at most one collection using JOIN FETCH* So, you should actually write two jpql query. The first one will fetch the collection of `B` by the list of `A` ids and the second one will fetch all needed `C` entities by the list of `B`. – SternK Nov 09 '20 at 09:04
  • and when all needed entities will be loaded to the [persistence context](https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#pc) you will be able to loop over your associations collections without fear to get [N+1 query issues](https://vladmihalcea.com/how-to-detect-the-n-plus-one-query-problem-during-testing/) – SternK Nov 09 '20 at 09:21