CODEWITHSUNDEEP
javamysqlhibernatepostgresqljpa
Em Ae
Em Ae

Reputation: 8704

How to fetch many-to-many relationship entity

I have following table structure

TABLE1
  T1_ID
  T1_Col1

TABLE2
  T1_ID
  T3_ID

TABLE3
  T3_ID
  T3_COL1

Table1 and Table3 are joined by a middle table which is Table2. Now I have only T1_ID and I want to fetch all the rows from Table3 which are associated with T1_ID. A simple SQL query would be

select T1.*, T3.* 
from TABLE1 T1, TABLE T2, TABLE3 T3
where T1.T1_ID = T2.T1_ID
and T2.T3_ID = T3.T3_ID

So how can i do this in hibernate/jpa ... I have yet to write my entity classes for Table1, Table2, Table3. I want to execute this query as part of Table1, so that i can write a method say entity.fetchAssociatedTable3(). The easiest approach i can think of is in fetchAssociatedTable3 i can put custom queries like the one i mentioned above. But since i am using hibernate/jpa I want to know if there is a better way to do this.

UPDATE Apparently, my question isn't clear enough. I want to do something as user Dragan Bozanovic mentioned. However, What i want to know that

  1. How would i write Table1 entity ? I mean what annotations i would put on the columns etc which will make hibernate/jpa understand that this column is related to Table3 column via Table2

  2. I guess if question 1 is answered, then it would be difficult to write getEntity3s() method. But if (for a noob) there is something that I need to know, I would appreciate.

Upvotes: 2

Views: 149

Answers (2)

Shailendra
Shailendra

Reputation: 9102

First thing you would need do is to stop thinking in terms of tables when using ORM tool (Hibernate/JPA). You model your classes and their relations and let the ORM tool help with the mappings declaratively. Your join table just is serving here for creating relation many to many relation between two entities. So in terms of classes you would have only Entity1 and Entity3. You would not be creating a class representing join table (unless of course you want it to have other attributes other than foreign keys in which case it would qualify to be a Entity class in it's own right). And then you can use either method suggested by @Dragan i.e., loading by primary key or using explicit HQL/JPQL.

Upvotes: 0

Dragan Bozanovic
Dragan Bozanovic

Reputation: 23552

Assuming that you will have a many-to-many association between Entity1 (mapped to TABLE1) and Entity3 (mapped to TABLE3), you can either:

1) read the Entity1 by id and get all of the Entity3s from it:

Entity1 entity1 = entityManager.find(Entity1.class, entity1Id);
Collection<Entity3> entity3s = entity1.getEntity3s();

2) or, execute the JPQL query to get Entity3s without loading the Entity1 instance:

select distinct e3 from Entity3 e3 join Entity1 e1 where e1.id = :entity1Id

Upvotes: 1

Related Questions