my dog learned polymorphism
The moose likes Object Relational Mapping and the fly likes Hibernate native sql problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate native sql problem" Watch "Hibernate native sql problem" New topic

Hibernate native sql problem

Sapana R Patel

Joined: Nov 18, 2010
Posts: 4
I am trying to execute sql native query using hibernate 3.3.2.GA.

I have following query.

Mapping file for Dept class is

But why I get following error?

Hibernate: SELECT dept1.deptId as deptId1_0_, dept1.version as version1_0_, dept1.deptName as deptName1_0_, dept2.deptId as deptId1_1_, dept2.version as version1_1_, dept2.deptName as deptName1_1_ FROM Dept d1, Dept d2 WHERE d1.deptId = d2.deptId
20:43:41,109 WARN JDBCExceptionReporter:100 - SQL Error: 904, SQLState: 42000
20:43:41,109 ERROR JDBCExceptionReporter:101 - ORA-00904: "DEPT2"."DEPTNAME": invalid identifier

Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(
at org.hibernate.exception.JDBCExceptionHelper.convert(
at org.hibernate.loader.Loader.doList(
at org.hibernate.loader.Loader.listIgnoreQueryCache(
at org.hibernate.loader.Loader.list(
at org.hibernate.loader.custom.CustomLoader.list(
at org.hibernate.impl.SessionImpl.listCustomQuery(
at org.hibernate.impl.AbstractSessionImpl.list(
at org.hibernate.impl.SQLQueryImpl.list(
at com.test.test.Test1.main(
Caused by: java.sql.SQLException: ORA-00904: "DEPT2"."DEPTNAME": invalid identifier

at oracle.jdbc.dbaccess.DBError.throwSqlException(
at oracle.jdbc.ttc7.TTIoer.processError(
at oracle.jdbc.ttc7.Oall7.receive(
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(
at org.hibernate.jdbc.AbstractBatcher.getResultSet(
at org.hibernate.loader.Loader.getResultSet(
at org.hibernate.loader.Loader.doQuery(
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(
at org.hibernate.loader.Loader.doList(
... 7 more

Hibernate documentation says following.

The {cat.*} and {mother.*} notation used above is a shorthand for "all properties". Alternatively,
you can list the columns explicitly, but even in this case Hibernate injects the SQL column aliases
for each property.

But it is not working as documented in hibernate reference.
kri shan
Ranch Hand

Joined: Apr 08, 2004
Posts: 1428
ORA-00904: "DEPT2"."DEPTNAME": invalid identifier

check your column name in the mapping file/model class.
Christian Dillinger
Ranch Hand

Joined: Jul 20, 2009
Posts: 195
Mapping seems to be okay, check the table instead. Is the column there?

{dept1.*} <==> d1? Try: "{d1.*}... FROM Dept d1"

BTW: Don't crosspost... I read your post in the hibernate forum some minutes ago.
I agree. Here's the link:
subject: Hibernate native sql problem
It's not a secret anymore!