CODEWITHSUNDEEP
hibernatejoinhqljpql
Adrian T
Adrian T

Reputation: 93

Hibernate - join three times same table

I have this problem:

two tables: 

tbl_suppliers
   id* (int), 
   name (string), 
   activity1 (int), 
   activity2 (int), 
   activity3 (int)

tbl_activity 
   id* (int), 
   description (string)

There is a kind of "one-to-many" relationship between the two tables: tbl_activity (one) --> tbl_suppliers (many) ; this means that every supplier can have all three ids filled for every activity or even no activity (no id).

I need to display the record of tbl_suppliers with columns name, activity1, activity2, activity3.

In SQL it's ok with this query:

SELECT s.id, s.name, a1.description activitystring1, 
    a2.description activitystring2, a3.description activitystring3 
FROM tbl_suppliers s 
LEFT JOIN tbl_activity a1 ON (s.activity1=a1.id) 
LEFT JOIN tbl_activity a2 ON (s.activity2=a2.id) 
LEFT JOIN tbl_activity a3 ON (s.activity3=a3.id)

I can't do the same thing in HQL. Should I map three times "one-to-many" the activity field?

Upvotes: 2

Views: 3173

Answers (1)

Mikko Maunu
Mikko Maunu

Reputation: 42114

Tables can be mapped as follows:

@Entity
@Table(name="tbl_activity")
public class Activity {
    @Id private Integer id;

    private String description;
    //getters, setters, etc.
}

@Entity
@Table(name="tbl_suppliers")
public class Supplier {
    @Id private Integer id;

    private String name;

    @JoinColumn(name = "activity1")
    @ManyToOne private Activity activity1;

    @JoinColumn(name = "activity2")
    @ManyToOne private Activity activity2;

    @JoinColumn(name = "activity3")
    @ManyToOne private Activity activity3;
    //getters, setters, etc.
}

Original SQL query can be then expressed with following JPQL:

SELECT s.id, 
       s.name, 
       activity1.description, 
       activity2.description, 
       activity3.description
FROM Supplier s
  LEFT OUTER JOIN s.activity1 activity1
  LEFT OUTER JOIN s.activity2 activity2
  LEFT OUTER JOIN s.activity3 activity3

If you prefer more object oriented approach, you can simply query for Supplier and access descriptions via getActivity1.getDescription().

Upvotes: 3

Related Questions