CODEWITHSUNDEEP
sqlhibernatehqlaggregate
TJ-
TJ-

Reputation: 14363

Multiple aggregate functions in Hibernate Query

I want to have an HQL query which essentially does this :

select quarter, sum(if(a>1, 1, 0)) as res1, sum(if(b>1, 1, 0)) as res2 from foo group by quarter;

I want a List as my output list with Summary Class ->

Class Summary
{
long res1;
long res2;
int quarter;
}

How can I achieve this aggregation in HQL? What will be the hibernate mappings for the target Object? I don't want to use SQL kind of query that would return List<Object[]> and then transform it to List<Summary>

Upvotes: 1

Views: 5544

Answers (2)

axtavt
axtavt

Reputation: 242686

Since Summary is not an entity, you don't need a mapping for it, you can create an appropriate constructor and use an HQL constructor expression instead. Aggregate functions and ifs are also possible, though you need to use case syntax instead of if.

So, if Foo is an entity mapped to the table Foo it would look like this:

select new Summary(
    f.quarter,
    sum(case when f.a > 1 then 1 else 0 end),
    sum(case when f.b > 1 then 1 else 0 end)
) from Foo f group by f.quarter

See also:

Upvotes: 4

Johanna
Johanna

Reputation: 5293

It might be possible with a subselect in the mapping. Have a look at More complex association mappings in the hibernate documentation. I've never tried that possibility.

But even the hibernate guys recommend "... but it is more practical to handle these kinds of cases using HQL or a criteria query." That's what I would do: Use the group-by in the HQL statement and work with the List. The extra time for copying this list into a list of suitable objects is negligible compared with the time which the group-by is using in the database. But it seems you don't like this possibility.

A third possibility is to define a view in the database containing your group-by and then create a normal mapping for this view.

Upvotes: 0

Related Questions