Hibernate Community • View topic - sqlGroupProjection and addOrder results in QueryException

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

sqlGroupProjection and addOrder results in QueryException

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

skausl

Post subject: sqlGroupProjection and addOrder results in QueryException

Posted: Sun Mar 04, 2007 5:22 am

Newbie

Joined: Fri Jun 24, 2005 4:36 am
Posts: 14
Location: Germany

I want to create a Hibernate Criteria for the following SQL statement:

Code:

select upper(left(name,1)) as x, count(*) from products group by x order by x desc;

I tried this code:

Code:

HibernateUtil.currentSession()
.createCriteria(ProductDB.class)
.setProjection(Projections.projectionList()
   .add(Projections.sqlGroupProjection(
      "upper(left(name, " + (prefix.length() + 1) + ")) as x", 
      "x", new String[] { "x" }, new Type[] { Hibernate.STRING }))
   .add(Projections.rowCount()))
.addOrder(Order.desc("x"));

But it's not working because:

Code:

org.hibernate.QueryException: could not resolve property: x of: Product

Why is Hibernate searching for x in product instead of using the alias x introduced by sqlGroupProjection?

There are work-arounds, for example sorting the result list with Collections.sort or using createSQLQuery.

But is it possible to do it with createCriteria?

Or is sqlGroupProjection not enough and we need something like a sqlGroupOrderProjection?

Top

ladoe00

Post subject: Re: sqlGroupProjection and addOrder results in QueryException

Posted: Tue Dec 08, 2009 11:05 am

Newbie

Joined: Tue Dec 08, 2009 10:38 am
Posts: 1

Hi,

I know this post is old, but I had the same problem and found a solution that I'd like to share ( augmenting the knowledge base :) ). The alias specified in your SQL is not being picked up by the Criteria engine, so whenever you try to reference it, the engine will fallback to your criteria root class (ProductDB.class) and hope it will find an attribute named "x".

To fix this, you need to make sure there is an alias existing within the Criteria scope. To make this happen, I wrapped my SQL group projection inside an alias projection:

Code:

Projections.alias(Projections.sqlGroupProjection(...), "youralias")

Your code snippet would become:

Code:

HibernateUtil.currentSession()
.createCriteria(ProductDB.class)
.setProjection(Projections.projectionList()
   .add(Projections.alias(Projections.sqlGroupProjection(
      "upper(left(name, " + (prefix.length() + 1) + ")) as x",
      "x", new String[] { "x" }, new Type[] { Hibernate.STRING }), "aliasInCriteriaScope"))
   .add(Projections.rowCount()))
.addOrder(Order.desc("aliasInCriteriaScope"));

Top

ichahid

Post subject: Re: sqlGroupProjection and addOrder results in QueryException

Posted: Wed Sep 26, 2012 8:47 am

Newbie

Joined: Wed Sep 26, 2012 8:42 am
Posts: 1

Ok for the solution. It works. Thanks

But how to do if the sqlGroupProjection returns more than one result:
Example:

Code:

        projectionList.add( Projections.alias( Projections.sqlGroupProjection(
                "extract('month' from {alias}.start_dt) as month, extract('year' from {alias}.start_dt) as year", 
                "extract('month' from {alias}.start_dt), extract('year' from {alias}.start_dt)", 
                new String[]{"month","year"}, 
                new Type[] {Hibernate.INTEGER, Hibernate.INTEGER}), "date" ));

Here have two Integers returned, but aliasing apply only to one.

Thanks for yours solutions.

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

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