Sum of Joda Money split into 2 columns with Spring Data JPA

Question

I'm trying to use JODA money class with Jadira types to handle mapping to Hibernate 4.

It's working okay (except that I'm getting too many currency fields).

But I need to build an aggregate query to sum some totals.

This is the type declaration.

@Columns(columns = { @Column(name = "total_currency", length=10), @Column(name = "total") })
@Type(type = "org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyAmountAndCurrency")
private Money total;

And I'm trying to define a query, something like:

@Query(value="select sum(oi.total) from OrderItem oi where oi.order = ?1")
Double calculateSubtotal(Order order);

Is there a way to aggregate query against a Money field using the JPQL?

Thanks.

Answer 1

There is two ways , but they are limited and I doubt you will really like them :( The main limitation is that in Hibernate HQL you can not invoke methods unless they are mapped properties - end of story. Hibernate does not support Method Invocation

On the other hand you can Map one column as many times as you want as long as you remember that only a single mapping can be updatable. Having this in mind The first step is that you need to do the Hibernate aware of the joda Money. Obviously you can not just annotate it. There are two ways for you to do this:

1. You can use XML to actually annotate the Money class as @Embeddable class. XML hibernate configuration can be used to configure something that is closed source. From what I see in the Money joda - time configuration.

2. The second solution. Probably the one I would use. Is to wrap the Money class in a Wrapper that is declared as @Embeddable. This embedded wrapper you can define multiple mappings for you existing database columns. When you set a value to the WRAPER this value will be pushed to the soda time Money that is inside.

Once you do this you can expose through the wrapper any attribute of the Money class that you want and do any aggregation you want.

@Embeddable
public class MoneyWrapper {


    @Columns(columns = { @Column(name = "total_currency", length=10), @Column(name = "total") })
    @Type(type = "org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyAmountAndCurrency")
    Money totalmoney;


    @Column(name = "total",updateable=false,insterable=false);
    BigDecimal total to agregate;

    @Column(name = "total_currency",updateable=false,insterable=false);
    BigDecimal totalCurrency;  
}

As a result of this mapping you can do a query like:

@Query(value="select select sum(oi.total.totalCurrency) from OrderItem oi where oi.order = ?1")