hibernate data fetching from multiple tables

Question

I have 3 tables. Master,child1,child2.

Master has one to many relationship with child1.

Master has one to many relationship with child2.

There is no relation between child1 and child2.

I want to retrieve data from all 3 tables in single query using hibernate.

Table - Master
   id    
   11
   12

Table - child1
   id    deposited_date      Master_child1_reltion_key
   2     2012-10-10                   11
   3     2012-10-10                   11
   4     2012-10-10                   11
   5     2011-01-01                   12
   6     2005-02-20                   13


Table - child2   
   id    phoneNo      phone_type        master_child2_relation_key
   15    1111111      personal              11
   16    2222222      office                11 
   17    3333333      home                  11 
   18    1010100      personal              12

In table child2 there can be none,1,2, or 3 records against Master table.

I wanted to retrieve values from all 3 tables. like

  id     deposited_date    phoneno1     phoneno2     phoneno3
  11       2012-10-10      1111111      2222222       3333333
  12       2011-01-01      1010100
  13       2005-02-20

Is it possible to retrieve data using single query in hibernate or do I need to use multiple queries ?

if you can write a sql for this, then you can write a hql as well — Swapnil
– Swapnil, Commented Aug 18, 2012 at 6:18

RAS · Accepted Answer · 2012-08-18 06:22:27Z

1

You can use Criteria to fetch data from all the 3 tables as following:

Criteria crit = session.createCriteria(Master.class);
crit.setFetchMode("child1", FetchMode.JOIN);
crit.setFetchMode("child2", FetchMode.JOIN);
crit.setResultTransformer(criteria.DISTINCT_ROOT_ENTITY);
List<Master> masters = crit.list();

Here masters will be a list of Master with associated child1 & child2 entities.

answered Aug 18, 2012 at 6:22

RAS

8,15017 gold badges68 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1607139 Over a year ago

Thanks RAS. with this code I am getting all results .But How I can get distinct result from table chid1 only (in my case child1 contains 3 entries for Master_child1_reltion_key 11 with same deposited_date .It should be 1 entry since dates are same). In child2 there can be duplicates.

RAS Over a year ago

You need to take care while inserting the data. If child1 with id 3 & 4 are not useful to you, then why do you insert them in the table?

Bharat Sinha · Accepted Answer · 2012-08-18 06:56:58Z

1

Select master from master master left join fetch master.child1 left join fetch master.child2

This will bring master as a root with two populated list of childs?

As concern to id=13 i didn't understand how do you have a FK to not existing master.

edited Aug 18, 2012 at 6:56

Bharat Sinha

14.4k7 gold badges45 silver badges64 bronze badges

answered Aug 18, 2012 at 6:20

Avihai Marchiano

3,9574 gold badges41 silver badges57 bronze badges

Collectives™ on Stack Overflow

hibernate data fetching from multiple tables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related