Hibernate criterion Projection alias not being used

Question

Do Hibernate Projection aliases even work? I could swear it just doesn't. At least, it doesn't do what I would expect it to do.

Here is the java:

return sessionFactory.getCurrentSession()
    .createCriteria(PersonProgramActivity.class)
    .setProjection(Projections.projectionList()
        .add(Projections.alias(Projections.sum("numberOfPoints"), "number_of_points"))
        .add(Projections.groupProperty("person.id")))
    .setFirstResult(start)
    .setFetchSize(size)
    .addOrder(Order.desc("numberOfPoints"))
    .list();

Here is the SQL that it generates:

select
    sum(this_.number_of_points) as y0_,
    this_.person_id as y1_
from
    PERSON_PROGRAM_ACTIVITY this_
group by
    this_.person_id
order by
    this_.number_of_points desc

It doesn't seem to use the alias at all. I would think setting the alias would mean that sum(this_.number_of_points) would be aliased as number_of_points and not y0_. Is there some trick I am missing?

Thanks.

Answer 1

You need to give the entire criteria an alias, then you can create other aliases that actually get used. What is strange is that the aliases get turned into y0_ instead of the other way around.

return sessionFactory.getCurrentSession()
    .createCriteria(PersonProgramActivity.class, "ppa")
    .setProjection(Projections.projectionList()
        .add(Projections.alias(Projections.sum("numberOfPoints"), "ppa.numberOfPoints"))
        .add(Projections.groupProperty("person.id")))
    .setFirstResult(start)
    .setFetchSize(size)
    .addOrder(Order.desc("ppa.numberOfPoints"))
    .list();

Generates the following SQL:

select
    sum(this_.number_of_points) as y0_,
    this_.person_id as y1_
from
    PERSON_PROGRAM_ACTIVITY this_
group by
    this_.person_id
order by
    this_.y0_ desc

Answer 2

The query should be

return sessionFactory.getCurrentSession()
    .createCriteria( PersonProgramActivity.class, "ppa" )
    .setProjection(Projections.projectionList()
        .add( Projections.alias( Projections.sum( **"ppa.numberOfPoints"** ), **"numberOfPoints"** ) )
        .add( Projections.groupProperty( "person.id" ) ) )
    .setFirstResult( start )
    .setFetchSize( size )
    .addOrder( Order.desc( "ppa.numberOfPoints" ) )
    .list();