Hi All ,
I am using Hibernate 3.0 . The back end database is Oracle 9.0 i
I have problem wih Criteria Query .
The scenario is , i want to fetch records from table which has composite key . The class corresponding to this table is InvoiceCharge.
I have mapping documents like thiis .....................


InvoiceCharge ---------
<hibernate-mapping package="qc.qbid.invoice.model.bo">
<class
name="InvoiceCharge"
table="INVOICE_CHARGE" lazy="false"
>
<composite-id name="invoiceChargeId" class="InvoiceChargePK">
<key-many-to-one
name="invoiceBillingDevice"
class="qc.qbid.invoice.model.bo.InvoiceBillingDevice"
column="INVOICE_BILLING_DEVICE_ID"

/>
<key-many-to-one
name="chargeType"
class="qc.qbid.admin.model.bo.ChargeType"
column="CHARGE_TYPE_ID"

/>
</composite-id>


</class>
</hibernate-mapping>



InvoiceBillingDevice :-----------------
<hibernate-mapping package="qc.qbid.invoice.model.bo">
<class
name="InvoiceBillingDevice"
table="INVOICE_BILLING_DEVICE" lazy="false"
>

<id
name="invoiceBillingDeviceId"
column="INVOICE_BILLING_DEVICE_ID"
type="integer"

>
<generator class="increment"/>
</id>

<many-to-one
name="invoice"
column="INVOICE_ID"
class = "qc.qbid.invoice.model.bo.Invoice"
lazy="true"
/>

</class>
</hibernate-mapping>



Invoice:-----------

hibernate-mapping>
<class
name="qc.qbid.invoice.model.bo.Invoice"
table="INVOICE" lazy="false"
>

<id
name="invoiceId"
column="INVOICE_ID"
type="java.lang.Long"
length="22"
>
<generator class="increment"/>
</id>

</class>
</hibernate-mapping>


The query which i have written is

final List returnList = session.createCriteria(InvoiceCharge.class)
.createAlias("invoiceChargeId.invoiceBillingDevice",
"invoiceBillingDevice").createAlias("invoiceBillingDevice.invoice",
"invoice"). add(Expression.eq("invoice.invoiceId",
invoiceId)).list();
session.close();



but this is giving me exception . The stack trace is

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1502)
at org.hibernate.loader.Loader.list(Loader.java:1482)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1246)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299)
at org.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:141)
at qc.qbid.invoice.model.dao.impl.InvoiceDAOHibImpl.getInvoiceChargeList(Unknown Source)
at qc.qbid.invoice.model.service.impl.InvoiceManagerImpl.getInvoiceBillingDeviceChargeList(Unknown Source)
at qc.qbid.invoice.view.bean.InvoiceDetailsMBean.getParseList(Unknown Source)
... 99 more
Caused by: java.sql.SQLException: ORA-00904: "INVOICEBIL1_"."INVOICE_ID": 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:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:107)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1183)
at org.hibernate.loader.Loader.doQuery(Loader.java:363)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:203)
at org.hibernate.loader.Loader.doList(Loader.java:1499)
... 107 more



The generated SQL is :-----------


select this_.INVOICE_BILLING_DEVICE_ID as INVOICE1_1_,
this_.CHARGE_TYPE_ID as CHARGE2_1_,
from INVOICE_CHARGE this_ where invoicebil1_.INVOICE_ID = ?



Please tell me , am i doing anything wrong here ? Is my criteria query wrong ?


Vaibhav

The addCriteria should probably look like :

add(Expression.eq("id.invoiceBillingDevice.invoice.invoiceId",
invoiceId))

See also thread: http://forum.hibernate.org/viewtopic.php?t=945956

Hi ,
Thanks for your reply . I tried with your suggestion.


The addCriteria should probably look like :

add(Expression.eq("id.invoiceBillingDevice.invoice.invoiceId",
invoiceId))


But ........... it failed . The stack trace is

Caused by: org.hibernate.QueryException: could not resolve property: id.invoiceBillingDevice.invoice.invoiceId of: qc.qbid.invoice.model.bo.InvoiceCharge
at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
at org.hibernate.persister.entity.AbstractPropertyMapping.toColumns(AbstractPropertyMapping.java:63)
at org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEntityPropertyMapping.java:33)
at org.hibernate.persister.entity.BasicEntityPersister.toColumns(BasicEntityPersister.java:1104)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumns(CriteriaQueryTranslator.java:402)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumnsUsingProjection(CriteriaQueryTranslator.java:368)
at org.hibernate.criterion.SimpleExpression.toSqlString(SimpleExpression.java:42)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:313)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:92)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1228)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299)
at org.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:141)
at qc.qbid.invoice.model.dao.impl.InvoiceDAOHibImpl.getInvoiceChargeList(Unknown Source)
at qc.qbid.invoice.model.service.impl.InvoiceManagerImpl.getInvoiceBillingDeviceChargeList(Unknown Source)
at qc.qbid.invoice.view.bean.InvoiceDetailsMBean.getParseList(Unknown Source)
... 99 more


So , to reolve this exception i added alias . So my query was like this


final List returnList = session.createCriteria(InvoiceCharge.class,"invoiceCharge")
.createCriteria("invoiceCharge.invoiceChargeId.invoiceBillingDevice")
.setFetchMode("billingDevice",
FetchMode.SELECT).setFetchMode("invoice", FetchMode.SELECT).createAlias("invoiceCharge.id.invoiceBillingDevice","invoiceBillingDevice").
add(Expression.eq("invoiceBillingDevice.invoice.invoiceId",
invoiceId)).list();


But still it went wrong . The stack trace is :

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1502)
at org.hibernate.loader.Loader.list(Loader.java:1482)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1246)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299)
at org.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:141)
at qc.qbid.invoice.model.dao.impl.InvoiceDAOHibImpl.getInvoiceChargeList(Unknown Source)
at qc.qbid.invoice.model.service.impl.InvoiceManagerImpl.getInvoiceBillingDeviceChargeList(Unknown Source)
at qc.qbid.invoice.view.bean.InvoiceDetailsMBean.getParseList(Unknown Source)
... 99 more
Caused by: java.sql.SQLException: ORA-00904: "INVOICEBIL2_"."INVOICE_ID": 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:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:107)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1183)
at org.hibernate.loader.Loader.doQuery(Loader.java:363)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:203)
at org.hibernate.loader.Loader.doList(Loader.java:1499)


Generated SQL is
select this_.INVOICE_BILLING_DEVICE_ID as INVOICE1_1_,
this_.CHARGE_TYPE_ID as CHARGE2_1_

from INVOICE_CHARGE this_
where invoicebil2_.INVOICE_ID = ?


So i think , there is problem with Alias. Anybody knows how should i define Alias in this case ?

Thanks in advance ,
Vaibhav

Criteria API doesn't support aliases with more than one level deep.

See HHH-235 and HHH-631