chiperortiz chiperortiz - 3 months ago 23
Java Question

Java Hibernate Projections.sqlGroupProjection aliasing

I have a

Criteria
using Hibernate like this

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


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

Example: (Student data)

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


And so.

I am trying to get a
SUM
of the
DiscriminatorValue
grouping by
DiscriminatorColumn


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

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


This creates the
rightSQL
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
resultSetTransformer
to Student.class I could not find the way Hibernate populates the
sum
of
discriminatorValue
column result in
discriminatorValue
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

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.

Example:

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.

Comments