Hibernate property based on sum

Question

I have figured out that I can use hibernate to get the sum of a number of entities using HQL as follows...

public Long getEnvelopeTotal(AbstractEnvelope envelope) {
    String query = "select sum(t.amount) from User_Transaction t";
    Long result = (Long) hibernateUtil.getSession().createQuery(query).uniqueResult();
    return result;
}

Currently the rest of my application is able to seamlesly navigate the database via the object graph only. The problem with having to use the function above is that I have to do the following psuedo code...

1. Get instance of Entity "Envelope"
2. Pass Envelope instance i
3. Based on the result of getEnvelopeTotal set the Envelope instance property "total" to the result.

What I am wondering if it is possible to use hibernate in such a way that the property "total" is set via custom HQL query instead of being mapped to a simply database column.

ex:

@SomeMagicAnnotation(query="select sum(t.amount) from User_Transaction t")
private Long total;

Any suggestions?

Answer 1

Aha I figured it out after reading this blog post.

The code now looks like this:

@Entity(name = "Abstract_Envelope")
public abstract class AbstractEnvelope extends AbstractAccount {

    @OneToMany(mappedBy = "abstractEnvelope")
    private List<UserTransaction> userTransactions;
    @Formula(value = "select sum(t.amount) from User_Transaction t where t.abstractenvelope_id = id")
    private Long total;

    public List<UserTransaction> getUserTransactions() {
        return userTransactions;
    }

    public void setUserTransactions(List<UserTransaction> userTransactions) {
        this.userTransactions = userTransactions;
    }

    public Long getTotal() {
        return total;
    }

    public void setTotal(Long total) {
        this.total = total;
    }
}

Note that you have to annotate the field instead of the method (well I had to) and than when writing these queries you are referring to the actual DB column name not the bean property name. Also by referring to id instead of something.id you are referring to this.id or this.price effectively.

Answer 2

I think you are looking for the formula Annotation: http://docs.jboss.org/hibernate/stable/annotations/reference/en/html_single/#d0e2273