Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Hello... Need help with this.

Have the following mapped class definitions:


<class name="com.applevacations.special.Special" schema="DBSCHEMA" table="ZWSPECIAL">
<id column="MATNR" name="id" type="string">
<generator class="native"/>
</id>
<property column="VENDORID" name="hotelId" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="HOTAIRCRSNM" length="40" name="hotelName" type="string"/>
<property column="SPECIALTYPE" length="2" name="specialType" type="string"/>
<property column="APPLERATING" length="16" name="appleRating" type="string"/>
<property column="ROOMVIEWMEAL" length="80" name="roomViewAndMealPlan" type="string"/>
<property column="ROOMTYPE" length="40" name="roomType" type="string"/>
<property column="HOTELTYPE" length="15" name="hotelType" type="string"/>
<property column="HOTELCHAIN" length="30" name="hotelChain" type="string"/>
<property column="MAINTSPL" length="1" name="maintainedSpecial" type="string"/>
<property column="PUBLISH" length="1" name="publishedSpecial" type="string"/>
<property column="ZZGATEWAY" name="gatewayCode" type="string"/>
<property column="ZZCSCODE" name="cruiseCode" type="string"/>
<property column="ZZDEST" name="destinationCode" type="string"/>

<!--
The specials details has a lazy false since we will always need to load the detail whenever we need to load the special
If ever there arises a case where we dont need it then make this true and load it on demand
-->
<bag name="details" cascade="all" inverse="true" lazy="true">
<key column="MATNR"/>
<one-to-many class="com.applevacations.special.SpecialDetail"/>
</bag>

<set name="interests" cascade="all" inverse="true" lazy="true">
<key column="MATNR"/>
<one-to-many class="com.applevacations.special.SpecialInterest"/>
</set>
</class>

<class name="com.applevacations.special.SpecialDetail" schema="DBSCHEMA" table="ZWSPLDETAIL">
<composite-id>
<key-many-to-one column="MATNR" name="special" class="com.applevacations.special.Special"/>
<key-property column="CHARG" length="10" name="batchNumber" type="string"/>
</composite-id>
<property column="DURATION" length="2" name="duration" not-null="true" type="integer"/>
<property column="DEPARTUREDATE" length="10" name="departureDate" not-null="true" type="date"/>
<property column="DAYOFWEEK" length="3" name="dayOfWeek" not-null="true" type="string"/>
<property column="ORIGINALPRICE" length="15" name="originalPrice" not-null="true" type="double"/>
<property column="PRICENOW" length="15" name="priceNow" not-null="true" type="double"/>
<property column="DISCOUNT" length="15" name="discount" type="double"/>
<property column="MAINTSPL" length="1" name="maintainedSpecial" not-null="true" type="string"/>
<property column="PUB" length="1" name="publishedSpecial" type="string"/>
<property column="ZZRISK" length="1" name="riskFlag" type="string"/>
</class>

<class name="com.applevacations.hotel.HotelAppleRating" schema="DBSCHEMA" table="ZAHOTELM">
<id column="MATNR" name="matnr" type = "string">
<generator class="native" />
</id>
<property column="ZZAPPLE_RATING" length="2" name="applerating" type="string"/>
</class>

<class name="com.applevacations.hotel.Hotel" schema="DBSCHEMA" table="ZWHOTFTRS">
<id column="ZZVENDOR" name="id" type="root.common.db.hibernateimpl.CharacterTrimType">
<generator class="native"/>
</id>
<property column="ZZDESID" length="6" name="regionDestinationCode" not-null="true" type="string"/>
<many-to-one column="ZZRGNID" name="region" class="com.applevacations.region.Region"/>
<many-to-one column="ZZRSTID" name="resort" class="com.applevacations.hotel.Resort"/>
<property column="ZZPRDHID" length="18" name="productHierarchyId" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZHOTNAME" length="60" name="name" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZALLINC" length="1" name="allInclusive" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZCOUPLES" length="1" name="couples" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZFAMILIES" length="1" name="families" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZSINGLES" length="1" name="singles" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZCHLDAGE" length="50" name="childrenAges" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZAMEREXP" length="1" name="americanExpressAccepted" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZDINERS" length="1" name="dinersClubAccepted" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZDISCOVER" length="1" name="discoverAccepted" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZMASTER" length="1" name="masterCardAccepted" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZVISA" length="1" name="visaAccepted" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<property column="ZZOTHERS" length="20" name="otherCardsAccepted" not-null="true" type="root.common.db.hibernateimpl.CharacterTrimType"/>
<one-to-one name="generalFacilities"/>
<one-to-one name="childrenFacilities"/>
<set name="mealPlans" cascade="all" inverse="true" lazy="true">
<key column="ZZVENDOR"/>
<one-to-many class="com.applevacations.hotel.facilities.Meal"/>
</set>
<one-to-one name="sportsFacilities"/>
<set name="restaurants" cascade="all" inverse="true" lazy="true">
<key column="ZZVENDOR"/>
<one-to-many class="com.applevacations.hotel.facilities.Restaurant"/>
</set>
<set name="roomCategories" cascade="all" inverse="true" lazy="true">
<key column="ZZVENDOR"/>
<one-to-many class="com.applevacations.hotel.facilities.RoomCategory"/>
</set>
<set name="photos" cascade="all" order-by="category asc, displayOrder asc" inverse="true" lazy="true">
<key column="PARENTID"/>
<one-to-many class="com.applevacations.common.Photo"/>
</set>
<one-to-one name="marketingInfo" property-ref="hotelId"/>
<one-to-one name="hotelCharacteristics"/>
<one-to-one name="digitalBrochure"/>
</class>

Current query that joins the first two tables goes like this

select distinct new SpecialCategoryToSpecial(special) from Special as special join special.details as detail where (special.gatewayCode=:gatewayCode OR special.gatewayCode IS NULL OR special.gatewayCode='') and detail.departureDate < :departureDate and detail.departureDate >= CURRENT DATE AND (special.publishedSpecial='Y' OR special.publishedSpecial IS NULL)

Hibernate send the query to db2 like this...

select
distinct special2_.MATNR as MATNR,
special2_.VENDORID as VENDORID,
special2_.HOTAIRCRSNM as HOTAIRCR3_,
special2_.SPECIALTYPE as SPECIALT4_,
special2_.APPLERATING as APPLERAT5_,
special2_.ROOMVIEWMEAL as ROOMVIEW6_,
special2_.ROOMTYPE as ROOMTYPE,
special2_.HOTELTYPE as HOTELTYPE,
special2_.HOTELCHAIN as HOTELCHAIN,
special2_.MAINTSPL as MAINTSPL,
special2_.PUBLISH as PUBLISH,
special2_.ZZGATEWAY as ZZGATEWAY,
special2_.ZZCSCODE as ZZCSCODE,
special2_.ZZDEST as ZZDEST,
special2_.MATNR as x0_0_
from DBSCHEMA.ZWSPECIAL special2_ inner join DBSCHEMA.ZWSPLDETAIL details3_ on special2_.MATNR=details3_.MATNR
where ((special2_.ZZGATEWAY=? )OR(special2_.ZZGATEWAY IS NULL )OR(special2_.ZZGATEWAY='' ))and
(details3_.DEPARTUREDATE<? )and(details3_.DEPARTUREDATE>=CURRENT DATE )AND
((special2_.PUBLISH='Y' )OR(special2_.PUBLISH IS NULL ))


Made a couple of changes to the query that is being sent to db2 to add tables ZAHOTELM and ZWHOTFTRS. What
changes in the mapping file I need to do to support a query like this (see below)...

select
distinct special2_.MATNR as MATNR,
special2_.VENDORID as VENDORID,
special2_.HOTAIRCRSNM as HOTAIRCR3_,
special2_.SPECIALTYPE as SPECIALT4_,
special2_.APPLERATING as APPLERAT5_,
special2_.ROOMVIEWMEAL as ROOMVIEW6_,
special2_.ROOMTYPE as ROOMTYPE,
special2_.HOTELTYPE as HOTELTYPE,
special2_.HOTELCHAIN as HOTELCHAIN,
special2_.MAINTSPL as MAINTSPL,
special2_.PUBLISH as PUBLISH,
special2_.ZZGATEWAY as ZZGATEWAY,
special2_.ZZCSCODE as ZZCSCODE,
special2_.ZZDEST as ZZDEST,
special2_.MATNR as x0_0_,
hotelapple1_.ZZAPPLE_RATING as x1_0_,
hotel0_.ZZALLINC as x2_0_
from DBSCHEMA.ZWSPECIAL special2_ inner join DBSCHEMA.ZWSPLDETAIL details3_ on special2_.MATNR=details3_.MATNR
left outer join DBSCHEMA.ZAHOTELM hotelapple1_ on hotelapple1_.MATNR = substr(special2_.MATNR , 4 , 10)
left outer join DBSCHEMA.ZWHOTFTRS hotel0_ on hotel0_.ZZVENDOR = special2_.VENDORID
where ((special2_.ZZGATEWAY=? )OR(special2_.ZZGATEWAY IS NULL )OR(special2_.ZZGATEWAY='' ))and
(details3_.DEPARTUREDATE<? )and(details3_.DEPARTUREDATE>=CURRENT DATE )AND
((special2_.PUBLISH='Y' )OR(special2_.PUBLISH IS NULL ))


Trying this...but, is not working, getting the following error...
outer or full join must be followed by path expression



select distinct new SpecialCategoryToSpecial(special,zahotelm.applerating,zwhotftrs.allInclusive) from Special as special join special.details as detail left outer join HotelAppleRating as zahotelm on (zahotelm.matnr = substr(special.id,4,10)) left outer join Hotel as zwhotftrs on (zwhotftrs.id = special.hotelId) where (special.gatewayCode=:gatewayCode or special.gatewayCode IS NULL or special.gatewayCode='') and (detail.departureDate < :departureDate and detail.departureDate >= CURRENT DATE) and (special.publishedSpecial='Y' or special.publishedSpecial IS NULL)



Thanks,

Can anybody please help me with this... I need to define association for 4 tables, and I need help with it...

Thanks,