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.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
funkyman2000
 Post subject: Incorrect HQL join
PostPosted: Tue Apr 03, 2007 4:51 am 
Newbie

Joined: Tue Apr 03, 2007 4:35 am
Posts: 1
Hi folks!

I'm trying to create a HQL join but hibernate (3.2.1.ga) creates wrong SQL.
I wanna join the customer table with the address table using a 1 to n relationship. The primary key of both tables is called
Quote:
ID
.

The foreign key in the address table is called
Quote:
ID_CST_CUSTOMER
.


Hibernate joins the customer primary key and the address primary key (that's wrong, of course)!!!

What's my mistake?

Thanks a lot!!

regards,
Steven


Here is my HQL query:
Code:
select cust from CustomerEntity cust left outer join fetch cust.cstAddresses adr where cust.id=? and cust.client=? and cust.subclient=? and cust.validFlag='1' and cust.activeFlag='1' and adr.validFlag='1' and adr.activeFlag='1'


Here is the generated SQL query:
Code:
select
customeren0_.ID as ID9_0_, cstaddress1_.ID as ID2_1_, customeren0_.LOCK_NO as LOCK2_9_0_, customeren0_.ID_CST_MASTER as ID3_9_0_,
customeren0_.CLIENT as CLIENT9_0_, customeren0_.SUBCLIENT as SUBCLIENT9_0_, customeren0_.CUSTOMER_NO as CUSTOMER6_9_0_,
customeren0_.CUSTOMER_STATUS as CUSTOMER7_9_0_, customeren0_.STATUS as STATUS9_0_, customeren0_.GENDER_CODE as GENDER9_9_0_,
customeren0_.NATIONALITY_CODE as NATIONA10_9_0_, customeren0_.LANGUAGE_CODE as LANGUAGE11_9_0_, customeren0_.SALUTATION_CODE as SALUTATION12_9_0_,
customeren0_.NAME_AFFIX as NAME13_9_0_, customeren0_.TITLE as TITLE9_0_, customeren0_.FIRST_NAME as FIRST15_9_0_,
customeren0_.MIDDLE_NAME as MIDDLE16_9_0_, customeren0_.NAME_PREFIX as NAME17_9_0_, customeren0_.LAST_NAME as LAST18_9_0_,
customeren0_.BIRTH_DATE as BIRTH19_9_0_, customeren0_.NO_01_CUSTOM as NO20_9_0_, customeren0_.NO_02_CUSTOM as NO21_9_0_,
customeren0_.NO_03_CUSTOM as NO22_9_0_, customeren0_.NO_04_CUSTOM as NO23_9_0_, customeren0_.NO_05_CUSTOM as NO24_9_0_,
customeren0_.DEF_TIME as DEF25_9_0_, customeren0_.DEF_USER as DEF26_9_0_, customeren0_.MOD_TIME as MOD27_9_0_,
customeren0_.MOD_USER as MOD28_9_0_, customeren0_.VALID_FLAG as VALID29_9_0_, customeren0_.ACTIVE_FLAG as ACTIVE30_9_0_,
cstaddress1_.LOCK_NO as LOCK2_2_1_, cstaddress1_.ID_CST_CUSTOMER as ID3_2_1_, cstaddress1_.CLIENT as CLIENT2_1_,
cstaddress1_.SUBCLIENT as SUBCLIENT2_1_, cstaddress1_.STATUS as STATUS2_1_, cstaddress1_.CUSTOMER_NO as CUSTOMER7_2_1_,
cstaddress1_.UPDATED_FLAG as UPDATED8_2_1_, cstaddress1_.ADDRESS_TYPE_CODE as ADDRESS9_2_1_, cstaddress1_.SALUTATION_CODE as SALUTATION10_2_1_,
cstaddress1_.NAME_AFFIX as NAME11_2_1_, cstaddress1_.TITLE as TITLE2_1_, cstaddress1_.FIRST_NAME as FIRST13_2_1_,
cstaddress1_.MIDDLE_NAME as MIDDLE14_2_1_, cstaddress1_.NAME_PREFIX as NAME15_2_1_, cstaddress1_.LAST_NAME as LAST16_2_1_,
cstaddress1_.COMPANY as COMPANY2_1_, cstaddress1_.DEPARTMENT as DEPARTMENT2_1_, cstaddress1_.SUPPLEMENT as SUPPLEMENT2_1_,
cstaddress1_.STREET as STREET2_1_, cstaddress1_.STREET_NO as STREET21_2_1_, cstaddress1_.PO_BOX as PO22_2_1_,
cstaddress1_.BLOCK_OF_FLATS as BLOCK23_2_1_, cstaddress1_.PROVINCE_DISTRICT as PROVINCE24_2_1_, cstaddress1_.ZIP as ZIP2_1_,
cstaddress1_.CITY as CITY2_1_, cstaddress1_.STATE as STATE2_1_, cstaddress1_.COUNTRY_CODE as COUNTRY28_2_1_,
cstaddress1_.LANGUAGE_CODE as LANGUAGE29_2_1_, cstaddress1_.STANDARD_FLAG as STANDARD30_2_1_, cstaddress1_.VALID_FROM as VALID31_2_1_,
cstaddress1_.VALID_TO as VALID32_2_1_, cstaddress1_.NO_01_CUSTOM as NO33_2_1_, cstaddress1_.NO_02_CUSTOM as NO34_2_1_,
cstaddress1_.NO_03_CUSTOM as NO35_2_1_, cstaddress1_.NO_04_CUSTOM as NO36_2_1_, cstaddress1_.NO_05_CUSTOM as NO37_2_1_,
cstaddress1_.DEF_TIME as DEF38_2_1_, cstaddress1_.DEF_USER as DEF39_2_1_, cstaddress1_.MOD_TIME as MOD40_2_1_,
cstaddress1_.MOD_USER as MOD41_2_1_, cstaddress1_.VALID_FLAG as VALID42_2_1_, cstaddress1_.ACTIVE_FLAG as ACTIVE43_2_1_,
cstaddress1_.ID as ID0__
from
cst_customer customeren0_
left outer join cst_address cstaddress1_
on customeren0_.ID=cstaddress1_.ID
where customeren0_.ID=1 and customeren0_.CLIENT='testclient' and customeren0_.SUBCLIENT='DEU' and customeren0_.VALID_FLAG='1' and
customeren0_.ACTIVE_FLAG='1' and cstaddress1_.VALID_FLAG='1' and cstaddress1_.ACTIVE_FLAG='1'


Here are my mapping files:

CstCustomer.hbm.xml:
Code:
<hibernate-mapping>
    <class name="de.test.core.customer.internal.dao.hibernate.entity.CustomerEntity" table="cst_customer">
        <id name="id" type="java.lang.Long">
            <column name="ID" />
            <generator class="native" />
        </id>
        <version name="lockNo" access="field" type="java.lang.Short">
            <column name="LOCK_NO"/>
        </version>       
        <property name="idMaster" type="java.lang.Long">
            <column name="ID_CST_MASTER"/>
        </property>
        <property name="client" type="java.lang.String">
            <column name="CLIENT" />
        </property>
        <property name="subclient" type="java.lang.String">
            <column name="SUBCLIENT"/>
        </property>
        <property name="customerNo" type="java.lang.String">
            <column name="CUSTOMER_NO"/>
        </property>
        <property name="customerStatus" type="java.lang.Integer">
            <column name="CUSTOMER_STATUS" />
        </property>
        <property name="status" type="java.lang.String">
            <column name="STATUS"  />
        </property>
        <property name="genderCode" type="java.lang.String">
            <column name="GENDER_CODE" />
        </property>
        <property name="nationalityCode" type="java.lang.String">
            <column name="NATIONALITY_CODE" />
        </property>
        <property name="languageCode" type="java.lang.String">
            <column name="LANGUAGE_CODE"/>
        </property>
        <property name="salutationCode" type="java.lang.String">
            <column name="SALUTATION_CODE"/>
        </property>
        <property name="nameAffix" type="java.lang.String">
            <column name="NAME_AFFIX"/>
        </property>
        <property name="title" type="java.lang.String">
            <column name="TITLE"/>
        </property>
        <property name="firstName" type="java.lang.String">
            <column name="FIRST_NAME"/>
        </property>
        <property name="middleName" type="java.lang.String">
            <column name="MIDDLE_NAME"/>
        </property>
        <property name="namePrefix" type="java.lang.String">
            <column name="NAME_PREFIX"/>
        </property>
        <property name="lastName" type="java.lang.String">
            <column name="LAST_NAME"/>
        </property>
        <property name="birthDate" type="java.util.Date">
            <column name="BIRTH_DATE"/>
        </property>
        <property name="no01Column" type="java.lang.String">
            <column name="NO_01_CUSTOM"/>
        </property>
        <property name="no02Column" type="java.lang.String">
            <column name="NO_02_CUSTOM"/>
        </property>
        <property name="no03Column" type="java.lang.String">
            <column name="NO_03_CUSTOM" />
        </property>
        <property name="no04Column" type="java.lang.String">
            <column name="NO_04_CUSTOM"/>
        </property>
        <property name="no05Column" type="java.lang.String">
            <column name="NO_05_CUSTOM"/>
        </property>
        <property name="defTime" type="java.util.Date">
            <column name="DEF_TIME"/>
        </property>
        <property name="defUser" type="java.lang.String">
            <column name="DEF_USER"  />
        </property>
        <property name="modTime" type="java.util.Date">
            <column name="MOD_TIME"/>
        </property>
        <property name="modUser" type="java.lang.String">
            <column name="MOD_USER"/>
        </property>
        <property name="validFlag" type="java.lang.Boolean">
            <column name="VALID_FLAG"/>
        </property>
        <property name="activeFlag" type="java.lang.Boolean">
            <column name="ACTIVE_FLAG"/>
        </property>
        <set name="cstContacts" inverse="true" cascade="save-update">
            <key>
                <column name="ID" />
            </key>
            <one-to-many class="de.test.core.customer.internal.dao.hibernate.entity.ContactEntity" />
        </set>
        <set name="cstImportAddresses" inverse="true" cascade="save-update">
            <key>
                <column name="ID" />
            </key>
            <one-to-many class="de.test.core.customer.internal.dao.hibernate.entity.AddressImportEntity" />
        </set>
        <set name="cstAddresses" inverse="true" cascade="save-update">
            <key>
                <column name="ID" />
            </key>
            <one-to-many class="de.test.core.customer.internal.dao.hibernate.entity.AddressEntity" />
        </set>
        <set name="cstCommunicationChannels" inverse="true" cascade="save-update">
            <key>
                <column name="ID"/>
            </key>
            <one-to-many class="de.test.core.customer.internal.dao.hibernate.entity.CommunicationChannelEntity" />
        </set>
        <set name="cstBlocks" inverse="true" cascade="save-update">
            <key>
                <column name="ID" />
            </key>
            <one-to-many class="de.test.core.customer.internal.dao.hibernate.entity.BlockEntity" />
        </set>
        <set name="cstCustLimits" inverse="true" cascade="save-update">
            <key>
                <column name="ID"/>
            </key>
            <one-to-many class="de.test.core.customer.internal.dao.hibernate.entity.CustomerLimitEntity" />
        </set>
        <set name="cstPaymentInstruments" inverse="true" cascade="save-update">
            <key>
                <column name="ID"/>
            </key>
            <one-to-many class="de.test.core.customer.internal.dao.hibernate.entity.PaymentInstrumentEntity" />
        </set>
    </class>
</hibernate-mapping>


CstAddress.hbm.xml:
Code:
<hibernate-mapping>
    <class name="de.test.core.customer.internal.dao.hibernate.entity.AddressEntity" table="cst_address">
        <id name="id" type="java.lang.Long">
            <column name="ID" />
            <generator class="native" />
        </id>
        <version name="lockNo" access="field" type="java.lang.Short">
            <column name="LOCK_NO" />
        </version>
        <many-to-one name="customerEntity" class="de.test.core.customer.internal.dao.hibernate.entity.CustomerEntity" fetch="select">
            <column name="ID_CST_CUSTOMER"/>
        </many-to-one>
        <property name="client" type="java.lang.String">
            <column name="CLIENT"/>
        </property>
        <property name="subclient" type="java.lang.String">
            <column name="SUBCLIENT" />
        </property>
        <property name="status" type="java.lang.String">
            <column name="STATUS"/>
        </property>       
        <property name="customerNo" type="java.lang.String">
            <column name="CUSTOMER_NO" />
        </property>
        <property name="updatedFlag" type="java.lang.Boolean">
            <column name="UPDATED_FLAG" />
        </property>
        <property name="addressTypeCode" type="java.lang.String">
            <column name="ADDRESS_TYPE_CODE"/>
        </property>
        <property name="salutationCode" type="java.lang.String">
            <column name="SALUTATION_CODE"/>
        </property>
        <property name="nameAffix" type="java.lang.String">
            <column name="NAME_AFFIX" />
        </property>
        <property name="title" type="java.lang.String">
            <column name="TITLE" />
        </property>
        <property name="firstName" type="java.lang.String">
            <column name="FIRST_NAME" />
        </property>
        <property name="middleName" type="java.lang.String">
            <column name="MIDDLE_NAME" />
        </property>
        <property name="namePrefix" type="java.lang.String">
            <column name="NAME_PREFIX" />
        </property>
        <property name="lastName" type="java.lang.String">
            <column name="LAST_NAME"/>
        </property>
        <property name="company" type="java.lang.String">
            <column name="COMPANY" />
        </property>
        <property name="department" type="java.lang.String">
            <column name="DEPARTMENT" />
        </property>
        <property name="supplement" type="java.lang.String">
            <column name="SUPPLEMENT"/>
        </property>
        <property name="street" type="java.lang.String">
            <column name="STREET"/>
        </property>
        <property name="streetNo" type="java.lang.String">
            <column name="STREET_NO" />
        </property>
        <property name="poBox" type="java.lang.String">
            <column name="PO_BOX"/>
        </property>
        <property name="blockOfFlats" type="java.lang.String">
            <column name="BLOCK_OF_FLATS"/>
        </property>
        <property name="provinceDistrict" type="java.lang.String">
            <column name="PROVINCE_DISTRICT"/>
        </property>
        <property name="zip" type="java.lang.String">
            <column name="ZIP"/>
        </property>
        <property name="city" type="java.lang.String">
            <column name="CITY"/>
        </property>
        <property name="state" type="java.lang.String">
            <column name="STATE"/>
        </property>
        <property name="countryCode" type="java.lang.String">
            <column name="COUNTRY_CODE" />
        </property>
        <property name="languageCode" type="java.lang.String">
            <column name="LANGUAGE_CODE" />
        </property>
        <property name="standardFlag" type="java.lang.Boolean">
            <column name="STANDARD_FLAG" />
        </property>       
        <property name="validFrom" type="java.util.Date">
            <column name="VALID_FROM"/>
        </property>
        <property name="validTo" type="java.util.Date">
            <column name="VALID_TO"/>
        </property>       
        <property name="no01Custom" type="java.lang.String">
            <column name="NO_01_CUSTOM"/>
        </property>
        <property name="no02Custom" type="java.lang.String">
            <column name="NO_02_CUSTOM"/>
        </property>
        <property name="no03Custom" type="java.lang.String">
            <column name="NO_03_CUSTOM"/>
        </property>
        <property name="no04Custom" type="java.lang.String">
            <column name="NO_04_CUSTOM"/>
        </property>
        <property name="no05Custom" type="java.lang.String">
            <column name="NO_05_CUSTOM"/>
        </property>
        <property name="defTime" type="java.sql.Timestamp">
            <column name="DEF_TIME" />
        </property>
        <property name="defUser" type="java.lang.String">
            <column name="DEF_USER"/>
        </property>
        <property name="modTime" type="java.sql.Timestamp">
            <column name="MOD_TIME"/>
        </property>
        <property name="modUser" type="java.lang.String">
            <column name="MOD_USER"/>
        </property>
        <property name="validFlag" type="java.lang.Boolean">
            <column name="VALID_FLAG"/>
        </property>
        <property name="activeFlag" type="java.lang.Boolean">
            <column name="ACTIVE_FLAG"/>
        </property>
    </class>
</hibernate-mapping>
Top
 Profile  
 
piet.t
 Post subject:
PostPosted: Tue Apr 03, 2007 5:05 am 
Regular
Regular

Joined: Fri May 12, 2006 4:05 am
Posts: 106
You'll have to use
Code:
<key>
   <column name="ID_CST_CUSTOMER" />
</key>

<key> always gives the name of the foreing-key-column!!
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.