Hibernate Community • View topic - ProjectionList and Criteria produces incorrect sql

View unanswered posts | View active topics

Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]

ProjectionList and Criteria produces incorrect sql

Page 1 of 1

[ 6 posts ]

Previous topic | Next topic

Author

Message

hamster

Post subject: ProjectionList and Criteria produces incorrect sql

Posted: Wed Apr 27, 2005 3:20 pm

Newbie

Joined: Wed Apr 27, 2005 3:09 pm
Posts: 5

Hibernate version:
3.0.1

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

Code:

ProjectionList projectionList = Projections.projectionList();
projectionList.add(Property.forName("id"), "id");
projectionList.add(Property.forName("fileName"), "fileName");
projectionList.add(Property.forName("type"), "type");

Criteria criteria = getSession().createCriteria(Attachment.class)
  .add(Restrictions.eq("individual", individual))
  .add(Restrictions.eq("type", type));
criteria.addOrder(Order.asc("fileName"));

criteria.setProjection(projectionList);
criteria.setResultTransformer(new AliasToBeanResultTransformer(Attachment.class));

return criteria.list();

Description
I'm trying to use ProjectionList to avoid retrieving BLOB columns.

Code below produces something like this:

Code:

select this_.id as y0_, this_.file_name as y1_, this_.type as y2_ 
  from attachments this_
 where this_.individual_id = ? 
   and y2_ = ?;

Which gives nice ORA error:
Caused by: java.sql.SQLException: ORA-00904: "Y2_": invalid identifier.

When I comment :

Code:

// projectionList.add(Property.forName("type"), "type");

Then I get:

Code:

select this_.id as y0_, this_.file_name as y1_
  from attachments this_
 where this_.individual_id = ? 
   and this_.type = ?;

But I would like attachment.type to be retrieved.

Full stack trace of any exception that occurs:
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:1525)
at org.hibernate.loader.Loader.list(Loader.java:1505)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1256)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299)
[..]
Caused by: java.sql.SQLException: ORA-00904: "Y2_": 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.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:118)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1204)
at org.hibernate.loader.Loader.doQuery(Loader.java:368)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:208)
at org.hibernate.loader.Loader.doList(Loader.java:1522)

Name and version of the database you are using:
Oracle9

Code:

Top

Post subject:

Posted: Tue Jun 07, 2005 12:17 pm

Newbie

Joined: Thu Apr 07, 2005 7:21 pm
Posts: 12

Did you ever get this resolved? It looks like I am having a similar problem, except I'm using the MySQL database. Check out the subject "Projections not working with property that is primary key".

thanks,
M

Top

hamster

Post subject: solution

Posted: Tue Jun 07, 2005 12:32 pm

Newbie

Joined: Wed Apr 27, 2005 3:09 pm
Posts: 5

No I did not have to to resolve this problem properly. I've simply commented property which I have used in restriction. It is not good solutuion for everyone but here I've been using Restriction.eq() so it works fine.

Top

rrangine

Post subject: Re: ProjectionList and Criteria produces incorrect sql

Posted: Thu Feb 18, 2010 1:20 pm

Newbie

Joined: Thu Oct 01, 2009 4:02 pm
Posts: 4

Hi,

I got stuck with the same problem. Did you get it resolved ?. If so, could you please post the solution for it.

Thanks,
Raja Chandra Rangineni

Top

jkakkad

Post subject: Re: ProjectionList and Criteria produces incorrect sql

Posted: Thu Jun 16, 2011 9:21 am

Newbie

Joined: Thu Jun 16, 2011 9:17 am
Posts: 1

hi this problem is because of hibernate version
In older version, if we use same alias name as property name and we use in where clause it generates problem like this

so try the same code with version above 3.6, this error will not come again

Thank You

Top

faowuser

Post subject: Re: ProjectionList and Criteria produces incorrect sql

Posted: Fri Jan 20, 2012 7:59 am

Newbie

Joined: Mon May 09, 2011 1:06 am
Posts: 4

I still face similar problem
The generated HQL uses an alias which is not mapped to any table and thus oracle gives error
Caused by: java.sql.SQLException: ORA-00904: "MPRODUCT1_"."NAME_DBCOL": invalid identifier

the HQL where clause is:
where this_.Product_Code=? and mproduct1_.Name_DBCol=?

mproduct1_ is not mapped to any db table and hence oracle throws the exception.

Top

Page 1 of 1

[ 6 posts ]

Board index » Projects » Search, Validator, Shards

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum