Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hibernate property based on sum

Tags:

hibernate

jpa

hql

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?

like image 375
benstpierre Avatar asked Dec 09 '22 20:12

benstpierre


2 Answers

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.

like image 198
benstpierre Avatar answered Dec 26 '22 14:12

benstpierre


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

like image 35
Jens Schauder Avatar answered Dec 26 '22 15:12

Jens Schauder