Michael, thanks for the very quick reply.
The createAlias() api doc states:
"Join an association, assigning an alias to the joined entity "
If I use createAlias() on any other given many-to-one it creates an inner join. I can then traverse my associations down multiple levels.
I want to join an association off of my primary key object and then query some property off of it.
as an example.
Code:
Criteria criteria = session.createCriteria(Price.class);
criteria = criteria.createAlias(Price.SOURCE_LOCATION_PROPERTY, Price.SOURCE_LOCATION_PROPERTY);
criteria = criteria.createAlias(Price.SOURCE_LOCATION_PROPERTY + "." + Location.JURISDICTION_BY_PROV_ST_JURISD_CODE_PROPERTY, Location.JURISDICTION_BY_PROV_ST_JURISD_CODE_PROPERTY);
criteria = criteria.createAlias(Price.ITEM_PROPERTY, Price.ITEM_PROPERTY);
criteria = criteria.createAlias(Price.ITEM_PROPERTY + "." + Item.SHIP_MODE_PROPERTY, Price.ITEM_PROPERTY + "bleh");
criteria.add(Expression.eq(Location.JURISDICTION_BY_PROV_ST_JURISD_CODE_PROPERTY + "." + Jurisdiction.JURISDICTION_CODE_PROPERTY, "AB"));
criteria.add(Expression.ilike(Price.SOURCE_LOCATION_PROPERTY + "." + Location.ADDR_LINE1_PROPERTY, "Calgary", MatchMode.START));
criteria.add(Expression.ilike(Price.ITEM_PROPERTY + "." + Item.DESCRIPTION_LONG_PROPERTY, "barge", MatchMode.START));
criteria.setFirstResult(0);
criteria.setMaxResults(1);
List list = criteria.list();
Produces the following sql
select * from ( select this.PRICE_ID as PRICE_ID24_, this.DESCRIPTION as DESCRIPT2_24_, this.EXCHANGE_RATE_TYPE as EXCHANGE3_24_, this.PRICE_GROUP as PRICE_GR4_24_, this.MILEAGE_COMP as MILEAGE_5_24_, this.INACTIVE_DATE as INACTIVE6_24_, this.UPDATE_USER as UPDATE_U7_24_, this.UPDATE_DATE as UPDATE_D8_24_, this.UPPER_DESCRIPTION as UPPER_DE9_24_, this.SEPARATE_ON_INVOICE_IND as SEPARAT10_24_, this.INDICATE_USAGE_ON_INVOICE as INDICAT11_24_, this.DESCRIPTION_PART2 as DESCRIP12_24_, this.SURCHARGE_PRICE_ID as SURCHAR13_24_, this.PRICE_SUBGROUP as PRICE_S14_24_, this.NOTES_ID as NOTES_ID24_, this.PRICE_CAT_CODE as PRICE_C16_24_, this.CURRENCY_CODE as CURRENC17_24_, this.UOM_CODE as UOM_CODE24_, this.DELIVERY_ADDR_ID as DELIVER19_24_, this.SOURCE_ADDR_ID as SOURCE_20_24_, this.CARRIER_ID as CARRIER_ID24_, this.ITEM_CODE as ITEM_CODE24_, pricecateg1_.PRICE_CAT_CODE as PRICE_CA1_0_, pricecateg1_.DESCRIPTION as DESCRIPT2_0_, pricecateg1_.TYPE as TYPE0_, pricecateg1_.FORMULA_FLG as FORMULA_4_0_, pricecateg1_.UPDATE_USER as UPDATE_U5_0_, pricecateg1_.UPDATE_DATE as UPDATE_D6_0_, pricecateg1_.SURCHARGE_FLG as SURCHARG7_0_, pricecateg1_.TRADING_GROUP_FLG as TRADING_8_0_, pricecateg1_.NUM_CALC_DECIMALS as NUM_CALC9_0_, pricecateg1_.LOCKING_METHOD_TYPE as LOCKING10_0_, pricecateg1_.GENERATE_COST_FOR_BU_UNIT_IND as GENERAT11_0_, pricecateg1_.SURCHARGE_CAT_CODE as SURCHAR12_0_, pricecateg1_.INCLUDE_IN_NET_PRICE_IND as INCLUDE13_0_, pricecateg1_.FUTURE_PRICE_FLG as FUTURE_14_0_, pricecateg1_.FREQUENCY_CODE as FREQUEN15_0_, pricecateg1_.EXCLUDE_IN_NGL_RPT_IND as EXCLUDE16_0_, pricecateg1_.RISK_TYPE as RISK_TYPE0_, pricecateg2_.TYPE as TYPE1_, pricecateg2_.DESCRIPTION as DESCRIPT2_1_, pricecateg2_.RISK_CATEGORY as RISK_CAT3_1_, pricecateg2_.REPORTING_ORDER as REPORTIN4_1_, pricecateg2_.INACTIVE_DATE as INACTIVE5_1_, currency3_.CURRENCY_CODE as CURRENCY1_2_, currency3_.DESCRIPTION as DESCRIPT2_2_, currency3_.INACTIVE_DATE as INACTIVE3_2_, currency3_.UPDATE_USER as UPDATE_U4_2_, currency3_.UPDATE_DATE as UPDATE_D5_2_, currency3_.CURRENCY_SYMBOL as CURRENCY6_2_, currency3_.DISPLAY_FORMAT as DISPLAY_7_2_, currency3_.COUNTRY_CODE as COUNTRY_8_2_, jurisdicti4_.JURISDICTION_CODE as JURISDIC1_3_, jurisdicti4_.NAME as NAME3_, jurisdicti4_.TYPE as TYPE3_, jurisdicti4_.SENIOR_JURISDICTION_CODE as SENIOR_J4_3_, jurisdicti4_.UPDATE_USER as UPDATE_U5_3_, jurisdicti4_.UPDATE_DATE as UPDATE_D6_3_, jurisdicti4_.PHONE_EDIT_MASK as PHONE_ED7_3_, jurisdicti4_.DFLT_CURRENCY_CODE as DFLT_CUR8_3_, jurisdicti4_.STATE_GEOCODE as STATE_GE9_3_, jurisdicti4_.COUNTY_GEOCODE as COUNTY_10_3_, jurisdicti4_.CITY_GEOCODE as CITY_GE11_3_, jurisdicti4_.HOME_RULE_FLG as HOME_RU12_3_, jurisdicti4_.DEFAULT_VOL_UOM as DEFAULT13_3_, jurisdicti4_.DEFAULT_ENERGY_UOM as DEFAULT14_3_, uom5_.UOM_CODE as UOM_CODE4_, uom5_.DESCRIPTION as DESCRIPT2_4_, uom5_.TYPE as TYPE4_, uom5_.INACTIVE_DATE as INACTIVE4_4_, uom5_.UPDATE_USER as UPDATE_U5_4_, uom5_.UPDATE_DATE as UPDATE_D6_4_, uom5_.SI as SI4_, uom5_.EDIT_MASK as EDIT_MASK4_, uom5_.SHORT_DESCRIPTION as SHORT_DE9_4_, location6_.ADDR_ID as ADDR_ID5_, location6_.ADDR_LINE1 as ADDR_LINE15_, location6_.ADDR_LINE2 as ADDR_LINE25_, location6_.ADDR_LINE3 as ADDR_LINE35_, location6_.CITY as CITY5_, location6_.POST_ZIP_CODE as POST_ZIP6_5_, location6_.X_COORD as X_COORD5_, location6_.Y_COORD as Y_COORD5_, location6_.Z_COORD as Z_COORD5_, location6_.DESCRIPTION as DESCRIP10_5_, location6_.EDI_ACCOUNT as EDI_ACC11_5_, location6_.INACTIVE_DATE as INACTIV12_5_, location6_.UPDATE_USER as UPDATE_13_5_, location6_.UPDATE_DATE as UPDATE_14_5_, location6_.TIME_ZONE as TIME_ZONE5_, location6_.CUTOFF_TIME as CUTOFF_16_5_, location6_.FACILITY_BALANCE_DEFAULT as FACILIT17_5_, location6_.OPERATOR_ID as OPERATO18_5_, location6_.DFLT_UOM_CODE as DFLT_UO19_5_, location6_.DFLT_TEMP_UOM_CODE as DFLT_TE20_5_, location6_.RECEIPT_VOLUME_TYPE as RECEIPT21_5_, location6_.OPERATOR_REF_REQ_IND as OPERATO22_5_, location6_.DIRECT_SHIP_CITY as DIRECT_23_5_, location6_.BANK_IDENTIFIER_NUMBER as BANK_ID24_5_, location6_.BANK_ACCOUNT_NUMBER as BANK_AC25_5_, location6_.SEQ_TICKET_REF_FLG as SEQ_TIC26_5_, location6_.SEQ_TICKET_REF_PREFIX as SEQ_TIC27_5_, location6_.NOTES_ID as NOTES_ID5_, location6_.ADDR_LINE4 as ADDR_LINE45_, location6_.SUBTYPE as SUBTYPE5_, location6_.DISPLAY_ON_MAP_FLG as DISPLAY31_5_, location6_.TERTIARY_JURISD_CODE as TERTIAR32_5_, location6_.PROV_ST_JURISD_CODE as PROV_ST33_5_, location6_.COUNTRY_JURISD_CODE as COUNTRY34_5_, location6_.TYPE as TYPE5_, jurisdicti7_.JURISDICTION_CODE as JURISDIC1_6_, jurisdicti7_.NAME as NAME6_, jurisdicti7_.TYPE as TYPE6_, jurisdicti7_.SENIOR_JURISDICTION_CODE as SENIOR_J4_6_, jurisdicti7_.UPDATE_USER as UPDATE_U5_6_, jurisdicti7_.UPDATE_DATE as UPDATE_D6_6_, jurisdicti7_.PHONE_EDIT_MASK as PHONE_ED7_6_, jurisdicti7_.DFLT_CURRENCY_CODE as DFLT_CUR8_6_, jurisdicti7_.STATE_GEOCODE as STATE_GE9_6_, jurisdicti7_.COUNTY_GEOCODE as COUNTY_10_6_, jurisdicti7_.CITY_GEOCODE as CITY_GE11_6_, jurisdicti7_.HOME_RULE_FLG as HOME_RU12_6_, jurisdicti7_.DEFAULT_VOL_UOM as DEFAULT13_6_, jurisdicti7_.DEFAULT_ENERGY_UOM as DEFAULT14_6_, jurisdicti8_.JURISDICTION_CODE as JURISDIC1_7_, jurisdicti8_.NAME as NAME7_, jurisdicti8_.TYPE as TYPE7_, jurisdicti8_.SENIOR_JURISDICTION_CODE as SENIOR_J4_7_, jurisdicti8_.UPDATE_USER as UPDATE_U5_7_, jurisdicti8_.UPDATE_DATE as UPDATE_D6_7_, jurisdicti8_.PHONE_EDIT_MASK as PHONE_ED7_7_, jurisdicti8_.DFLT_CURRENCY_CODE as DFLT_CUR8_7_, jurisdicti8_.STATE_GEOCODE as STATE_GE9_7_, jurisdicti8_.COUNTY_GEOCODE as COUNTY_10_7_, jurisdicti8_.CITY_GEOCODE as CITY_GE11_7_, jurisdicti8_.HOME_RULE_FLG as HOME_RU12_7_, jurisdicti8_.DEFAULT_VOL_UOM as DEFAULT13_7_, jurisdicti8_.DEFAULT_ENERGY_UOM as DEFAULT14_7_, jurisdicti9_.JURISDICTION_CODE as JURISDIC1_8_, jurisdicti9_.NAME as NAME8_, jurisdicti9_.TYPE as TYPE8_, jurisdicti9_.SENIOR_JURISDICTION_CODE as SENIOR_J4_8_, jurisdicti9_.UPDATE_USER as UPDATE_U5_8_, jurisdicti9_.UPDATE_DATE as UPDATE_D6_8_, jurisdicti9_.PHONE_EDIT_MASK as PHONE_ED7_8_, jurisdicti9_.DFLT_CURRENCY_CODE as DFLT_CUR8_8_, jurisdicti9_.STATE_GEOCODE as STATE_GE9_8_, jurisdicti9_.COUNTY_GEOCODE as COUNTY_10_8_, jurisdicti9_.CITY_GEOCODE as CITY_GE11_8_, jurisdicti9_.HOME_RULE_FLG as HOME_RU12_8_, jurisdicti9_.DEFAULT_VOL_UOM as DEFAULT13_8_, jurisdicti9_.DEFAULT_ENERGY_UOM as DEFAULT14_8_, locationty10_.TYPE as TYPE9_, locationty10_.DESCRIPTION as DESCRIPT2_9_, locationty10_.LONG_DESCRIPTION as LONG_DES3_9_, locationty10_.DOI_ALLOWED_FLG as DOI_ALLO4_9_, locationty10_.DFLT_DISPLAY_ON_MAP_FLG as DFLT_DIS5_9_, locationty10_.SUBTYPES_AVAILABLE_FLG as SUBTYPES6_9_, locationty10_.INACTIVE_DATE as INACTIVE7_9_, sourceLocation.ADDR_ID as ADDR_ID10_, sourceLocation.ADDR_LINE1 as ADDR_LINE110_, sourceLocation.ADDR_LINE2 as ADDR_LINE210_, sourceLocation.ADDR_LINE3 as ADDR_LINE310_, sourceLocation.CITY as CITY10_, sourceLocation.POST_ZIP_CODE as POST_ZIP6_10_, sourceLocation.X_COORD as X_COORD10_, sourceLocation.Y_COORD as Y_COORD10_, sourceLocation.Z_COORD as Z_COORD10_, sourceLocation.DESCRIPTION as DESCRIP10_10_, sourceLocation.EDI_ACCOUNT as EDI_ACC11_10_, sourceLocation.INACTIVE_DATE as INACTIV12_10_, sourceLocation.UPDATE_USER as UPDATE_13_10_, sourceLocation.UPDATE_DATE as UPDATE_14_10_, sourceLocation.TIME_ZONE as TIME_ZONE10_, sourceLocation.CUTOFF_TIME as CUTOFF_16_10_, sourceLocation.FACILITY_BALANCE_DEFAULT as FACILIT17_10_, sourceLocation.OPERATOR_ID as OPERATO18_10_, sourceLocation.DFLT_UOM_CODE as DFLT_UO19_10_, sourceLocation.DFLT_TEMP_UOM_CODE as DFLT_TE20_10_, sourceLocation.RECEIPT_VOLUME_TYPE as RECEIPT21_10_, sourceLocation.OPERATOR_REF_REQ_IND as OPERATO22_10_, sourceLocation.DIRECT_SHIP_CITY as DIRECT_23_10_, sourceLocation.BANK_IDENTIFIER_NUMBER as BANK_ID24_10_, sourceLocation.BANK_ACCOUNT_NUMBER as BANK_AC25_10_, sourceLocation.SEQ_TICKET_REF_FLG as SEQ_TIC26_10_, sourceLocation.SEQ_TICKET_REF_PREFIX as SEQ_TIC27_10_, sourceLocation.NOTES_ID as NOTES_ID10_, sourceLocation.ADDR_LINE4 as ADDR_LINE410_, sourceLocation.SUBTYPE as SUBTYPE10_, sourceLocation.DISPLAY_ON_MAP_FLG as DISPLAY31_10_, sourceLocation.TERTIARY_JURISD_CODE as TERTIAR32_10_, sourceLocation.PROV_ST_JURISD_CODE as PROV_ST33_10_, sourceLocation.COUNTRY_JURISD_CODE as COUNTRY34_10_, sourceLocation.TYPE as TYPE10_, jurisdicti12_.JURISDICTION_CODE as JURISDIC1_11_, jurisdicti12_.NAME as NAME11_, jurisdicti12_.TYPE as TYPE11_, jurisdicti12_.SENIOR_JURISDICTION_CODE as SENIOR_J4_11_, jurisdicti12_.UPDATE_USER as UPDATE_U5_11_, jurisdicti12_.UPDATE_DATE as UPDATE_D6_11_, jurisdicti12_.PHONE_EDIT_MASK as PHONE_ED7_11_, jurisdicti12_.DFLT_CURRENCY_CODE as DFLT_CUR8_11_, jurisdicti12_.STATE_GEOCODE as STATE_GE9_11_, jurisdicti12_.COUNTY_GEOCODE as COUNTY_10_11_, jurisdicti12_.CITY_GEOCODE as CITY_GE11_11_, jurisdicti12_.HOME_RULE_FLG as HOME_RU12_11_, jurisdicti12_.DEFAULT_VOL_UOM as DEFAULT13_11_, jurisdicti12_.DEFAULT_ENERGY_UOM as DEFAULT14_11_, jurisdictionByProvStJurisdCode.JURISDICTION_CODE as JURISDIC1_12_, jurisdictionByProvStJurisdCode.NAME as NAME12_, jurisdictionByProvStJurisdCode.TYPE as TYPE12_, jurisdictionByProvStJurisdCode.SENIOR_JURISDICTION_CODE as SENIOR_J4_12_, jurisdictionByProvStJurisdCode.UPDATE_USER as UPDATE_U5_12_, jurisdictionByProvStJurisdCode.UPDATE_DATE as UPDATE_D6_12_, jurisdictionByProvStJurisdCode.PHONE_EDIT_MASK as PHONE_ED7_12_, jurisdictionByProvStJurisdCode.DFLT_CURRENCY_CODE as DFLT_CUR8_12_, jurisdictionByProvStJurisdCode.STATE_GEOCODE as STATE_GE9_12_, jurisdictionByProvStJurisdCode.COUNTY_GEOCODE as COUNTY_10_12_, jurisdictionByProvStJurisdCode.CITY_GEOCODE as CITY_GE11_12_, jurisdictionByProvStJurisdCode.HOME_RULE_FLG as HOME_RU12_12_, jurisdictionByProvStJurisdCode.DEFAULT_VOL_UOM as DEFAULT13_12_, jurisdictionByProvStJurisdCode.DEFAULT_ENERGY_UOM as DEFAULT14_12_, jurisdicti14_.JURISDICTION_CODE as JURISDIC1_13_, jurisdicti14_.NAME as NAME13_, jurisdicti14_.TYPE as TYPE13_, jurisdicti14_.SENIOR_JURISDICTION_CODE as SENIOR_J4_13_, jurisdicti14_.UPDATE_USER as UPDATE_U5_13_, jurisdicti14_.UPDATE_DATE as UPDATE_D6_13_, jurisdicti14_.PHONE_EDIT_MASK as PHONE_ED7_13_, jurisdicti14_.DFLT_CURRENCY_CODE as DFLT_CUR8_13_, jurisdicti14_.STATE_GEOCODE as STATE_GE9_13_, jurisdicti14_.COUNTY_GEOCODE as COUNTY_10_13_, jurisdicti14_.CITY_GEOCODE as CITY_GE11_13_, jurisdicti14_.HOME_RULE_FLG as HOME_RU12_13_, jurisdicti14_.DEFAULT_VOL_UOM as DEFAULT13_13_, jurisdicti14_.DEFAULT_ENERGY_UOM as DEFAULT14_13_, locationty15_.TYPE as TYPE14_, locationty15_.DESCRIPTION as DESCRIPT2_14_, locationty15_.LONG_DESCRIPTION as LONG_DES3_14_, locationty15_.DOI_ALLOWED_FLG as DOI_ALLO4_14_, locationty15_.DFLT_DISPLAY_ON_MAP_FLG as DFLT_DIS5_14_, locationty15_.SUBTYPES_AVAILABLE_FLG as SUBTYPES6_14_, locationty15_.INACTIVE_DATE as INACTIVE7_14_, stakeholde16_.STKHLDR_ID as STKHLDR_ID15_, stakeholde16_.TYPE as TYPE15_, stakeholde16_.NAME_LAST_ORG as NAME_LAS3_15_, stakeholde16_.NAME_FIRST_DEPT as NAME_FIR4_15_, stakeholde16_.NAME_OTHER as NAME_OTHER15_, stakeholde16_.NOTES_ID as NOTES_ID15_, stakeholde16_.INACTIVE_DATE as INACTIVE7_15_, stakeholde16_.UPDATE_USER as UPDATE_U8_15_, stakeholde16_.UPDATE_DATE as UPDATE_D9_15_, stakeholde16_.CREATE_DATE as CREATE_10_15_, stakeholde16_.LEGAL_ENTITY_FLG as LEGAL_E11_15_, stakeholde16_.LEGAL_ENTITY_ID as LEGAL_E12_15_, stakeholde16_.UNALLOCATED_PAYMENT_TOTAL as UNALLOC13_15_, stakeholde16_.LAST_PAYMENT_ID as LAST_PA14_15_, stakeholde16_.LAST_PAYMENT_DATE as LAST_PA15_15_, stakeholde16_.NON_RESIDENT_FLG as NON_RES16_15_, item.ITEM_CODE as ITEM_CODE16_, item.DESCRIPTION_LONG as DESCRIPT2_16_, item.DESCRIPTION_SHORT as DESCRIPT3_16_, item.LOCATION_CONTROL_FLG as LOCATION4_16_, item.DATE_AVAILABLE as DATE_AVA5_16_, item.INACTIVE_DATE as INACTIVE6_16_, item.UPDATE_USER as UPDATE_U7_16_, item.UPDATE_DATE as UPDATE_D8_16_, item.TEMP_CORRECTION_FACTOR as TEMP_COR9_16_, item.REPORT_SEQUENCE as REPORT_10_16_, item.DISPLAY_ORDER as DISPLAY11_16_, item.INVENTORY_KEPT_FLG as INVENTO12_16_, item.ITEM_GROUP_CODE as ITEM_GR13_16_, item.CLASS_CODE as CLASS_CODE16_, item.TYPE as TYPE16_, item.SHIP_MODE as SHIP_MODE16_, itemgroup18_.ITEM_GROUP_CODE as ITEM_GRO1_17_, itemgroup18_.DESCRIPTION as DESCRIPT2_17_, itemgroup18_.ALT_UOM_FLG as ALT_UOM_3_17_, itemgroup18_.MODIFIER_TYPE1 as MODIFIER4_17_, itemgroup18_.MODIFIER_TYPE2 as MODIFIER5_17_, itemgroup18_.MODIFIER_TYPE3 as MODIFIER6_17_, itemgroup18_.MOD1_LIST_FLG as MOD1_LIS7_17_, itemgroup18_.MOD2_LIST_FLG as MOD2_LIS8_17_, itemgroup18_.MOD3_LIST_FLG as MOD3_LIS9_17_, itemgroup18_.ACCT_AMORT_TYPE as ACCT_AM10_17_, itemgroup18_.ACCT_AMORT_RATE as ACCT_AM11_17_, itemgroup18_.INACTIVE_DATE as INACTIV12_17_, itemgroup18_.UPDATE_USER as UPDATE_13_17_, itemgroup18_.UPDATE_DATE as UPDATE_14_17_, itemgroup18_.REPORT_SEQUENCE as REPORT_15_17_, itemgroup18_.SCHEDULE_TYPE_CODE as SCHEDUL16_17_, itemgroup18_.FORECAST_ALLOC_METHOD_CDE as FORECAS17_17_, itemgroup18_.GRAVITY_COL_VISIBLE_IND as GRAVITY18_17_, itemgroup18_.USE_ORDERS_ON_RISK_RPTS as USE_ORD19_17_, itemgroup18_.VAR_HISTORY_PERIOD as VAR_HIS20_17_, itemgroup18_.UNIT_FLG as UNIT_FLG17_, itemgroup18_.TRAILER_FLG as TRAILER22_17_, itemgroup18_.M2M_HISTORY_PERIOD as M2M_HIS23_17_, itemgroup18_.VAR_HOLDING_PERIOD as VAR_HOL24_17_, itemgroup18_.VAR_SEASONALITY_FLG as VAR_SEA25_17_, itemgroup18_.VAR_START_LEAD as VAR_STA26_17_, itemgroup18_.VAR_END_LAG as VAR_END27_17_, itemgroup18_.ACT_VOL_REQUIRED_REG_CODE as ACT_VOL28_17_, itemgroup18_.ACT_VOL_DISALLOWED_REG_CODE as ACT_VOL29_17_, itemgroup18_.VOL_ENABLED_REG_CODE as VOL_ENA30_17_, itemgroup18_.VAR_HISTORY_INTERVAL_UOM_CODE as VAR_HIS31_17_, itemgroup18_.SKU_UOM_CODE as SKU_UOM32_17_, itemgroup18_.ACCT_CODE as ACCT_CODE17_, uom19_.UOM_CODE as UOM_CODE18_, uom19_.DESCRIPTION as DESCRIPT2_18_, uom19_.TYPE as TYPE18_, uom19_.INACTIVE_DATE as INACTIVE4_18_, uom19_.UPDATE_USER as UPDATE_U5_18_, uom19_.UPDATE_DATE as UPDATE_D6_18_, uom19_.SI as SI18_, uom19_.EDIT_MASK as EDIT_MASK18_, uom19_.SHORT_DESCRIPTION as SHORT_DE9_18_, uom20_.UOM_CODE as UOM_CODE19_, uom20_.DESCRIPTION as DESCRIPT2_19_, uom20_.TYPE as TYPE19_, uom20_.INACTIVE_DATE as INACTIVE4_19_, uom20_.UPDATE_USER as UPDATE_U5_19_, uom20_.UPDATE_DATE as UPDATE_D6_19_, uom20_.SI as SI19_, uom20_.EDIT_MASK as EDIT_MASK19_, uom20_.SHORT_DESCRIPTION as SHORT_DE9_19_, account21_.ACCT_CODE as ACCT_CODE20_, account21_.DESCRIPTION as DESCRIPT2_20_, account21_.TYPE as TYPE20_, account21_.SUBLEDGER_TYPE as SUBLEDGE4_20_, account21_.TAX_VALUE as TAX_VALUE20_, account21_.RESP_CENTER_REQ_FLG as RESP_CEN6_20_, account21_.ADDR_REQ_FLG as ADDR_REQ7_20_, account21_.STKHLDR_REQ_FLG as STKHLDR_8_20_, account21_.INVENTORY_REQ_FLG as INVENTOR9_20_, account21_.INACTIVE_DATE as INACTIV10_20_, account21_.UPDATE_DATE as UPDATE_11_20_, account21_.UPDATE_USER as UPDATE_12_20_, account21_.QTY_REQ_FLG as QTY_REQ13_20_, account21_.COST_CENTER_REQ_FLG as COST_CE14_20_, account21_.AGRMT_REQ_FLG as AGRMT_R15_20_, account21_.ACTIVITY_REQ_FLG as ACTIVIT16_20_, account21_.COST_ELMT_REQ_FLG as COST_EL17_20_, account21_.INCOME_STATEMENT_FLG as INCOME_18_20_, account21_.FS_SIGN_REVERSAL_FLG as FS_SIGN19_20_, account21_.FS_TOTAL_ACCT_CODE as FS_TOTA20_20_, account21_.FS_TOTAL_UNDERLINE as FS_TOTA21_20_, account21_.NEXT_CHEQUE_NUMBER as NEXT_CH22_20_, account21_.BUSINESS_UNIT_ID as BUSINES23_20_, account21_.SUPER_USER_ACCT_FLG as SUPER_U24_20_, account21_.SHOW_HEADING_FLG as SHOW_HE25_20_, account21_.ROLL_UP_LEVEL as ROLL_UP26_20_, account21_.GENERATE_PAYMENT_INTERFACE_FLG as GENERAT27_20_, account21_.BANK_IDENTIFIER_NUMBER as BANK_ID28_20_, account21_.BANK_ACCOUNT_NUMBER as BANK_AC29_20_, account21_.ITEM_REQ_FLG as ITEM_RE30_20_, account21_.CURRENCY_CODE as CURRENC31_20_, itemclass22_.CLASS_CODE as CLASS_CODE21_, itemclass22_.DESCRIPTION as DESCRIPT2_21_, itemclass22_.TYPE as TYPE21_, itemclass22_.INACTIVE_DATE as INACTIVE4_21_, itemclass22_.UPDATE_USER as UPDATE_U5_21_, itemclass22_.UPDATE_DATE as UPDATE_D6_21_, itemtype23_.TYPE as TYPE22_, itemtype23_.INVENTORY_BALANCE_FLG as INVENTOR2_22_, itemtype23_.DESCRIPTION as DESCRIPT3_22_, itemtype23_.CREATE_DATE as CREATE_D4_22_, itemtype23_.CREATE_USER as CREATE_U5_22_, itembleh.SHIP_MODE as SHIP_MODE23_, itembleh.DESCRIPTION as DESCRIPT2_23_, itembleh.UOM_CODE as UOM_CODE23_, itembleh.LAYCAN_ENABLED_FLG as LAYCAN_E4_23_ from PRICE_H this left outer join PRICE_CATEGORY pricecateg1_ on this.PRICE_CAT_CODE=pricecateg1_.PRICE_CAT_CODE left outer join PRICE_CATEGORY_RISK_TYPE pricecateg2_ on pricecateg1_.RISK_TYPE=pricecateg2_.TYPE left outer join CURRENCY_H currency3_ on this.CURRENCY_CODE=currency3_.CURRENCY_CODE left outer join JURISDICTION jurisdicti4_ on currency3_.COUNTRY_CODE=jurisdicti4_.JURISDICTION_CODE left outer join UOM uom5_ on this.UOM_CODE=uom5_.UOM_CODE left outer join ADDRESS location6_ on this.DELIVERY_ADDR_ID=location6_.ADDR_ID left outer join JURISDICTION jurisdicti7_ on location6_.TERTIARY_JURISD_CODE=jurisdicti7_.JURISDICTION_CODE left outer join JURISDICTION jurisdicti8_ on location6_.PROV_ST_JURISD_CODE=jurisdicti8_.JURISDICTION_CODE left outer join JURISDICTION jurisdicti9_ on location6_.COUNTRY_JURISD_CODE=jurisdicti9_.JURISDICTION_CODE left outer join ADDRESS_TYPE locationty10_ on location6_.TYPE=locationty10_.TYPE inner join ADDRESS sourceLocation on this.SOURCE_ADDR_ID=sourceLocation.ADDR_ID left outer join JURISDICTION jurisdicti12_ on sourceLocation.TERTIARY_JURISD_CODE=jurisdicti12_.JURISDICTION_CODE inner join JURISDICTION jurisdictionByProvStJurisdCode on sourceLocation.PROV_ST_JURISD_CODE=jurisdictionByProvStJurisdCode.JURISDICTION_CODE left outer join JURISDICTION jurisdicti14_ on sourceLocation.COUNTRY_JURISD_CODE=jurisdicti14_.JURISDICTION_CODE left outer join ADDRESS_TYPE locationty15_ on sourceLocation.TYPE=locationty15_.TYPE left outer join STKHLDR stakeholde16_ on this.CARRIER_ID=stakeholde16_.STKHLDR_ID inner join ITEM item on this.ITEM_CODE=item.ITEM_CODE left outer join ITEM_GROUP itemgroup18_ on item.ITEM_GROUP_CODE=itemgroup18_.ITEM_GROUP_CODE left outer join UOM uom19_ on itemgroup18_.VAR_HISTORY_INTERVAL_UOM_CODE=uom19_.UOM_CODE left outer join UOM uom20_ on itemgroup18_.SKU_UOM_CODE=uom20_.UOM_CODE left outer join ACCOUNT account21_ on itemgroup18_.ACCT_CODE=account21_.ACCT_CODE left outer join ITEM_CLASS itemclass22_ on item.CLASS_CODE=itemclass22_.CLASS_CODE left outer join ITEM_TYPE itemtype23_ on item.TYPE=itemtype23_.TYPE inner join SHIP_MODE itembleh on item.SHIP_MODE=itembleh.SHIP_MODE where jurisdictionByProvStJurisdCode.JURISDICTION_CODE=? and lower(sourceLocation.ADDR_LINE1) like ? and lower(item.DESCRIPTION_LONG) like ? ) where rownum <= ?
Notice that any of the aliases on the many-to-ones I create are translated into INNER JOINs. I was expecting I could do the same with a many-to-one that just happened to be part of my primary key but it seems to be ignored. Since I can create the inner join through HQL I can only guess that something is happening in the Criteria API or I am on dope. Right now that could be a 50/50 chance :)
Now, before anyone says anything, don't get me started on this legacy datamodel :P