Hey,
I'm trying to write a HQL with join between 3 tables. Hibernate automatically performs the join but the problem is that it compares the wrong columns. The classes are CountryT, CustomerT, CustomerorderT.
CountryT - countrycode (PK)
CustomerT - countrycode, customerno (composite)
CustomerorderT - countrycode, customerorderno (composite)
(CustomerorderT also contains customerno but is not a part of the key. )
Relations:
CustomerorderT M:1 CustomerT
CustomerT M:1 CountryT
This is the HQL:
Code:
Query q = session.createQuery("SELECT co.customerT.firstname, co.customerT.lastname, "
+ "co.customerorderTPK.countryT.countryname, co.customerorderTPK.customerorderno "
+ "FROM net.sf.hibernate.CustomerorderT co");
With debugger:
Code:
11:21:13,418 DEBUG SQL:237 - select customert1_.FIRSTNAME as x0_0_, customert1_.LASTNAME as x1_0_, countryt2_.COUNTRYNAME as x2_0_, customeror0_.CUSTOMERORDERNO as x3_0_ from CUSTOMERORDER_T customeror0_, CUSTOMER_T customert1_, COUNTRY_T countryt2_ where customeror0_.COUNTRYCODE=customert1_.CUSTOMERNO and customeror0_.CUSTOMERNO=customert1_.COUNTRYCODE and customeror0_.COUNTRYCODE=countryt2_.COUNTRYCODE
As can be seen it compares countrycode with customerno (
customeror0_.COUNTRYCODE=customert1_.CUSTOMERNO and customeror0_.CUSTOMERNO=customert1_.COUNTRYCODE ) which of course is wrong. customerno is not a specific key in the CustomerorderT which make a line in a table unique. But I believe it should be used in the join, or am I wrong? But it seems like Hibernate doesn't find it.
I should be a fault in the relations but the code i generated in middlegen, both the xml and java files from the database.
So how do I solve this problem so the join becomes correct? Perhaps my query is wrong?
MVH Newman
Login
FAQ
Search
