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;
}
Login
FAQ
Search