Hi,

I am wondering if I can achieve this query using the HQL:
select 'columnName1', sum(myTable.column_name) from table as myTable where ...

I need this kind of query so that I can populate the value of a bean's property using things like apache beanutils. columnName1 is a String literal return in the resultset. It is also the property name in the object.

The real query in my project is actually a lot more complicated and because I need to use aggregate functions, Hibernate does not populate the values into the objects.

Right now, I am getting a list of values and I have to call each setter to populate the values. I also have to rely on the index on the list to make sure I set the right ones. It's extremely difficult to maintain with the addition or removal of properties (columns). If Hibernate supports the query I described in the beginning, at least, everything is in one place.

I hope I have explain my question clearly. Is there a better approach?

Thanks.

Don't quite understand but does the "select new Foo(....) from ...." syntax help?

Thanks for the reply.

The original query:
select sum(finacial.ytdNewwrittenpremPl), sum(finacial.ytdRenewalpremPl), ... from Financial as finacial where ..."

This query will give me a list of one object. That object is an array, let's say result. So now I have to call:

Financial sum = new Financial();
int i = 0;
sum.setYtdNewwrittenpremPl((Integer)Array.get(result, i++));
sum.setYtdRenewalpremPl((Integer)Array.get(result, i++));
...

With 20 or more columns, that's a maintainence problem.

Now what I want to do is, instead of getting back
sum(finacial.ytdNewwrittenpremPl), I also want to get an additional row, which is the literal String "ytdNewwrittenpremPl". Therefore, I can use any reflection based technique to do something like this:

for (int i = 0; i < result.size; i++)
{
// get the fieldName, which would be ytdNewwrittenpremPl during first iteration
// get field value, which would be sum(finacial.ytdNewwrittenpremPl) during first iteration.
BeanUtil.copyProperty(sum, fieldName, fieldValue);
}

This is possible in SQL:

select 'ytdNewwrittenpremPl', sum(finacial.ytdNewwrittenpremPl), 'ytdRenewalpremPl', sum(finacial.ytdRenewalpremPl), ... from Financial as finacial where ..."

Basically, you add the String literal to the select.

Clearer?