chiperortiz chiperortiz - 1 year ago 177
Java Question

Java Hibernate Projections.sqlGroupProjection aliasing

I have a

using Hibernate like this

public class Student
private Integer id,discriminatorColumn,discriminatorValue;
//other stuff omitted for brevity

This entity has a
which have values from 1 to 10 (never
) and each
has a
which have a value from
1 to 100

Example: (Student data)

ID DiscriminatorColumn DiscriminatorValue
1 1 3
2 2 4
3 1 13

And so.

I am trying to get a
of the
grouping by

I mean, in the previous example if I filter by
DiscriminatorColumn = 1
I would receive

final Criteria criteria = session.createCriteria(Student.class)
"sum({alias}.discriminatorValue) as rowCount",
"hibernateAlias.discriminatorColumn having rowCount>0",
new String[]{"rowCount"},

This creates the
I see something like this

select sum(discriminatorValue) as rowCount
from student
group by discriminator_column
having rowCount>0

The problem i have is when i set the
to Student.class I could not find the way Hibernate populates the
column result in
Java field.

I think that for that purpose is use the following parameter
new String[]{"rowCount"}

But I have tried

new String[]{"discriminatorValue"}
new String[]{"this.discriminatorValue"}
new String[]{"{alias}.discriminatorValue"}
new String[]{"{studentAlias}.discriminatorValue"} using alias in Criteria
new String[]{"rowCount as discriminatorValue"}
new String[]{"rowCount as this_.discriminatorValue"}
and So On..

But seems impossible what i am doing wrong? It's not possible?
Sum the values as set it as a simple Property as retrieving regular from database

I have written my own transformer for this but it's kind of annoying.

Answer Source

I have found the answer by myself and i will post here to be able to help somebody.

Projections.sqlGroupProjection("sum({alias}.discriminatorValue) as rowCount","hibernateAlias.discriminatorColumn having rowCount>0",new String[]{"rowCount"},Helper.HIBERNATE_INTEGER_INSTANCE)

Seems the rowCount aliasing in new String[]{"rowCount"} is just for mysql syntax or is just ignore by Criteria engine is you need to use a alias to populate a property of your EntityClass use outer alias instead.


yourProjections.add(Projections.alias(Projections.sqlGroupProjection("sum({alias}.discriminatorValue) as rowCount","hibernateAlias.discriminatorColumn having rowCount>0",new String[]{"rowCount"},Helper.HIBERNATE_INTEGER_INSTANCE),"discriminatorValue"));

Just wrap your sqlGroupProjections in a Projection.alias as set the alias match your property in your Java class and Hibernate will populate with the sum result and not custom transformer are not longer required..

I hope helps somebody.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download