HQL : LEFT OUTER JOIN

Question

Hi all,

I have two tables and respective classes in java.The mapping in the HBM.xml is as follows :

---

<class name="com.hcl.spring.db.sample.model.Parts" table="MARM_PARTS">
<id name="partNo" type="int" column="PART_NO">
<generator class="increment" />
</id>
<property name="imageId" column="IMAGE_ID"></property>
<property name="groupId" column="GROUP_ID"></property>

</class>

---

<class name="com.hcl.spring.db.sample.model.Reports" table="MARM_REPORTS">
<id name="reportId" type="int" column="REPORT_ID">
<generator class="increment" />
</id>
<property name="noteId" column="NOTE_ID"></property>
<property name="deptNo" column="DEPT_NO"></property>
<property name="badgeNo" column="BADGE_NO"></property>
<many-to-one
name="parts" class="com.hcl.spring.db.sample.model.Parts"
column="partNo"
update="false" />

The query in the HBM.xml is as follows :

<query name="selectReports">
from Reports as rep left join rep.parts as parts
</query>

---

I am getting the following exception while executing the code :

---

May 19, 2010 10:47:04 AM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 904, SQLState: 42000
May 19, 2010 10:47:04 AM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ORA-00904: "REPORTS0_"."PARTNO": invalid identifier

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at com.hcl.spring.db.sample.dao.ItemDAOImpl.loadItems(ItemDAOImpl.java:43)
at com.hcl.spring.db.sample.service.ItemServiceImpl.loadItems(ItemServiceImpl.java:20)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy3.loadItems(Unknown Source)
at com.hcl.spring.db.sample.Main.loadItems(Main.java:40)
at com.hcl.spring.db.sample.Main.main(Main.java:19)
Caused by: java.sql.SQLException: ORA-00904: "REPORTS0_"."PARTNO": invalid identifier

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1038)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1133)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 22 more

---

Do request your help on the same.

Answer 1

You have many-to-one relationship, which means many Reports are linked to 1 Part. So your model should look like this:

class Report {
    private Part part;
}

And query like this:

from Report r where r.part.imageId = :value

PS. Naming your classes in plural form is not a good idea.