I'm having a problem with the Hibernate Criteria API generating invalid SQL. I have now tried this both using createCriteria to navigate to the sub-table I need as well as adding aliases:
Code:
Criteria criteria = this.getSession().createCriteria(Product.class);
criteria.createAlias("awards", "awards");
criteria.createAlias("awards.award", "awardsaward");
// [... more aliases ...]
criteria.add(Restrictions.like("awardsaward.awardName", "%"));
List products = criteria.list();
The generated SQL looks like
Code:
select
this_.PRODUCT_ID as PRODUCT1_6_12_,
this_2_.SOLUTION_OWNER_ID as SOLUTION2_6_12_,
this_2_.CONTENT_PROVIDER_ID as CONTENT3_6_12_,
this_2_.PARENT_PRODUCT_ID as PARENT4_6_12_,
this_2_.PRODUCT_TITLE as PRODUCT5_6_12_,
this_2_.PRODUCT_SUB_TITLE as PRODUCT6_6_12_,
this_2_.SERIES as SERIES6_12_,
this_2_.COPYRIGHT as COPYRIGHT6_12_,
this_2_.PRODUCT_TYPE as PRODUCT9_6_12_,
this_2_.CATEGORY_ID as CATEGORY10_6_12_,
this_2_.PUBLISHING_YEAR as PUBLISHING11_6_12_,
this_2_.PRODUCTION_YEAR as PRODUCTION12_6_12_,
this_2_.LANGUAGE as LANGUAGE6_12_,
this_2_.LIVE_START_DATE as LIVE14_6_12_,
this_2_.LIVE_END_DATE as LIVE15_6_12_,
this_2_.SHORT_COMMENT as SHORT16_6_12_,
this_2_.SUMMARY as SUMMARY6_12_,
this_2_.KEYWORDS as KEYWORDS6_12_,
this_2_.ACTIVE as ACTIVE6_12_,
this_2_.STATUS as STATUS6_12_,
this_2_.CRE_USR as CRE21_6_12_,
this_2_.CRE_DAT as CRE22_6_12_,
this_2_.MOD_USR as MOD23_6_12_,
this_2_.MOD_DAT as MOD24_6_12_,
this_2_.BO_REMARK as BO25_6_12_,
this_2_.BO_PRIORITY as BO26_6_12_,
this_2_.BO_ERROR as BO27_6_12_,
this_2_.OVERWRITE_PUBLISHER_B2BINCL as OVERWRITE28_6_12_,
this_2_.DELETED as DELETED6_12_,
this_2_.IS_WATERMARKED as IS30_6_12_,
this_1_.FSK as FSK9_12_,
this_1_.RECOMMENDED_AGE as RECOMMEN3_9_12_,
this_1_.GEMA_MINUTES as GEMA4_9_12_,
this_1_.GEMA_MINUTES_PERCENTAGE as GEMA5_9_12_,
this_1_.GEMA_PUBLISHER_ID as GEMA6_9_12_,
this_1_.TOTAL_DURATION as TOTAL7_9_12_,
this_1_.EXTERNAL_LISTENING_SAMPLE as EXTERNAL8_9_12_,
this_1_.CRE_USR as CRE9_9_12_,
this_1_.CRE_DAT as CRE10_9_12_,
this_1_.MOD_USR as MOD11_9_12_,
this_1_.MOD_DAT as MOD12_9_12_,
this_1_.ISBN_10 as ISBN13_9_12_,
this_1_.ISBN_13 as ISBN14_9_12_,
this_1_.CDS_TOTAL_NUMBER as CDS15_9_12_,
this_1_.PRODUCT_TOTAL_TRACK_NUMBER as PRODUCT16_9_12_,
this_1_.DOWNLOAD_ACTIVE as DOWNLOAD17_9_12_,
this_.SERIES_ID as SERIES2_10_12_,
this_.TRANSLATION_ID as TRANSLAT3_10_12_,
this_.ORIGINAL_ID as ORIGINAL4_10_12_,
this_.EXTERNAL_PRODUCT_ID as EXTERNAL5_10_12_,
this_.TITLE_PREFIX as TITLE6_10_12_,
this_.TITLE_WITHOUT_PREFIX as TITLE7_10_12_,
this_.RIGHTS_COUNTRY as RIGHTS8_10_12_,
this_.RIGHTS_MARKET as RIGHTS9_10_12_,
this_.TYPE as TYPE10_12_,
this_.PRODUCTION_FORMAT as PRODUCTION11_10_12_,
this_.IS_SIMULTANEOUS as IS12_10_12_,
this_.IS_PROMO as IS13_10_12_,
this_.IS_EXPLICIT as IS14_10_12_,
this_.SPECIFIC_INTERNAL as SPECIFIC15_10_12_,
this_.COMPLETE_DATE as COMPLETE16_10_12_,
this_.AGE_GROUP_ID as AGE17_10_12_,
this_.AGE_CATEGORY_ID as AGE18_10_12_,
this_.CRE_USR as CRE19_10_12_,
this_.CRE_DAT as CRE20_10_12_,
this_.MOD_USR as MOD21_10_12_,
this_.MOD_DAT as MOD22_10_12_,
contentpro2_.CONTENT_PROVIDER_ID as CONTENT1_26_0_,
contentpro2_.SOLUTION_OWNER_ID as SOLUTION2_26_0_,
contentpro2_.SUPPLY_MANAGER_ID as SUPPLY3_26_0_,
contentpro2_.NAME as NAME26_0_,
contentpro2_.DESCRIPTION as DESCRIPT5_26_0_,
contentpro2_.CRE_USR as CRE6_26_0_,
contentpro2_.CRE_DAT as CRE7_26_0_,
contentpro2_.MOD_USR as MOD8_26_0_,
contentpro2_.MOD_DAT as MOD9_26_0_,
contentpro2_.ACTIVE as ACTIVE26_0_,
contentpro2_1_.CONTACT1 as CONTACT2_84_0_,
contentpro2_1_.CONTACT2 as CONTACT3_84_0_,
contentpro2_1_.BANKING as BANKING84_0_,
contentpro2_1_.CONTRACT_ID as CONTRACT5_84_0_,
contentpro2_1_.INCOME_SPLIT_CALC as INCOME6_84_0_,
contentpro2_1_.MINPERWP as MINPERWP84_0_,
contentpro2_1_.NETPERCCP as NETPERCCP84_0_,
contentpro2_1_.LPPERCCP as LPPERCCP84_0_,
contentpro2_1_.CRE_USR as CRE10_84_0_,
contentpro2_1_.CRE_DAT as CRE11_84_0_,
contentpro2_1_.MOD_USR as MOD12_84_0_,
contentpro2_1_.MOD_DAT as MOD13_84_0_,
contentpro2_1_.ADDRESS as ADDRESS84_0_,
contentpro2_1_.WEBSITE as WEBSITE84_0_,
case
when contentpro2_1_.CONTENT_PROVIDER_ID is not null then 1
when contentpro2_.CONTENT_PROVIDER_ID is not null then 0
end as clazz_0_,
productper4_.PRODUCT_ID as PRODUCT1_50_1_,
productper4_.PERFORMER_ID as PERFORMER2_50_1_,
productper4_.SORTBY as SORTBY50_1_,
productper4_.CRE_USR as CRE4_50_1_,
productper4_.CRE_DAT as CRE5_50_1_,
productper4_.MOD_USR as MOD6_50_1_,
productper4_.MOD_DAT as MOD7_50_1_,
bolindaser3_.SERIES_ID as SERIES1_43_2_,
bolindaser3_.SOLUTION_OWNER_ID as SOLUTION2_43_2_,
bolindaser3_.NAME as NAME43_2_,
bolindaser3_.SERIES_NUMBER as SERIES4_43_2_,
bolindaser3_.CRE_USR as CRE5_43_2_,
bolindaser3_.CRE_DAT as CRE6_43_2_,
bolindaser3_.MOD_USR as MOD7_43_2_,
bolindaser3_.MOD_DAT as MOD8_43_2_,
original7_.ORIGINAL_ID as ORIGINAL1_33_3_,
original7_.COPYRIGHT_HOLDER_ID as COPYRIGHT2_33_3_,
original7_.FILE_FORMAT_ID as FILE3_33_3_,
original7_.PRODUCT_ID as PRODUCT4_33_3_,
original7_.COPYRIGHT_YEAR as COPYRIGHT5_33_3_,
original7_.LANGUAGE as LANGUAGE33_3_,
original7_.CRE_USR as CRE7_33_3_,
original7_.CRE_DAT as CRE8_33_3_,
original7_.MOD_USR as MOD9_33_3_,
original7_.MOD_DAT as MOD10_33_3_,
originalco18_.COPYRIGHT_HOLDER_ID as COPYRIGHT1_48_4_,
originalco18_.SOLUTION_OWNER_ID as SOLUTION2_48_4_,
originalco18_.COPYRIGHT_HOLDER_NAME as COPYRIGHT3_48_4_,
originalco18_.CRE_USR as CRE4_48_4_,
originalco18_.CRE_DAT as CRE5_48_4_,
originalco18_.MOD_USR as MOD6_48_4_,
originalco18_.MOD_DAT as MOD7_48_4_,
originalfi8_.FILE_FORMAT_ID as FILE1_62_5_,
originalfi8_.FORMAT_DESCRIPTION as FORMAT2_62_5_,
originalfi8_.EXTENSION as EXTENSION62_5_,
originalfi8_.CRE_USR as CRE4_62_5_,
originalfi8_.CRE_DAT as CRE5_62_5_,
originalfi8_.MOD_USR as MOD6_62_5_,
originalfi8_.MOD_DAT as MOD7_62_5_,
publishers26_.PRODUCT_ID as PRODUCT1_,
publishers1_.PUBLISHER_ID as PUBLISHER2_,
publishers1_.PUBLISHER_ID as PUBLISHER1_64_6_,
publishers1_.SOLUTION_OWNER_ID as SOLUTION2_64_6_,
publishers1_.PUBLISHER_IMPRINT as PUBLISHER3_64_6_,
bestseller28_.PRODUCT_ID as PRODUCT1_,
bestseller13_.BESTSELLER_ID as BESTSELLER2_,
bestseller13_.BESTSELLER_ID as BESTSELLER1_42_7_,
bestseller13_.SOLUTION_OWNER_ID as SOLUTION2_42_7_,
bestseller13_.BESTSELLER_NAME as BESTSELLER3_42_7_,
bestseller13_.CRE_USR as CRE4_42_7_,
bestseller13_.CRE_DAT as CRE5_42_7_,
bestseller13_.MOD_USR as MOD6_42_7_,
bestseller13_.MOD_DAT as MOD7_42_7_,
selections30_.PRODUCT_ID as PRODUCT1_,
selections14_.SELECTION_ID as SELECTION2_,
selections14_.SELECTION_ID as SELECTION1_41_8_,
selections14_.SELECTION_NAME as SELECTION2_41_8_,
selections14_.SOLUTION_OWNER_ID as SOLUTION3_41_8_,
selections14_.CRE_USR as CRE4_41_8_,
selections14_.CRE_DAT as CRE5_41_8_,
selections14_.MOD_USR as MOD6_41_8_,
selections14_.MOD_DAT as MOD7_41_8_,
awards9_.PRODUCT_ID as PRODUCT1_40_9_,
awards9_.AWARD_ID as AWARD2_40_9_,
awards9_.CATEGORY_ID as CATEGORY3_40_9_,
awards9_.POSITION_ID as POSITION4_40_9_,
awards9_.YEAR as YEAR40_9_,
awards9_.CRE_USR as CRE6_40_9_,
awards9_.CRE_DAT as CRE7_40_9_,
awards9_.MOD_USR as MOD8_40_9_,
awards9_.MOD_DAT as MOD9_40_9_,
publicatio15_.PUBLICATION_ID as PUBLICAT1_46_10_,
publicatio15_.PRODUCT_ID as PRODUCT2_46_10_,
publicatio15_.PUBLICATION_DATE as PUBLICAT3_46_10_,
publicatio15_.DISTRIBUTIONTERRITORY_ID as DISTRIBU4_46_10_,
publicatio15_.CRE_USR as CRE5_46_10_,
publicatio15_.CRE_DAT as CRE6_46_10_,
publicatio15_.MOD_USR as MOD7_46_10_,
publicatio15_.MOD_DAT as MOD8_46_10_,
publicatio16_.DISTRIBUTIONTERRITORY_ID as DISTRIBU1_121_11_,
publicatio16_.SUPPLYMANAGER_ID as SUPPLYMA2_121_11_,
publicatio16_.NAME as NAME121_11_,
publicatio16_.CRE_USR as CRE4_121_11_,
publicatio16_.CRE_DAT as CRE5_121_11_,
publicatio16_.MOD_USR as MOD6_121_11_,
publicatio16_.MOD_DAT as MOD7_121_11_
from
TOWLDCS.BOL_PRODUCT this_,
TOWLDCS.WLDCS_PRODUCT_AUDIO this_1_,
TOWLDCS.WLDCS_PRODUCT this_2_,
TOWLDCS.WLDCS_CONTENT_PROVIDER contentpro2_,
TOWLDCS.BOL_CONTENT_PROVIDER contentpro2_1_,
TOWLDCS.WLDCS_PRODUCT_PERFORMER productper4_,
TOWLDCS.BOL_SERIES bolindaser3_,
TOWLDCS.BOL_ORIGINAL original7_,
TOWLDCS.BOL_COPYRIGHT_HOLDER originalco18_,
TOWLDCS.WLDCS_FILE_FORMAT originalfi8_,
TOWLDCS.BOL_PRODUCT_PUBLISHER publishers26_,
TOWLDCS.BOL_PUBLISHER publishers1_,
TOWLDCS.BOL_PRODUCT_BESTSELLER bestseller28_,
TOWLDCS.BOL_BESTSELLER bestseller13_,
TOWLDCS.BOL_PRODUCT_SELECTION selections30_,
TOWLDCS.BOL_SELECTION selections14_,
TOWLDCS.BOL_PRODUCT_AWARDS awards9_,
TOWLDCS.BOL_PUBLICATION publicatio15_,
TOWLDCS.BOL_DISTRIBUTIONTERRITORY publicatio16_
where
this_2_.CONTENT_PROVIDER_ID=contentpro2_.CONTENT_PROVIDER_ID
and contentpro2_.CONTENT_PROVIDER_ID=contentpro2_1_.CONTENT_PROVIDER_ID(+)
and this_.PRODUCT_ID=productper4_.PRODUCT_ID
and this_.SERIES_ID=bolindaser3_.SERIES_ID
and this_.ORIGINAL_ID=original7_.ORIGINAL_ID
and original7_.COPYRIGHT_HOLDER_ID=originalco18_.COPYRIGHT_HOLDER_ID
and original7_.FILE_FORMAT_ID=originalfi8_.FILE_FORMAT_ID
and this_.PRODUCT_ID=publishers26_.PRODUCT_ID
and publishers26_.PUBLISHER_ID=publishers1_.PUBLISHER_ID
and this_.PRODUCT_ID=bestseller28_.PRODUCT_ID
and bestseller28_.BESTSELLER_ID=bestseller13_.BESTSELLER_ID
and this_.PRODUCT_ID=selections30_.PRODUCT_ID
and selections30_.SELECTION_ID=selections14_.SELECTION_ID
and this_.PRODUCT_ID=awards9_.PRODUCT_ID
and this_.PRODUCT_ID=publicatio15_.PRODUCT_ID
and publicatio15_.DISTRIBUTIONTERRITORY_ID=publicatio16_.DISTRIBUTIONTERRITORY_ID
and this_.PRODUCT_ID=this_1_.PRODUCT_ID
and this_.PRODUCT_ID=this_2_.PRODUCT_ID
and awardsawar10_.AWARD_NAME like ?
However, awardsawar10_ is not defined in the SQL's FROM clause! Thus, the following error occurs:
Code:
2009-04-16 12:59:34,811 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 904, SQLState: 42000
2009-04-16 12:59:34,811 [main] ERROR org.hibernate.util.JDBCExceptionReporter - ORA-00904: "AWARDSAWAR10_"."AWARD_NAME": invalid identifier
As I mentioned, the exact same thing happens when using .createCriteria to "walk" to the awards.award table directly.
Other properties in sub-tables do or do not work seemingly at random. I am at a complete loss as to why this isn't working. Any ideas?
Login
FAQ
Search