If no expressions in the select are supported, how then can I sum the product of two properties?

The following works:


but the following does not:




Here, lineItem.unitCost is of type Money, lineItem.quantity is of type int.

I receive the following error:



I have no idea what sum(lineItem.unitCost * lineItem.quantity) would be returned as. How can you tell? It would be nice if Hibernate provided the method signature it was looking for. I have tried Integer, int, Long, long, Double, double, BigDecimal, Object and Money. No dice. How can I tell what Hiberate resolves this as?

Regards,

Joshua

If I change sum(o.x * o.y) to sum(o.x + o.y) or any other operator, the query works. Is this a bug? Is there a work around?

What about "this is not supported" don't you understand? You can't do this in HQL. Do it in java, map a formula property or something else.

Michael,

Why are you so grumpy?

To answer your question, I am still in shock that such a highly toughted persistance framework does not support something as basic as sum(obj.x*obj.y) in the select clause. In addition, the error message I get back "no appropriate constructor in class" does little to suggest that this operation is not supported.

Regards,

Joshua

It is not supported because it is very difficult to figure out the JDBC type of such an expression, when we read back the result set.

What type is sum( float + int ) ? Is it constant across all dbs?

Anyway, I think we will be able to implement some support for this with the new query parser in HB3, but I'm concerned that different DBs do not have the same rules for type coercion, etc.

FYI, Hibernate was one of the first, if not -the- first ORM solution to support aggregation *at all*. Most JDO implementations, for example, don't support aggregation, because JDOQL doesn't define it. So the tone of your criticism was a bit unnecessary.

And calling missing features "bugs" is irritating and inexact. Please don't do that in future. Thanks.

Thanks gavin,

Now I understand the problem at hand and realize that this is a missing feature, not a bug.

Regards,

Joshua

ok, np :)

Use view, it is a good way for many reasons and this missing feature is not a problem (if nobody makes you to use some incomplete RDBMS )

Interesting idea. Do I need to map an object to this view to get it to work or is there a way I can hack the hql above?

Use a view which contains the additional calculated column instead of the table you have maped lineitem to now, I'd say

Seems sum (x * y) is still not supported directly in HB3. Here's a work around (in MySQL):

assume
x = decimal(20,2)
y = int(10)

sum (x * y) returns Integer, which is propably not what you want. But since you know that there's a maximum of 2 decimal places, you can use sum (x * 100 * y) / 100, and HB3 is smart enough to return a Double.

Takes some hard work to figure out that it's not supported though.

I have had the same problem and I solved it in the next way:

I created into the class a new field:

/**
* The total price per stock of the invoice lines.
*/
private double totalPricePerStock;

/**
* @hibernate.property formula="FAR_AANTAL*FAR_PRIJS_PER_STUK"
*
* @return Returns the totalPricePerStock.
*/
public final double getTotalPricePerStock() {
return totalPricePerStock;
}

/**
* @param totalPricePerStock The totalPricePerStock to set.
*/
public final void setTotalPricePerStock(final double totalPricePerStock) {
this.totalPricePerStock = totalPricePerStock;
}


And into the query I used sum(totalPricePerStock).

For doing this mapping using Xdoclet you need the 1.2.3 version.
The version 1.2 does not generate the mapping with formula.