Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Criteria Query Issue with Projection and restriction
PostPosted: Tue Jun 24, 2008 8:57 am 
Newbie

Joined: Tue Jun 24, 2008 8:35 am
Posts: 2
I'm getting this odd error whenever I try to add a restriction to a property with a projection set on it. Hibernate tries to query using the property alias and not with its name. Properties without a projection works fine.
I had to use a sqlRestriction with the collumn name hardcoded on it as a workaround...
Does anyone know what could it be?

Hibernate version: 3.2.6ga

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

Code:
      Criteria c = session.createCriteria(Customer.class).setProjection(
      Projections.projectionList()
         .add(Projections.property(Customer.PROP_ID), "id")
         .add(Projections.property(Customer.PROP_NAME), "name")
      )
      .add(Restrictions.like(Customer.PROP_EMAIL, email))
      .add(Restrictions.like(Customer.PROP_NAME, name));



The generated SQL (show_sql=true):
Code:
    select
        this_.ctm_id as y0_,
        this_.ctm_name as y1_
    from
        customers this_
    where
        this_.ctm_mail like ?
        and y1_ like ?




Full stack trace of any exception that occurs:
Code:
09:47:21,920  WARN JDBCExceptionReporter:77 - SQL Error: 1054, SQLState: 42S22
09:47:21,923 ERROR JDBCExceptionReporter:78 - Unknown column 'y1_' in 'where clause'
Exception in thread "main" 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:2216)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
   at org.hibernate.loader.Loader.list(Loader.java:2099)
   at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
   at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
   at br.com.webtraffic.bidmanagement.dao.CustomerListDAOImpl.main(CustomerListDAOImpl.java:43)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'y1_' in 'where clause'
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1403)
   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:2213)
   ... 6 more



Name and version of the database you are using:MySQL 5


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 24, 2008 9:56 am 
Expert
Expert

Joined: Tue May 13, 2008 3:42 pm
Posts: 919
Location: Toronto & Ajax Ontario www.hibernatemadeeasy.com
I'd wonder if the name field in the POJO is properly mapped to the corresponding column in the database.

As a test, I'd try to do the query without the projections. See if it works, and see what is returned for the name attribute.

_________________
Cameron McKenzie - Author of "Hibernate Made Easy" and "What is WebSphere?"
http://www.TheBookOnHibernate.com Check out my 'easy to follow' Hibernate & JPA Tutorials


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 24, 2008 10:01 am 
Newbie

Joined: Tue Jun 24, 2008 8:35 am
Posts: 2
Cameron McKenzie wrote:
I'd wonder if the name field in the POJO is properly mapped to the corresponding column in the database.

As a test, I'd try to do the query without the projections. See if it works, and see what is returned for the name attribute.


Hello Cameron,
I tried it already and everything worked fine. In the example I posted I query two properties, one with a projection and another without it, the projection-less property is queried in the right way but the one with a projection is not.
I made a bunch of tests and it looks like a Hibernate bug for me.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Nov 25, 2008 5:13 pm 
Newbie

Joined: Tue Oct 11, 2005 11:03 am
Posts: 4
I have the same issue. If I specify column alias with projection and then have restriction the wrong alias gets used in the query and it fails. Is it a bug or am I missing something?


Top
 Profile  
 
 Post subject: Re: Criteria Query Issue with Projection and restriction
PostPosted: Sat Jun 06, 2009 2:53 pm 
Newbie

Joined: Sat Jun 06, 2009 2:49 pm
Posts: 1
I had a very similar problem and was able to get past it by using "this.property-name" in my restriction.

I wrote up the details here: http://stackoverflow.com/questions/84644/hibernate-query-by-example-and-projections/960278#960278


Top
 Profile  
 
 Post subject: Re: Criteria Query Issue with Projection and restriction
PostPosted: Sun Nov 15, 2009 11:44 am 
Newbie

Joined: Sun Nov 15, 2009 10:33 am
Posts: 2
I ran into the same problem today.

The MySQL reference is clear that the use of aliases in the WHERE clause is not permitted in standard SQL.
http://dev.mysql.com/doc/refman/5.0/en/ ... alias.html

There is a bug report from 2005 here:
http://opensource.atlassian.com/project ... se/HHH-817

and patches have been submitted, but the problem has never been fixed - the bug is classified as 'minor', and is unassigned. Gavin King's only contribution was his initial comment that "This is perfectly reasonable SQL. Oracle should support it."

This is terrible.

My initial workaround was to use an SQL restriction, but now I've heard about "this.property" as an alternative (and checked that it works in my case), I'll favour that.

-- Justin Forder


Top
 Profile  
 
 Post subject: Re: Criteria Query Issue with Projection and restriction
PostPosted: Sun Nov 15, 2009 1:07 pm 
Newbie

Joined: Sun Nov 15, 2009 10:33 am
Posts: 2
By the way, even though the "this.property" approach is not documented in the Hibernate Reference Manual, it is mentioned in "Hibernate in Action"

Section 7.3.3:
Quote:
Properties of the root entity (Item) may be referred to without the qualifying alias or by using the alias "this".

and in "Java Persistence with Hibernate"

Section 15.1.2:
Quote:
Properties of the root entity of the criteria query (Item) may be referred to without the qualifying alias, or with the alias "this":


So I think it is safe to assume it won't break in future.

-- Justin Forder


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 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.