So, I've tried what you said Mrtz and am still having trouble. Would you have any idea as to why the join is not working?
I have many mapping files but I'll put in the 2 that I'm working with (PIF and Property). PIF has a many-to-one of Property which I have set as lazy="false" and fetch="join". I have also set MaxFetchDepth="3". The query is still not joining these values
Query I'm Using
Code:
from PIF pif
where pif.CurrentOwner.Id = ?
or pif.Id in (
select a.PIF.Id
from Activity a
where a.AssignedTo.Id = ?
)
order by pif.CreatedDate
Configuration File
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE server>
<server>
<mbean code="org.jboss.hibernate.jmx.Hibernate" name="jboss.har:service=Hibernate">
<!-- depends>jboss.jca:service=RARDeployer</depends -->
<depends>jboss.jca:service=DataSourceBinding,name=jdbc/MAFC</depends>
<attribute name="DatasourceName">java:/jdbc/MAFC</attribute>
<attribute name="SessionFactoryName">java:/hibernate/MAFCHibernateFactory</attribute>
<!-- attribute name="CacheProviderClass">org.hibernate.cache.OSCacheProvider</attribute -->
<attribute name="SecondLevelCacheEnabled">true</attribute>
<attribute name="CacheProviderClass">com.mafc.moses.cache.OSCacheProvider</attribute>
<attribute name="Dialect">org.hibernate.dialect.MySQLDialect</attribute>
<attribute name="QueryCacheEnabled">true</attribute>
<attribute name="ShowSqlEnabled">true</attribute>
<attribute name="MaxFetchDepth">3</attribute>
</mbean>
</server>
Mapping Files (PIF & Property)
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.mafc.moses.bo">
<class
name="PIF"
table="pif"
dynamic-update="true"
>
<cache usage="read-write"/>
<id
name="Id"
type="java.lang.Long"
column="pif_id"
>
<generator class="identity"/>
</id>
<property
name="ReservationNumber"
column="reservation_nbr"
type="string"
not-null="false"
length="35"
/>
<property
name="CreatedDate"
column="created_date"
type="timestamp"
not-null="true"
length="19"
/>
<property
name="ListPrice"
column="list_price"
type="big_decimal"
not-null="false"
length="12"
/>
<property
name="ListDate"
column="list_date"
type="date"
not-null="false"
/>
<property
name="AcceptPrice"
column="accept_price"
type="big_decimal"
not-null="false"
length="12"
/>
<property
name="AcquisitionAmount"
column="acquisition_amount"
type="big_decimal"
not-null="false"
length="12"
/>
<property
name="MortgageRemainingBalance"
column="mortgage_remaining_balance"
type="big_decimal"
not-null="false"
length="12"
/>
<property
name="CurrentInPayments"
column="is_current_in_payments"
type="true_false"
/>
<property
name="RepairRoof"
column="repair_roof"
type="true_false"
/>
<property
name="RepairInteriorPaint"
column="repair_interior_paint"
type="true_false"
/>
<property
name="RepairExteriorPaint"
column="repair_exterior_paint"
type="true_false"
/>
<property
name="RepairCarpet"
column="repair_carpet"
type="true_false"
/>
<property
name="RepairFurnace"
column="repair_furnace"
type="true_false"
/>
<property
name="RepairAC"
column="repair_ac"
type="true_false"
/>
<property
name="RepairWindows"
column="repair_windows"
type="true_false"
/>
<property
name="RepairCabinets"
column="repair_cabinets"
type="true_false"
/>
<property
name="RepairStove"
column="repair_stove"
type="true_false"
/>
<property
name="RepairRefridgerator"
column="repair_refridgerator"
type="true_false"
/>
<property
name="OtherRepairs"
column="other_repairs"
type="string"
not-null="false"
/>
<property
name="OtherImprovements"
column="other_improvements"
type="string"
not-null="false"
/>
<property
name="VacateDate"
column="vacate_date"
type="date"
not-null="false"
/>
<property
name="OutsaleAmount"
column="outsale_amount"
type="big_decimal"
not-null="false"
/>
<property
name="OutsaleDepositAmount"
column="outsale_deposit_amount"
type="big_decimal"
not-null="false"
/>
<many-to-one
name="Property"
column="property_id"
class="Property"
not-null="true"
lazy="false"
fetch="join"
>
</many-to-one>
<many-to-one
name="ContactPerson"
column="contact_person_id"
class="Person"
not-null="false"
>
</many-to-one>
<many-to-one
name="CurrentOwner"
column="current_owner_id"
class="User"
not-null="false"
>
</many-to-one>
<many-to-one
name="CurrentValuation"
column="current_valuation_id"
class="Valuation"
>
</many-to-one>
<many-to-one
name="RequestedValuation"
column="requested_valuation_id"
class="Valuation"
>
</many-to-one>
<many-to-one
name="CurrentAcquisitionOffer"
column="current_acquisition_offer_id"
class="AcquisitionOffer"
not-null="false"
>
</many-to-one>
<many-to-one
name="CurrentOutsaleOffer"
column="current_outsale_offer_id"
class="OutsaleOffer"
not-null="false"
>
</many-to-one>
<many-to-one
name="TermiteInspection"
column="termite_inspection_id"
class="ContractorInspection"
not-null="false"
>
</many-to-one>
<many-to-one
name="HVACInspection"
column="hvac_inspection_id"
class="ContractorInspection"
not-null="false"
>
</many-to-one>
<many-to-one
name="MoldInspection"
column="mold_inspection_id"
class="ContractorInspection"
not-null="false"
>
</many-to-one>
<many-to-one
name="HomeInspection"
column="home_inspection_id"
class="Valuation"
not-null="false"
>
</many-to-one>
<many-to-one
name="DriveByInspection"
column="drive_by_inspection_id"
class="Valuation"
not-null="false"
>
</many-to-one>
<many-to-one
name="OccupancyType"
column="occupancy_type"
class="OccupancyType"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="Condition"
column="property_condition"
class="PropertyCondition"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="MLSStatus"
column="mls_status"
class="MlsStatus"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="Stage"
column="property_stage"
class="PropertyStage"
not-null="true"
lazy="false"
>
</many-to-one>
<many-to-one
name="SubStage"
column="property_sub_stage"
class="PropertySubStage"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="LeaseType"
column="lease_type"
class="LeaseType"
lazy="false"
>
</many-to-one>
<many-to-one
name="SellReason"
column="sell_reason"
class="SellReason"
lazy="false"
>
</many-to-one>
<many-to-one
name="AdvertisingType"
column="advertising_type"
class="AdvertisingType"
lazy="false"
>
</many-to-one>
<many-to-one
name="DeadDealReason"
column="dead_deal_reason"
class="DeadDealReason"
lazy="false"
>
</many-to-one>
<bag name="Events" order-by="created_date desc" cascade="save-update">
<cache usage="read-write"/>
<key column="pif_id"/>
<one-to-many class="PropertyEvent"/>
</bag>
<bag name="UnapprovedJobs" order-by="created_date asc" where="is_approved = 'F' and is_submitted_for_approval = 'F'" inverse="true">
<cache usage="read-write"/>
<key column="pif_id"/>
<one-to-many class="Job"/>
</bag>
<bag name="AvailableJobs" order-by="created_date asc" where="bid_id is null and is_approved = 'T'" inverse="true">
<cache usage="read-write"/>
<key column="pif_id"/>
<one-to-many class="Job"/>
</bag>
<set name="Owners" sort="natural" cascade="none" inverse="true" where="type='O'" table="pif_person">
<cache usage="read-write"/>
<key column="pif_id"/>
<many-to-many column="person_id" class="Person"/>
</set>
<set name="Tenants" sort="natural" cascade="none" inverse="true" where="type='T'" table="pif_person">
<cache usage="read-write"/>
<key column="pif_id"/>
<many-to-many column="person_id" class="Person"/>
</set>
<bag name="Documents" order-by="created_date desc" inverse="true">
<cache usage="read-write"/>
<key column="pif_id"/>
<one-to-many class="PIFDocument"/>
</bag>
<bag name="Pictures" order-by="created_date desc" inverse="true">
<cache usage="read-write"/>
<key column="pif_id"/>
<one-to-many class="PIFPicture"/>
</bag>
<bag name="Comps" order-by="created_date" inverse="true">
<cache usage="read-write"/>
<key column="pif_id"/>
<one-to-many class="Comp"/>
</bag>
</class>
<query name="findPIFsForUser">
from PIF pif
where pif.CurrentOwner.Id = ?
or pif.Id in (
select a.PIF.Id
from Activity a
where a.AssignedTo.Id = ?
)
order by pif.CreatedDate
</query>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.mafc.moses.bo">
<class
name="Property"
table="property"
lazy="false"
>
<cache usage="read-write"/>
<id
name="Id"
type="java.lang.Long"
column="property_id"
>
<generator class="identity"/>
</id>
<property
name="CreatedDate"
column="created_date"
type="timestamp"
not-null="true"
length="19"
/>
<property
name="TypeABC"
column="property_type_abc"
type="string"
not-null="true"
/>
<property
name="NumBedrooms"
column="num_bedrooms"
type="java.lang.Short"
not-null="false"
length="6"
/>
<property
name="NumBaths"
column="num_baths"
type="java.lang.Short"
not-null="false"
length="6"
/>
<property
name="TotalSqFt"
column="total_sq_ft"
type="integer"
not-null="false"
length="11"
/>
<property
name="AboveGroundSqFt"
column="above_ground_sq_ft"
type="integer"
not-null="false"
length="11"
/>
<property
name="BasementSqFt"
column="basement_sq_ft"
type="integer"
not-null="false"
length="11"
/>
<property
name="NumRooms"
column="num_rooms"
type="java.lang.Short"
not-null="false"
length="6"
/>
<property
name="NumHalfBaths"
column="num_half_baths"
type="java.lang.Short"
not-null="false"
length="6"
/>
<property
name="Pool"
column="has_pool"
type="true_false"
/>
<property
name="YearBuilt"
column="year_built"
type="integer"
not-null="false"
length="11"
/>
<property
name="NumAboveGroundStories"
column="num_above_ground_levels"
type="java.lang.Short"
not-null="false"
length="6"
/>
<property
name="NumFireplaces"
column="num_fireplaces"
type="java.lang.Short"
not-null="false"
length="6"
/>
<property
name="NumStories"
column="num_stories"
type="java.lang.Short"
not-null="false"
length="6"
/>
<property
name="FurnaceAge"
column="furnace_age"
type="integer"
not-null="false"
length="11"
/>
<property
name="CrawlSpace"
column="has_crawl_space"
type="true_false"
/>
<property
name="OilTank"
column="has_oil_tank"
type="true_false"
/>
<property
name="Well"
column="has_well"
type="true_false"
/>
<property
name="SepticTank"
column="has_septic_tank"
type="true_false"
/>
<property
name="AssociationFeePerMonth"
column="association_fee_per_month"
type="big_decimal"
not-null="false"
length="6"
/>
<property
name="ReceiveNewsletter"
column="receive_newsletter"
type="true_false"
/>
<property
name="Subdivision"
column="subdivision_name"
type="string"
not-null="false"
/>
<property
name="SpecialView"
column="special_view"
type="string"
not-null="false"
>
</property>
<component name="PublicRecord" class="PublicRecord">
<property
name="TaxId"
column="pr_tax_id"
type="string"
not-null="false"
/>
<property
name="CO"
column="pr_co"
type="string"
not-null="false"
/>
<property
name="LegalSub"
column="pr_legal_sub"
type="string"
not-null="false"
/>
<property
name="LotNumber"
column="pr_lot_number"
type="string"
not-null="false"
/>
<property
name="BlockSquare"
column="pr_block_square"
type="string"
not-null="false"
/>
<property
name="Section"
column="pr_section"
type="string"
not-null="false"
/>
<property
name="TotalTax"
column="pr_total_tax"
type="big_decimal"
not-null="false"
/>
<property
name="GrossLivingArea"
column="pr_gross_living_area"
type="java.lang.Short"
not-null="false"
/>
</component>
<many-to-one
name="ExteriorType"
column="exterior_type"
class="ExteriorType"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="CurrentPIF"
column="current_pif_id"
class="PIF"
not-null="false"
>
</many-to-one>
<many-to-one
name="ParkingType"
column="parking_type"
class="ParkingType"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="SidingType"
column="siding_type"
class="SidingType"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="Type"
column="property_type"
class="PropertyType"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="AssociationType"
column="association_type"
class="AssociationType"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="BasementType"
column="basement_type"
class="BasementType"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="BasementFinish"
column="basement_finish"
class="BasementFinish"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="ACType"
column="air_conditioning_type"
class="AirConditioningType"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="GarageType"
column="garage_type"
class="GarageType"
not-null="false"
lazy="false"
>
</many-to-one>
<many-to-one
name="Address"
column="address_id"
class="Address"
not-null="true"
lazy="false"
fetch="join"
>
</many-to-one>
<many-to-one
name="Market"
column="market_id"
class="Market"
not-null="false"
lazy="false"
>
</many-to-one>
</class>
</hibernate-mapping>
Generated SQL (from the HQL above... notice that the property stuff isn't in there)
Code:
select pif0_.pif_id as pif1_, pif0_.reservation_nbr as reservat2_63_, pif0_.created_date as created3_63_, pif0_.list_price as list4_63_, pif0_.list_date as list5_63_, pif0_.accept_price as accept6_63_, pif0_.acquisition_amount as acquisit7_63_, pif0_.mortgage_remaining_balance as mortgage8_63_, pif0_.is_current_in_payments as is9_63_, pif0_.repair_roof as repair10_63_, pif0_.repair_interior_paint as repair11_63_, pif0_.repair_exterior_paint as repair12_63_, pif0_.repair_carpet as repair13_63_, pif0_.repair_furnace as repair14_63_, pif0_.repair_ac as repair15_63_, pif0_.repair_windows as repair16_63_, pif0_.repair_cabinets as repair17_63_, pif0_.repair_stove as repair18_63_, pif0_.repair_refridgerator as repair19_63_, pif0_.other_repairs as other20_63_, pif0_.other_improvements as other21_63_, pif0_.vacate_date as vacate22_63_, pif0_.outsale_amount as outsale23_63_, pif0_.outsale_deposit_amount as outsale24_63_, pif0_.property_id as property25_63_, pif0_.contact_person_id as contact26_63_, pif0_.current_owner_id as current27_63_, pif0_.current_valuation_id as current28_63_, pif0_.requested_valuation_id as requested29_63_, pif0_.current_acquisition_offer_id as current30_63_, pif0_.current_outsale_offer_id as current31_63_, pif0_.termite_inspection_id as termite32_63_, pif0_.hvac_inspection_id as hvac33_63_, pif0_.mold_inspection_id as mold34_63_, pif0_.home_inspection_id as home35_63_, pif0_.drive_by_inspection_id as drive36_63_, pif0_.occupancy_type as occupancy37_63_, pif0_.property_condition as property38_63_, pif0_.mls_status as mls39_63_, pif0_.property_stage as property40_63_, pif0_.property_sub_stage as property41_63_, pif0_.lease_type as lease42_63_, pif0_.sell_reason as sell43_63_, pif0_.advertising_type as adverti44_63_, pif0_.dead_deal_reason as dead45_63_ from pif pif0_ where (pif0_.property_stage not in ('DDL' , 'SLD')) and (pif0_.current_owner_id=? or pif0_.pif_id in (select activity1_.pif_id from activity activity1_ where activity1_.assigned_to_id=?)) limit ?
Any ideas or hints would be greatly appreciated. Thank you very much!