I am doing a group-by query using Hibernate 2.1.2 written in HQL. The underlying database is Postgres 7.3.

My query is defined thus:

select m.locationCode, m.account, m.subAccount, sum(m.amountInCents), max(m.id) \
from com.fubar.MeglAggregate m \
group by m.locationCode, m.account, m.subAccount \
order by m.locationCode, m.account, m.subAccount

I guess I have gotten it formatted correctly, because the query DOES return the expected data. However, it returns it, not as a List of objects of the specified type (com.fubar.MeglAggregate), but as a List of 5-element arrays of objects (the object has five attributes).

Also, it seems compulsory with Hibernate that I use the alias here, even though there are no joins and it therefore should not be necessary, but if I avoid it, I get Hibernate errors.

Can someone explain what, if anything, I'm doing wrong?

Below is the mapping file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<!--
Document : MeglAggregate.hbm.xml
-->

<hibernate-mapping>

<class name="com.fubar.MeglAggregate" table="Month_End_Gl">

<id name="id" type="integer" unsaved-value="null" >
<column name="megl_id" sql-type="serial" not-null="true"/>
<generator class="native"/>
</id>

<property name="account">
<column name="cp_account" sql-type="char(4)" not-null="true"/>
</property>

<property name="subAccount">
<column name="cp_subaccount" sql-type="char(8)" not-null="true"/>
</property>

<property name="locationCode">
<column name="location_code" sql-type="char(4)" not-null="true"/>
</property>

<property name="amountInCents">
<column name="amount" sql-type="int" not-null="false"/>
</property>


</class>

</hibernate-mapping>

This is completely excepted behaviour. Please reread the docs and reconsider what you actually want to retrieve with your query.

Hmm, I find the place on p60 of the manual in which it says

"Hibernate queries sometimes return tuples of objects in which case each tuple is returned as an array."

which confirms your statement that this is to be expected.

But that little word "sometimes" bothers me. I have to write java code that casts the returned objects. I was trying to cast them to MeglAggregate objects, which wasn't working. If I need to expect arrays instead I can code to that expectation, but if "sometimes" I might get objects, that will be very unpleasant too. Is there further documentation which I am missing that will give me a way to expect a return type with confidence? Or should I always expect arrays?

If you use projection (select clause), you get projection. The result of a projection operation is a set of tuples, in Java a collection of object arrays.

First, let me thank you guys for your VERY quick responses. Very nice!

Pardon me as I am a SQL veteran and an HQL newbie.

How would i write my query in HQL without the "SELECT" clause?

Basically if you use more than one "result" in the Select clause, you will get arrays as result. If you use just one "result", you will get a List of the results.

(1) it is really incredibly simple and intuitive: if the query returns more than one column of results, it is a tuple, and hence an array; if it does not, it just returns the results directly

(2) select clause is optional in HQL (and in ODMG OQL, and EJBQL, etc) and there are plenty of examples in the documentation

thanks.

So I think what you are saying is, in my case, since you cannot write a non-trivial "GROUP BY" query without specifying more than one column
(column(s)) to group on, other columns to aggregate, you must always expect an array in such situations.

I'm not complaining, I'm just trying to understand. I was expecting to get a List of Objects of the specified type. That seems like a reasonable enhancement to me, but then, I am new to the O-R mapping business as you can tell.

At any rate, thank you all very much for your EXTREMELY prompt replies. You put other open-source forums to shame, not to mention commercial ones.

Well in your select, you are selecting only some of the properties of the object, and others in addition. There is no way to reconstruct an object from this. You should really rethink what you actually want to have as result, and I am pretty sure you can query for it.



We can be even better :)

Are you saying that my aggregates are "others" rather than elements of the query? Are you saying that I could solve this with AS statements on the aggregates?

What do you want to have returned from the query?

A List of MeglAggregate objects.

Great, and what should the property values of these objects be? I really suggest you read some more tutorials and doku ...

I will take a look. I thought my mappings were specifying everything exactly, but I must be missing something.

Look, Hibernate will return you exactly objects in the database. If you force projection explicitly in the select clause, you will get exaclty that, a tuple of values. If you want to force using an object as a data container for projecting selects, you can do that with the select new syntax if you have an appropriate constructor:



Objects don't just mysteriously appear, especially not if there is no related entry in the database (in your query, there is not even an object id in the result). I suggest you experiment some more and get a feel on how an ORM works.