Expressions in hibernate criteria

Question

Hi there, 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?

Answer 1

Hi,

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.

Answer 2

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 = ?

Answer 3

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", 
                    new String[] { "total" }, 
                    new Type[] { Hibernate.DOUBLE } 
                  ) 
            ) 
        );

Ori