CODEWITHSUNDEEP
hibernatehql
xrcwrn
xrcwrn

Reputation: 5327

HQL Query using join to fetch data from tables

I need help regarding to write HQL query to fetch data from three table using join.

public class PostMessages  implements java.io.Serializable {
     private Long messageid;
     private String message;
      private Set videosDescriptions = new HashSet(0);
     private Set postImageses = new HashSet(0);
}

public class PostImages  implements java.io.Serializable {
     private Long imageId;
     private String smallPicPath;
     private String largePicPath;
     private PostMessages postMessages;
 }

public class VideosDescription  implements java.io.Serializable {
     private Long videoId;
     private String videoPath;
     private String videoTitle;
     private PostMessages postMessages;
    }

In PostMessages.hbm.xml I mapped both classes as follow

<set name="postImageses" table="post_images" inverse="true" lazy="true" fetch="select">
     <key>
     <column name="messageid" />
     </key>
  <one-to-many class="hibernetMappings.PostImages" />
</set>
<set name="videosDescriptions" table="videos_description" inverse="true" lazy="true" fetch="select">
  <key>
   <column name="message_id" />
  </key>
  <one-to-many class="hibernetMappings.VideosDescription" />
 </set>

Above are my pojo classes I want to fetch all postMessages and postImages and videoDescription details for given message id how to fetch it.

Upvotes: 0

Views: 2792

Answers (2)

Adrian Shum
Adrian Shum

Reputation: 40036

The answer from OP may work, but blindly joining two "path" of ~ToMany relationship is going to make the resulting SQL an inefficient query which give cartesian product for the result.

To simplify the story, assume we have entities like this:

public class Message {
    @Id
    private Long id;

    @OneToMany
    private Set<Image> images;

    @OneToMany
    private Set<Description> descriptions;
}

if you are retrieving Message + its images + its descriptions by

from Message 
left join fetch images 
left join fetch descriptions 
where id = :id

if that message contains 100 images and 100 descriptions, the resulting SQL is going to give you 10,000 records.

A more proper way to fetch this is by issuing two queries.

from Message left join fetch images where id = :id

from Message left join fetch descriptions where id = :id

With first-level cache of Hibernate (or any JPA implementation), you can get the result of either one of the queries and you will have both images or descriptions fetched.

Just to avoid confusion, if you have structure like this:

[Foo]  1-->* [Bar]  1-->* [Qux]

It is fine to do 

from Foo foo join fetch foo.bars bar join fetch bar.quxs qux where foo.id = :id

Upvotes: 2

xrcwrn
xrcwrn

Reputation: 5327

I tried following query which is working properly

select distinct pm
     from PostMessages as pm
     left join fetch pm.postImageses as pi
     left join fetch pm.videosDescriptions as vd 
     where pm.messageid=67

If is there any other way for getting this query please provide it

Upvotes: 0

Related Questions