-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: HQL query to SQL problem
PostPosted: Wed Jan 07, 2009 6:59 am 
Newbie

Joined: Wed Dec 17, 2008 6:38 am
Posts: 4
Hi,
I wish you all a Happy New Year!
I have a problem with a HQL query and I hope you can help me find what's wrong. I have an entity called Travel that is related to some Activity, Proposal or ProjectBaseline objects. An activity, proposal or projectbaseline can have multiple travel objects associated to them, so the relation from the travel object perspective is many-to-one(unidirecational, since the activity,proposal or projectbaseline objects don't know about the travel objects). Now, each activity/proposal/projectbaseline has a costcenter object associated to it(the relation from proposal/activity to costcenter is also many-to-one; the relation from the projectbaseline to costcenter is many-to-one, but it is not mapped in the projectbaseline object, but only the costcenterid field is mapped in the projectbaseline class). What I want to do is search for all the travels whose costcenterid has a given value. I also wanted to retrieve all the travellers for a travel and this information is stored in a separate object called travellers. So I created the following HQL query:

SELECT t FROM com.criticalsoftware.wiccore.wise.entities.travels.Travel AS t LEFT JOIN FETCH t.travellers WHERE t.activity.costCenter.id = ?1 OR t.projectBaseline.costCenterId = ?2 OR t.proposal.costCenter.id = ?3

The query above is translated to the following SQL query by hibernate:

Quote:
12:19:24,484 INFO [STDOUT] Hibernate:
select travel0_.IDTRAVEL as IDTRAVEL193_0_, travellers1_.idTravel as idTravel194_1_, travellers1_.login as login194_1_, travel0_.ACTIVITYID as ACTIVITYID193_0_, travel0_.approval as approval193_0_, travel0_.arrivalDate as arrivalD3_193_0_, travel0_.arrivalDateTime as arrivalD4_193_0_, travel0_.city as city193_0_, travel0_.IDCOUNTRY as IDCOUNTRY193_0_, travel0_.LOCALE as LOCALE193_0_, travel0_.creator as creator193_0_, travel0_.date as date193_0_, travel0_.departureDate as departur8_193_0_, travel0_.departureDateTime as departur9_193_0_, travel0_.driverLogin as driverL10_193_0_, travel0_.external as external193_0_, travel0_.hotelAddress as hotelAd12_193_0_, travel0_.hotelName as hotelName193_0_, travel0_.notes as notes193_0_, travel0_.notesApproval as notesAp15_193_0_, travel0_.notesDAF as notesDAF193_0_, travel0_.originCity as originCity193_0_, travel0_.ORIGINCOUNTRY as ORIGINC22_193_0_, travel0_.ORIGINLOCALE as ORIGINL23_193_0_, travel0_.PROJECTBASELINEID as PROJECT21_193_0_, travel0_.PROPOSALID as PROPOSALID193_0_, travel0_.state as state193_0_, travel0_.IDTRAVELGUIDE as IDTRAVE19_193_0_, travellers1_.IDTRAVEL as IDTRAVEL194_1_, travellers1_.IDTRAVEL as IDTRAVEL0__, travellers1_.idTravel as idTravel0__, travellers1_.login as login0__
from WICTRAVEL travel0_
left outer join WICTRAVELLERTRAVEL travellers1_ on travel0_.IDTRAVEL=travellers1_.IDTRAVEL,
WICACTIVITY activity2_, WICPROJECTBASELINE projectbas3_, WICPROPOSAL proposal4_
where travel0_.ACTIVITYID=activity2_.ID and travel0_.PROJECTBASELINEID=projectbas3_.ID and travel0_.PROPOSALID=proposal4_.ID and (activity2_.COSTCENTERID=? or projectbas3_.COSTCENTERID=? or proposal4_.COSTCENTERID=?)


This SQL query is not what I was expecting. What I was expecting and which really retrieves the data I wanted from the database was something like:

//...
from WICTRAVEL travel0_ left outer join WICTRAVELLERTRAVEL travellers1_ on travel0_.IDTRAVEL=travellers1_.IDTRAVEL
left outer join WICACTIVITY activity2_ on travel0_.ACTIVITYID=activity2_.ID
left outer join WICPROJECTBASELINE projectbas3_ on travel0_.PROJECTBASELINEID=projectbas3_.ID
left outer join WICPROPOSAL proposal4_ on travel0_.PROPOSALID=proposal4_.ID
where activity2_.COSTCENTERID=? or projectbas3_.COSTCENTERID=55? or proposal4_.COSTCENTERID=55?


So I am not sure if the HQL query I've written for the purpose described above is incorrect or it's a problem translating the HQL to SQL. Thanks for your help.
The Travel entity is below:

Code:
@Entity(name = "WICTRAVEL")
public class Travel implements Serializable{

    @Id
    @Column(name = "IDTRAVEL")
    private Long id;

    //other fields here



    @ManyToOne
    @JoinColumns({@JoinColumn(name = "IDCOUNTRY"), @JoinColumn(name="LOCALE")})
    private Country country;

    @ManyToOne
    @JoinColumns({@JoinColumn(name = "ORIGINCOUNTRY"), @JoinColumn(name="ORIGINLOCALE")})
    private Country originCountry;

    @ManyToOne
    @JoinColumn(name = "ACTIVITYID")
    private Activity activity;
   
    @ManyToOne
    @JoinColumn(name = "PROJECTBASELINEID")
    private ProjectBaseline projectBaseline;
   
    @ManyToOne
    @JoinColumn(name = "PROPOSALID")
    private Proposal proposal;
   
    @OneToMany(mappedBy = "travel")
    private List<TravellerTravel> travellers;
   
    /** default constructor */
    public Travel() {
    }
   
    //setters and getters here
   
}

@Entity(name = "WICTRAVELLERTRAVEL")
@IdClass(TravellerTravelPK.class)
public class TravellerTravel implements Serializable{
   @Id
   @Column(name="IDTRAVEL")
   private long idTravel;
   
   @Id
   @Column(name = "LOGIN")
   private String login;
   
   @ManyToOne
   @JoinColumn(name = "IDTRAVEL", insertable = false, updatable = false)
   private Travel travel;

}


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.