I am trying to search for an object by creating HQL. I need to search for an attribute which is mapped using a <join> tag. however, while generating the SQL from HQL, it does not include the joined table in the query...

i get an invalid identifier exception from database

Hibernate version: 3.0.5

Mapping documents:

<hibernate-mapping>
<class name="Resource" table="RES_RESOURCE_TB">
<id name="id">
<generator class="assigned">
</generator>
</id>
<property name="entitySpecificationId" type="java.lang.String"
column="ID_RES_SPECIFICATION_TB"/>
<bag name="persistedEntityCharacteristicList" lazy="false" cascade="all">
<key column="ID_RES_RESOURCE_TB" foreign-key="RESOURCE_CHARACTERISTIC_FK01">
</key>
<one-to-many entity-name="ResourceCharacteristic"/>
</bag>
</class>

<class name="AbstractEntityCharacteristic"
entity-name="ResourceCharacteristic"
table="RES_CHARACTERISTICS_TB">
<id name="id">
<generator class="assigned">
</generator>
</id>
<discriminator column="CHARACTERISTIC_TYPE" type="string" not-null="true"/>
<subclass name="ResourceInboundLinkCharacteristic" discriminator-value="ResourceInboundLinkCharacteristic">
<join table="RES_INBOUNDLINK_CHAR_TB" fetch="select">
<key column="ID" foreign-key="RES_INBOUNDLINK_CHAR_FK01" />
<property name="linkedResourceId" type="java.lang.String" not-null="true" column="RESOURCE_REFERENCE"/>
<!-- Dummy mapping for linked Resource -->
<many-to-one name="dummyLinkedResource" class="Resource" column="RESOURCE_REFERENCE" />
</join>
</subclass>
</class>
</hibernate-mapping>

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

Create the below HQL and fire list()

Select distinct resource from Resource as resource
left join resource.persistedEntityCharacteristicList as resourceInboundLinkCharacteristic0
left join resourceInboundLinkCharacteristic0.dummyLinkedResource as linkedResource0
left join linkedResource0.identifier as linkedResourceIdentifier0
where (resourceInboundLinkCharacteristic0.specificationCharacteristicId='2c95a9360a5b023d010a5b026f3c008e')
and linkedResourceIdentifier0.value like '%XYZ%'

Full stack trace of any exception that occurs:

Stack Trace:



java.sql.SQLException: ORA-00904: "PERSISTEDE1_4_"."RESOURCE_REFERENCE": invalid identifier



at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)

at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)

at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)

at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:877)

at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2513)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2857)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)

at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:80)

at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)

at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)

at org.hibernate.loader.Loader.doQuery(Loader.java:391)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)

at org.hibernate.loader.Loader.doList(Loader.java:1593)

at org.hibernate.loader.Loader.list(Loader.java:1577)

at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:890)

at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)

at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)

Name and version of the database you are using:

Oracle 9i

The generated SQL (show_sql=true):

SELECT
distinct resource0_.ID as ID,
resource0_.ID_RES_SPECIFICATION_TB as ID10_10_
FROM
RES_RESOURCE_TB resource0_
left outer join RES_CHARACTERISTICS_TB persistede1_ on resource0_.ID=persistede1_.ID_RES_RESOURCE_TB
left outer join RES_RESOURCE_TB resource2_ on persistede1_4_.RESOURCE_REFERENCE=resource2_.ID
left outer join RES_IDENTIFIER_TB resourceid3_ on resource2_.ID_RES_IDENTIFIER_TB=resourceid3_.ID
WHERE
(persistede1_.ID_RES_SPEC_CHARACTERISTICS_TB='2c95a9360a5b023d010a5b026f3c008e' ) and
(resourceid3_.VALUE like '%XYZ%' )

Debug level Hibernate log excerpt:

none