Hi!
I have a problem using native sql (the CONNECT BY PRIOR statement of plsql) with hibernate. I want to get a list of categories which are joined (fetched) with their associated articleLanguage objects (1:n). I tried to use namedQueries like the example in the reference (
http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html), but somehow it doesnt work... I always get the errror "No column name found for property [id.language.languageId] for alias [catLanguage]"
Hibernate version:
Hibernate 3.2.1
Mapping documents:
category.hbm.xml
Code:
<hibernate-mapping>
<class name="com.poi.egh.webshops.model.Category" table="CATEGORY" >
<id name="categoryId" type="java.lang.Long">
<column name="CATEGORY_ID" precision="10" scale="0" />
<generator class="sequence" />
</id>
<many-to-one name="parent" class="com.poi.egh.webshops.model.Category" fetch="select">
<column name="PARENT_ID" precision="10" scale="0" />
</many-to-one>
....
<set name="categoryLanguages" inverse="true" cascade="save-update">
<key>
<column name="CATEGORY_ID" precision="10" scale="0" not-null="true" />
</key>
<one-to-many class="com.poi.egh.webshops.model.CategoryLanguage" />
</set>
.....
</class>
<resultset name="categoryCategoryLanguageParentCategory">
<return alias="category" class="com.poi.egh.webshops.model.Category"/>
<return-join alias="catLanguage" property="category.categoryLanguages"/>
<return-join alias="parent" property="category.parent" />
<return-join alias="parLanguage" property="parent.categoryLanguages" />
</resultset>
<sql-query name="categoryForSelectionWithArticles" resultset-ref="categoryCategoryLanguageParentCategory">
SELECT c.category_id AS {category.categoryId},
c.parent_id AS {parent.categoryId},
cl.language_id AS {catLanguage.id.language.languageId},
cl.category_id AS {catLanguage.id.category},
cl.description AS {catLanguage.description},
pcl.language_id AS {parLanguage.id.language.languageId},
pcl.category_id AS {parLanguage.id.category},
pcl.description AS {parLanguage.description}
FROM CATEGORY c
JOIN CATEGORY p ON p.category_id = c.parent_id
JOIN CATEGORY_LANGUAGE cl ON cl.category_id = c.category_id
JOIN CATEGORY_LANGUAGE pcl ON pcl.category_id = p.category_id
WHERE c.category_id IN (
SELECT tmp.category_id FROM ARTICLE a, ARTICLE_CATEGORY_PRESENTATION acp , (SELECT category_id,parent_id FROM CATEGORY
CONNECT BY category_id= PRIOR parent_id ) tmp
WHERE acp.category_id = tmp.category_id
AND cl.language_id = :language
AND pcl.language_id = :language
<!-- AND a.sortimentKZ=:sortimentkz -->
AND a.article_id=acp.article_id)
ORDER BY c.parent_id
</sql-query>
</hibernate-mapping>
categoryLanguage.hbm.xml
Code:
<hibernate-mapping>
<class name="com.poi.egh.webshops.model.CategoryLanguage" table="CATEGORY_LANGUAGE" >
<composite-id name="id" class="com.poi.egh.webshops.model.CategoryLanguageId">
<key-many-to-one name="category" class="com.poi.egh.webshops.model.Category">
<column name="CATEGORY_ID" precision="10" scale="0" />
</key-many-to-one>
<key-many-to-one name="language" class="com.poi.egh.webshops.model.Language">
<column name="LANGUAGE_ID" length="12" />
</key-many-to-one>
</composite-id>
<property name="description" type="java.lang.String">
<column name="DESCRIPTION" length="200" />
</property>
....
</class>
</hibernate-mapping>
15:33:00.096 ERROR! [main] org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:362) >59> Error in named query: categoryForSelectionWithArticles
org.hibernate.QueryException: No column name found for property [id.language.languageId] for alias [catLanguage] [SELECT c.category_id AS {category.categoryId},
c.parent_id AS {parent.categoryId},
cl.language_id AS {catLanguage.id.language.languageId},
cl.category_id AS {catLanguage.id.category},
cl.description AS {catLanguage.description},
pcl.language_id AS {parLanguage.id.language.languageId},
pcl.category_id AS {parLanguage.id.category},
pcl.description AS {parLanguage.description}
FROM CATEGORY c
JOIN CATEGORY p ON p.category_id = c.parent_id
JOIN CATEGORY_LANGUAGE cl ON cl.category_id = c.category_id
JOIN CATEGORY_LANGUAGE pcl ON pcl.category_id = p.category_id
WHERE c.category_id IN (
SELECT tmp.category_id FROM ARTICLE a, ARTICLE_CATEGORY_PRESENTATION acp , (SELECT category_id,parent_id FROM CATEGORY
CONNECT BY category_id= PRIOR parent_id ) tmp
WHERE acp.category_id = tmp.category_id
AND cl.language_id = :language
AND pcl.language_id = :language
AND a.article_id=acp.article_id)
ORDER BY c.parent_id]
at org.hibernate.loader.custom.sql.SQLQueryParser.resolveCollectionProperties(SQLQueryParser.java:152)
at org.hibernate.loader.custom.sql.SQLQueryParser.substituteBrackets(SQLQueryParser.java:98)
at org.hibernate.loader.custom.sql.SQLQueryParser.process(SQLQueryParser.java:51)
at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:110)
at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:43)
at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:114)
at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:444)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:351)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1291)
Name and version of the database you are using:
Oracle9
greets Klaus
Login
FAQ
Search