CODEWITHSUNDEEP
javasqlhibernate
panagdu
panagdu

Reputation: 2133

Using sum() in hibernate criteria

How can I write the sql query select sum(amount * direction) from transactions into hibernate criteria ?

Upvotes: 19

Views: 29558

Answers (3)

panagdu
panagdu

Reputation: 2133

Think I found the answer. You must use the Projection.sqlProjection() and not Projections.sum(). Example:

.setProjection(Projections.sqlProjection("sum(cast(amount as signed)* direction) as amntDir", new String[] {"amntDir"} , new Type[] {Hibernate.DOUBLE}));

Upvotes: 14

zinan.yumak
zinan.yumak

Reputation: 1590

I think what you need is formula. Something like this,

@Entity
@Table('TRANSACTIONS')
Class transactions {

     @Column("AMOUNT")
     private double amount;

     @Column("DIRECTION")
     private double direction;

     @Formula("AMOUNT * DIRECTION")
     private double multiplication;

}

And add multiplication column to your projection list.

Upvotes: 6

Andreas Dolk
Andreas Dolk

Reputation: 114767

Projections is the key word to use aggregate functions with hibnerate criteria.

roseindia has a short example for implementing a SELECT SUM(...) query. Looks pretty close to your problem.

Here's another forum topic that includes a working example for a SUM(col1 * col2) expression and the original poster had similiar problems with exceptions before.

Upvotes: 1

Related Questions