Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: ProjectionList and Criteria produces incorrect sql
PostPosted: 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
 Profile  
 
 Post subject:
PostPosted: 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
 Profile  
 
 Post subject: solution
PostPosted: 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
 Profile  
 
 Post subject: Re: ProjectionList and Criteria produces incorrect sql
PostPosted: 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
 Profile  
 
 Post subject: Re: ProjectionList and Criteria produces incorrect sql
PostPosted: 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
 Profile  
 
 Post subject: Re: ProjectionList and Criteria produces incorrect sql
PostPosted: 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
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.