Hi,

I'm getting exception when running trying to fetch some records where collection being fetched.

Following tables are involved in the mapping.

table drs_product many-to-many drs_prod_parents
table drs_category many-to-many drs_prod_parents

basically drs_prod_parents is an intermediate table between drs_product and drs_category having composite id (product_id,category_id)

external_hotel has product_id as PK and FK to drs_product (id).

any help would be appreciated.

Thanks & Regards,
Irfan


Hibernate version:2.16

Mapping documents:
<!--DefaultCHMCategory.hbm.xml--><BR>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class name="com.irfan.chm.trawler.instance.DefaultCHMCategory" table="drs_category">
<id name="id" column="id">
<generator class="com.irfan.chm.trawler.persistence.hibernate.V2HibernateIDGenerator">
<param name="table">drs_category</param>
<param name="maxCachedIDs">100</param>
</generator>
</id>
<many-to-one name="parentCategory" cascade="none" outer-join="false">
<column name="parent_category" not-null="false"></column>
</many-to-one>
<set name="childCategories" cascade="all-delete-orphan" inverse="true">
<key column="parent_category"/>
<one-to-many class="com.irfan.chm.trawler.instance.DefaultCHMCategory"/>
</set>
<set name="prodCategories" cascade=" all-delete-orphan" inverse=" true" outer-join="false">
<key>
<column name="category_id" not-null="true"></column>
</key>
<one-to-many class="com.irfan.chm.trawler.instance.DefaultCHMProductCategory"/>
</set>
</class>
</hibernate-mapping>
<BR>
<!--DefaultCHMProduct.hbm.xml--><BR>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class name="com.irfan.chm.trawler.instance.DefaultCHMProduct" table="drs_product">
<id name="id" column="id">
<generator class="com.irfan.chm.trawler.persistence.hibernate.V2HibernateIDGenerator">
<param name="table">drs_product</param>
<param name="maxCachedIDs">100</param>
</generator>
</id>
<property name="typeId" column="type_id" type="int"></property>
<property name="operatingEntity" column="operating_entity"></property>
<set name="prodCategories" inverse="true" lazy="true">
<key>
<column name="product_id" not-null="true"></column>
</key>
<one-to-many class="com.irfan.chm.trawler.instance.DefaultCHMProductCategory"/>
</set>
<joined-subclass name="com.irfan.chm.trawler.instance.DefaultCHMExternalHotel" table="external_hotel">
<key column="product_id"/>
<property name="propertyId" column="property_id"/>
</joined-subclass>
</class>
</hibernate-mapping><Br>
<!--DefaulCHMProductCategory.hbm.xml--><BR>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class name="com.irfan.chm.trawler.instance.DefaultCHMProductCategory" table="drs_prod_parents">
<composite-id name="id" class="com.irfan.chm.trawler.instance.DefaultCHMProductCategory$Id">
<key-property name="categoryId" column="category_id"/>
<key-property name="productId" column="product_id"/>
</composite-id>
<many-to-one name="product" insert="false" update="false" column="product_id"/>
<many-to-one name="category" insert="false" update="false" column="category_id"/>
</class>
</hibernate-mapping>



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

Session hibernateSession = openSession();

Query q=hibernateSession.createQuery("select cat from DefaultCHMCategory as cat left join fetch
cat.prodCategories as pc join fetch pc.product as prod");
return new HashSet(q.list());



Full stack trace of any exception that occurs:(util.JDBCExceptionReporter 20 ) SQL Warning: 0, SQLState: 01I01
(util.JDBCExceptionReporter 28 ) Database has transactions
(util.JDBCExceptionReporter 20 ) SQL Warning: 0, SQLState: 01I04
(util.JDBCExceptionReporter 28 ) Database selected
com.irfan.sybil.persistence.PersistenceException: Didn't get any product
at com.irfan.chm.trawler.persistence.hibernate.HibernateCHMTrawlerPersister.findConnectivityHotelsByCategoryID(HibernateCHMTrawlerPersister.java:63)
at com.irfan.chm.trawler.persistence.hibernate.HibernateCHMTrawlerPersisterTest.testIrfan(HibernateCHMTrawlerPersisterTest.java:62)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:166)
at junit.framework.TestCase.runBare(TestCase.java:140)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:131)
at junit.framework.TestSuite.runTest(TestSuite.java:173)
at junit.framework.TestSuite.run(TestSuite.java:168)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
Caused by: net.sf.hibernate.QueryException: unexpected token: as [ select prod from DefaultCHMProduct as prod left join fetch prod.prodCategories as pc join fetch pc.category as c]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:94)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:294)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1562)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1533)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.irfan.chm.trawler.persistence.hibernate.HibernateCHMTrawlerPersister.findConnectivityHotelsByCategoryID(HibernateCHMTrawlerPersister.java:61)
... 16 more

Name and version of the database you are using:Informix9.4

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt: