Expressions in hibernate criteria

Question

Let's say I have a persistent class Item with a quantity field and a price field. Is there a way to build a Criteria that calculates the sum of quantity*price?

score 9 · Accepted Answer · edited Aug 26 '11 at 15:08

I think you can also use an SQL projection. It should be something like:

session.createCriteria(Item.class) 
        .createAlias("item", "i") 
        .setProjection( Projections.projectionList() 
            .add( Projections.groupProperty("i.id") ) 
            .add( Projections.groupProperty("i.price") ) 
            .add( Projections.groupProperty("i.quantity") ) 
            .add( Projections.sqlProjection( 
                    "price * quantity as total", 
                    new String[] { "total" }, 
                    new Type[] { Hibernate.DOUBLE } 
                  ) 
            ) 
        );

Ori

Can you please tell where is the function that creates the total? This only creates the product. — Victor, May 16 '11 at 15:52

score 1 · Answer 2 · answered Jun 07 '09 at 13:28

It's not exactly what you asked for, but you can use "derived properties" to get something rather similar.

For example, you can map a totalPrice property to an SQL expression:

<property name="totalPrice" formula="quantity * price" type="big_decimal"/>

The SQL formula "quantity * price" is evaluated every time the entity is retrieved from the database.

Ori

The Hibernate docs contain more info about this.

score 1 · Answer 3 · answered Jun 07 '09 at 13:36

1

It is (probably) not possible to do it with Criteria. But HQL can be helpful for this.

SELECT ent.quantity*ent.price from EntityName as ent WHERE ent.id = ?

answered Jun 07 '09 at 13:36

Matej

6,004
2
28
27

Much better with HQL – Lluis Martinez Apr 25 '18 at 14:50

Expressions in hibernate criteria

3 Answers3

Linked